David McAfee
davidmcafee at gmail.com
Thu Nov 3 16:08:39 CDT 2011
SELECT * FROM tblSomeTable PKID Uitv Sub Patient ----------- ---- ---- ----------- 1 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 (7 row(s) affected) SELECT Uitv, Sub, COUNT(Patient) AS UniquePatient FROM tblSomeTable 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) Maybe in Access/Jet, but in TSQL, the grouping takes care of that. I do not see how Jet would be different. David On Thu, Nov 3, 2011 at 1:53 PM, Stuart McLachlan <stuart at lexacorp.com.pg>wrote: > That won't work, it will return 3 for A - ee. > > Gustav's solution is the way I would do it - use a select Distinct Query > first to get rid of the > duplicates. > > On 3 Nov 2011 at 9:49, David McAfee wrote: > > > SELECT Uitv, Sub, COUNT(Patient) AS UniquePatient > > FROM tblSomeTable > > GROUP BY Uitv, Sub > > ORDER BY Uitv, Sub > > > > > > On Thu, Nov 3, 2011 at 2:43 AM, <pedro at plex.nl> wrote: > > > > > Dear Group, > > > > > > how can i query the unique patients, from "Uitv" and "Sub". > > > > > > Data > > > > > > Uitv Sub Patient > > > A ee 101 > > > A ee 101 > > > A ee 102 > > > A ff 201 > > > A gg 301 > > > B ee 201 > > > B ee 301 > > > B hh 501 > > > > > > > > > > > > Result > > > > > > Uitv Sub Unique Patient > > > A ee 2 > > > A ff 1 > > > A gg 1 > > > B ee 2 > > > B hh 1 > > > > > > > > > Thanks > > > > > > Pedro Janssen > > > > > > -- > > > AccessD mailing list > > > AccessD at databaseadvisors.com > > > http://databaseadvisors.com/mailman/listinfo/accessd > > > Website: http://www.databaseadvisors.com > > > > > -- > > AccessD mailing list > > AccessD at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/accessd > > Website: http://www.databaseadvisors.com > > > > > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com >