Stuart McLachlan
stuart at lexacorp.com.pg
Thu Nov 3 17:24:22 CDT 2011
You've changed the data set to suit your code, Essentially doing wht the suggested "pre-query" does. The original data was: A ee 101 A ee 101 A ee 102 ... On 3 Nov 2011 at 14:08, David McAfee wrote: > 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 > > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com >