Multiple query or Single Query Sum problem (t think) 1

[thelude]

MS Access Query Gurus,

Let me explain what I have and explain what I need and hopefully you can come up with a solution.
I run a volunteer fire company and have a form in access that I use to enter call information.
I have 4 slots for an truck.
“Officer – Member1 – Member2 – Member3”
I have a list box (in the form) that I can pull down and enter firefighter’s names, they update a table with fields that have the same name (eg. Officer, Member1, Member2,..) all in the same table. I have about 500 records. I need a query that will count up and display the total calls that a member was on. It would need to search through the “officer, member1, member2 and member3” fields in the table because you can be an officer on one call and be a member on another call. It doesn’t matter in this case if you are Officer or member. Not all calls have 4 members (e.g there might only be officer and member1, so it would have to not be fooled by blanks)

I don’t know sql but this what is in SQL view

SELECT Calls.[Officer], Count(Calls.[ Officer]) AS [CountOfOfficer]
FROM Calls
GROUP BY Calls.[Officer];

Gives me a nice list of all the calls by Officer. If I try to add another field to the query like this (I add the field in the design query view)

SELECT Calls.[Officer], Count(Calls.[Officer]) AS [CountOfOfficer], Calls.Member1, Count(Calls.Member1) AS CountOfMember1
FROM Calls
GROUP BY Calls.[Officer], Calls.Member1;

Is comes out all messed up with duplicates and wrong information.

Thanks for any help and I’m standing by if you need more info.

 

[PHV]

Create a saved normalization UNION query named, say, qryMembers:
SELECT Officer AS Member FROM Calls WHERE Officer Is Not Null
UNION ALL SELECT Member1 FROM Calls WHERE Member1 Is Not Null
UNION ALL SELECT Member2 FROM Calls WHERE Member2 Is Not Null
UNION ALL SELECT Member3 FROM Calls WHERE Member3 Is Not Null

And now your query:
SELECT Member, Count(*) AS TotalCalls
FROM qryMembers
GROUP BY Member

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[thelude]

PH,
Awesome. That is precisely what I needed, a huge thanks from the fire service.