Gary has attended:
Access Intermediate course
Access Advanced course
Excel Intermediate course
Excel Advanced course
Counting in reports
I have created a query qStaff that generates a list of dates [date] and staff names [worker]. I wish to create a field in a report that counts the number of time one particular member of staff appears (e.g. Fred Bloggs) in that list. What should go in Expression builder to do this?
Also what is the expression if I wish to do the same with another employee (e.g. Bill Smith) and subtract it from the first value?
Are these SQL statements?
Many thanks, Gary Heather
RE: Counting in reports
Hi Gary
Thanks for your question
I think a Grouped summation query will solve your first problem
Create a query with the staff field in twice. (They should be the only two fields in the query)
Then click the sigma button to add the grouping options. In the "group by" column in the second staff field change the setting to count, and run the query.
As to your second problem, I'm not sure that this can easily be achieved in an Access query. I suggest exporting the ungrouped query into excel and building a pivot table. I will however explore things further and if I find a soloution I will repost here
Reagrds
Stephen
