ACCESS FEED: Re: [MS_AccessPros] Append record based on criteria question

Jumat, 09 Januari 2015

Re: [MS_AccessPros] Append record based on criteria question

Duane,

thank you

The first sql statement worked. Poly is the campus name and it is located in the Loc Id field. I just received another request for the query to see the break down for each campus for the Empl Stat Ld field which includes the following values.

Active
Leave of Absence
Leave With Pay

I tried adding the below expression in the query and I see all records.

Count of Empl Stat LD Active Tempe Active: IIf("[Loc Id] = TEMPE",Sum(Abs([Empl Stat Ld]="Active")))

Jim Wagner

On Friday, January 9, 2015 10:11 AM, "Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

Jim,

Lots of Access users don't understand the use of Count(). Count() looks at null vs non-null values. Count(1) is the same as Count(0) is the same as Count("A") is the same as Count(123103954).

Without understanding the big picture of what you are doing, your first step might be:

SELECT Sum(Abs([Loc Id]="POLY")) AS [Count of Poly]
FROM [R&D-CURRENTEMPLOYEES];

or this which I don't like quite as much:

SELECT Count(IIf([Loc Id]="POLY",[Person Id],Null)) AS [Count of Poly]
FROM [R&D-CURRENTEMPLOYEES];

It's unclear if your target table has a field named something like "Poly" which suggests your table isn't normalized. I could be wrong since I don't have a clear picture of your requirements or data structures.

Duane Hookom MVP
MS Access

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Fri, 9 Jan 2015 16:15:29 +0000
Subject: Re: [MS_AccessPros] Append record based on criteria question

Duane,

I am writing an append query to append some counts to a table based on a downloaded excel sheet. But after thinking about the situation I am thinking of writing a query to get the counts using IIF and then append the values to the table.

I am trying to get counts of employees based on campuses and obviously an all counts. I want to do an append query each day I do the download. This way I can track the counts.

I tried to do this below but it is giving me an all count

SELECT Count(IIf([Loc Id]="POLY",[Person Id],0)) AS [Count of Poly]
FROM [R&D-CURRENTEMPLOYEES];

The fields I am looking at are Person Id, Loc Id

There are four campuses

Tempe

Poly

West

Jim Wagner

On Friday, January 9, 2015 8:51 AM, "Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

Jim,

Do you want to create an append query or write some VBA code or what?

Can you share the table(s) fields and values you want to go into the fields?

Is this from a form? Are there values on the form that need to go into the table?

Duane Hookom MVP
MS Access

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Fri, 9 Jan 2015 15:17:40 +0000
Subject: [MS_AccessPros] Append record based on criteria question

Hello all,

I am trying to append a record into a table based on the date in the record. the table name is tblCurrentEmployeesAnalysis where the field DateOfProcess has a default value of Date(). But I am blank on a Friday on how to do this. Any help would be appreciative.

Thank You

Jim Wagner