Need help with this query! probably simple

[darwinli]

hi all

got a quick question, please see the images below


basically i want a new table to show this

ID/loc desc/AM counts/PM Counts/Sat Counts/Sum ( / seperates each column)


as you can see, i go to calculate the totals for each ID but cannot seem to add additional columns with what I want, each ID and Count (AM/PM or SAt) is a new data entry so does it not like that combined into 1 row?

Sorry I am new to access and tryin to learn this for my job

thanks

[June7]

Try a crosstab query.

OR

Create fields in query with expressions like:

CountAM: IIf(AM_PM_SAT="AM",[Count],0)
CountPM: IIf(AM_PM_SAT="PM",[Count],0)
CountSAT: IIf(AM_PM_SAT="SAT",[Count],0)

Then apply the Sum aggregate function to each of these.

[darwinli]

you tried to execute a query as part of an aggregate function


i get this when i try expression

[June7]

Expression works for me. Show your attempted sql statement.