RE: [MS_AccessPros] Append record based on criteria question

Jim,


Have you considered using a crosstab query for reporting counts?

When I have lots of slicing and dicing, I will msQuery the Access data into Excel and use a pivot table.


If you want to count based on multiple criteria, the generic expression might be like:


Sum(Abs([Color] = "Red" and [Size] = "Large"))


Duane

________________________________
> To: MS_Access_Professionals@yahoogroups.com
> From: MS_Access_Professionals@yahoogroups.com
> Date: Fri, 9 Jan 2015 18:52:41 +0000
> Subject: 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
> 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
>
>
>
> 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
> ________________________________
>
>
>
>
>
>
>
>
>
>
>

------------------------------------

------------------------------------


------------------------------------

Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/MS_Access_Professionals/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/MS_Access_Professionals/join
(Yahoo! ID required)

<*> To change settings via email:
MS_Access_Professionals-digest@yahoogroups.com
MS_Access_Professionals-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
MS_Access_Professionals-unsubscribe@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/