Multiple counts/sums needed

[Audreysfirstdaughter]

Hi, I write the MS 2003 queries for my group and am completely self-trained. I'm having trouble writing a query for which I need the following summations:



By different Coordinator (employee), I need a count of how many requests each coordinator has for each state. Here are my fields:

ESC (the coordinator ee)
ISRN (a unique number assigned to an event)
RST (the state in which the event was held)
DRR (the date the request was received)

Let's say I'm one of the ESCs. My name is Cindy. I would need to know how many ISRNs Cindy has for each of the 50 states.

I'm sure this will be simple for someone, but I've tried grouping, counting and summing and all to no avail. Any assist will be most appreciated. Thank you!

[June7]

Did you try building a report using Grouping & Sorting and summary calcs in group footers?

[Audreysfirstdaughter]

I just started to look at reports because it seemed as though I was asking my query to do many calculations; thank you. Being self taught, I have much to learn. Thanks for pointing me in the right direction.

[Rixxe]

Let's say I'm one of the ESCs. My name is Cindy. I would need to know how many ISRNs Cindy has for each of the 50 states.

This should work:

SELECT a.ISRNs, Count(RST)
FROM TableA as a
WHERE a.ESC = 'Cindy'
GROUP BY a.ISRNs

[Audreysfirstdaughter]

Thank you, Rixxe. I'm clearly in over my head ... no idea to do with the info you gave me. I tried to copy and paste it in the SQL view but received a lot of errors. ... wish my company would pay for me to get some training. Ugh!

[alansidman]

When you took Rixxe's SQL statement and pasted it into a Query, did you bother to change the table names from TableA to reflect your table name? If not, then Access would not know where to get the data from.

[June7]

That query will produce summary data but if you want to see the details of records that contribute to the summary, should build report.