two variables

[margaret]

I have a query that asks for collcode, collcode2 and booth. On any given day
an employee may be collcode or collcode2. I would like to know how many
times a certain emp has been paired with another employee. So how do I ask
if an employee has been collcode/collcode2 and who the other person was? I
hope this question makes sense.

Thanks for any help

 

[John Spencer]

Query one:
SELECT CollCode, CollCode2, Booth
FROM YourTable
UNION ALL
SELECT CollCode2, CollCode, Booth
FROM YourTable


Query Two
SELECT CollCode, CollCode2, Count(CollCode) AS TIMESPAIRED
FROM QueryOne
GROUP BY CollCode, CollCode2

IF you want to limit the result to a specific individual then add a
where clause

Query Two
SELECT CollCode, CollCode2, Count(CollCode) AS TIMESPAIRED
FROM QueryOne
WHERE CollCode = "SomeValue"
GROUP BY CollCode, CollCode2

If you want to list every incident and the booth
SELECT CollCode, Callcode2, Booth
FROM QueryOne
WHERE CollCode = "SomeValue"




'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[margaret]

John:

That worked great, thanks for your prompt response. However, one more
variable. The employees will count many booths on one day, for example, the
team may count booth 1, 2, 3, 4. So I need to limit the count to how many
"fairdate" they were matched instead of how many times they were together.

FYI, the table is: id, collcode, collcode2, booth, fairdate ... Sorry I
know I didn't mention fairdate last time.

 

[John Spencer]

Try the following

Query One:
SELECT CollCode, CollCode2, FairDate
FROM YourTable
UNION
SELECT CollCode2, CollCode, FairDate
FROM YourTable

Using UNION instead of UNION ALL will remove any duplicates in the results.

Query Two (use query one as the basis)
SELECT CollCode, CollCode2, Count(FairDate) as DayCount
FROM QueryOne


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[margaret]

Wonderful ... thank you for your help today. It now works like a champ.