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.
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.