please help with this

[sjs01]

I have a table called 'sets' with 3 fields - spb, pat and set. An example of the table is shown below:

spb pat set
1 123 ue
1 123 lft
2 123 ue
3 123 ue
4 1234 ue
4 1234 lft
5 123 ue
6 1234 lft
For each 'set' I would like to output the total number of requests (a request is defined as having a unique 'spb' value) and the total number of unique patients (a patient is defined as having a unique 'pat' value), using a single query (if possible). In the above example the output should be:

set Requests Patients
ue 5 2
lft 3 2

Thanks for any help with this.

 

[raskew]

This is a good challenge!

Hi -

Have played with what seems to be a fairly simple problem, and have yet to come up with a fix.

Would be really interested to see how you resolve the poster's dilemma.

Bob

 

[Jon K]

I would use a Union query followed by a Crosstab query.

Query1:-
SELECT DISTINCT set, spb, "Requests" as ColumnHead
FROM TableName
UNION
SELECT DISTINCT set, pat, "Patients"
FROM TableName;

Query2:-
TRANSFORM Count(spb) AS CountOfspb
SELECT set
FROM Query1
GROUP BY set
ORDER BY set DESC
PIVOT ColumnHead In ("Requests","Patients");
.

 

[sjs01]

Bingo! Thanks for that!

Cheers.

 

[raskew]

Jon -

A very elegant solution!

Bob