[AccessD] unique patients

Wow, my eyes or mind must have filtered that second line out when typing
the records in.

SELECT * FROM tblSomeTable  ORDER BY Uitv, Sub, Patient

PKID        Uitv Sub  Patient
----------- ---- ---- -----------
1           A    ee   101
8           A    ee   101
2           A    ee   102
3           A    ff   201
4           A    gg   301
5           B    ee   201
6           B    ee   301
7           B    hh   501

(8 row(s) affected)




SELECT Uitv, Sub, Patient FROM tblSomeTable  GROUP BY Uitv, Sub, Patient
ORDER BY Uitv, Sub, Patient

Uitv Sub  Patient
---- ---- -----------
A    ee   101
A    ee   102
A    ff   201
A    gg   301
B    ee   201
B    ee   301
B    hh   501

(7 row(s) affected)


--Either of these work the same, not sure which is faster/better:


SELECT Uitv, Sub, COUNT(Patient) AS UniquePatient
FROM
(SELECT Uitv, Sub, Patient FROM tblSomeTable  GROUP BY Uitv, Sub, Patient) A
GROUP BY Uitv, Sub
ORDER BY Uitv, Sub

Uitv Sub  UniquePatient
---- ---- -------------
A    ee   2
A    ff   1
A    gg   1
B    ee   2
B    hh   1

(5 row(s) affected)



SELECT Uitv, Sub, COUNT(Patient) AS UniquePatient
FROM
(SELECT DISTINCT Uitv, Sub, Patient FROM tblSomeTable) A
GROUP BY Uitv, Sub
ORDER BY Uitv, Sub

Uitv Sub  UniquePatient
---- ---- -------------
A    ee   2
A    ff   1
A    gg   1
B    ee   2
B    hh   1

(5 row(s) affected)




On Thu, Nov 3, 2011 at 3:24 PM, Stuart McLachlan <stuart at lexacorp.com.pg>wrote:

> WIth the original dataset, not the cleansed one?
>
> --
> Stuart
>