query that counts from 3 fields

[pld60]

I have a data base that keeps track of incident reports. There may be 1, 2, or 3 staff involved in an incident. There are fields [staff 1], [staff 2], and [staff 3] in one table. A staff name may show up in any of the 3 fields. I have queries that show staff count in each field. The question is how do get one total, ie

Smith is in [Staff 1] 3 times, [Staff 2] 1 time, and [Staff 3] 2 times for a total of 6.
Jones is in [Staff 1] 10 times, [Staff 2] 6 time, and [Staff 3] 4 times for a total of 20.
And so on.

How do I make this happen?

[ajalwaysus]

I think that the solution to this is much more complicated than if you had a Staff table that you could assign staff to an incident report.

Example:
[Incident ID] [Staff Mamber]
1 Jones
1 Smith
2 Smith
3 Jones
3 Smith
4 Jones
5 Jones
6 Smith

-AJ

[pld60]

I may not have been clear. Each record in the table is an incident. So, if there is an incident report writen only Smith is involved then Smith is entered in the field [Staff 1]. If Smith and Jones are both involve then Smith is entered in the field [Staff 1] and Jones is entered in the field [Staff 2] or Jones is entered in the field [Staff 1] and Smith is entered in the field [Staff 2]. [Staff 1] would be the most involved etc.

If these were the only 2 records then I need a query result that would Count Smith 2, Jones 1.

Hope this helps.

[ajalwaysus]

I hope someone else may have a better idea but i am drawing a blank, this is the best I could come up with...

SELECT Staff.Staff1, Count(Staff.Staff1) AS CountOfStaff1, "" AS Staff2, "" AS CountOfStaff2, "" AS Staff3, "" AS CountOfStaff3
FROM Staff
GROUP BY Staff.Staff1
HAVING (((Staff.Staff1) Is Not Null))
UNION ALL
SELECT "" AS Staff1, "" AS CountOfStaff1, Staff.Staff2, Count(Staff.Staff2) AS CountOfStaff2, "" AS Staff3, "" AS CountOfStaff3
FROM Staff
GROUP BY Staff.Staff2
HAVING (((Staff.Staff2) Is Not Null))
UNION ALL SELECT "" AS Staff1, "" AS CountOfStaff1, "" AS Staff2, "" AS CountOfStaff2, Staff.Staff3, Count(Staff.Staff3) AS CountOfStaff3
FROM Staff
GROUP BY Staff.Staff3
HAVING (((Staff.Staff3) Is Not Null));
 

pld60, I understand fully what you are doing, but this does not pass normalization and therefore makes reporting more difficult. You say you need a way of prioritizing staff to the incident ticket then you could design a table like this...

[Incident ID] [Staff Member] [Priority Rank]
     1             Jones            1
     1             Smith            2
     2             Smith            1
     3             Jones            2
     3             Smith            1
     3             Doe              3
     4             Jones            1
     5             Jones            1
     6             Smith            1
 

[pld60]

Thanks for your help but, this does not work. I do not need away of prioritizing at all, that is done on the incident report when writen and then it is put in the fields accordingly. May be there is not a simple way of doing this?

[FishVal]

A simple way of doing this is normalization.

Kindest regards,
Fish.