Troublesome Query - Counting

[Jeff C]

I am trying to determine a score for representatives based on the number of
accounts they service.

Representatives
Acct Refer Admit Spec
09876 9901 8765 9901
89654 7765 6701 6578
56431 6767 6701 6767

Expected Output

Rep Count
9901 1
8765 1
7765 1
6578 1
6701 2
6767 1

Each rep can be assigned an account in a number of different categories but
each account only counts once for each representative. I built a union query
for each category which works fine for tracking how many times each rep was
assigned in a category but since some reps are assigned in multiple
categories the counts are exaggerated. Can anyone help me with a solution?

Thanks in advance

 

[Allen Browne]

Build a UNION query like this:

SELECT Table1.Acct, Table1.Refer AS Ref FROM Table1
WHERE Table1.Refer Is Not Null
UNION
SELECT Table1.Acct, Table1.Admit As Ref FROM Table1
WHERE Table1.Admit Is Not Null
UNION
SELECT Table1.Acct, Table1.Spec AS Ref FROM Table1
WHERE Table1.Spec Is Not Null

Now use that as an input 'table' for another query.
GROUP BY Acct, Ref.

Now can now use that query to get the count of accounts per ref.

 

[Jeff C]

So close yet so far away - Thank Allen - worked great.
--
Jeff C
Live Well .. Be Happy In All You Do

Build a UNION query like this:

SELECT Table1.Acct, Table1.Refer AS Ref FROM Table1
WHERE Table1.Refer Is Not Null
UNION
SELECT Table1.Acct, Table1.Admit As Ref FROM Table1
WHERE Table1.Admit Is Not Null
UNION
SELECT Table1.Acct, Table1.Spec AS Ref FROM Table1
WHERE Table1.Spec Is Not Null

Now use that as an input 'table' for another query.
GROUP BY Acct, Ref.

Now can now use that query to get the count of accounts per ref.