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
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];
FROM [R&D-CURRENTEMPLOYEES];
The fields I am looking at are Person Id, Loc Id
There are four campuses
DT
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
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
__._,_.___
Posted by: Duane Hookom <duanehookom@hotmail.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (4) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar