Stuart McLachlan
stuart at lexacorp.com.pg
Thu Nov 3 17:24:22 CDT 2011
WIth the original dataset, not the cleansed one? -- Stuart On 3 Nov 2011 at 15:06, David McAfee wrote: > I just tried it in Access and it works just like SQL Server. > > I can email you a zipped mdb > > On Thu, Nov 3, 2011 at 2:18 PM, Gary Kjos <garykjos at gmail.com> wrote: > > > Well the original example had TWO A ee 101 records and I agree with > > Stuart that it would return 3 and not 2 for the simple group by with > > count in the same one. It needs a double query somehow, two group > > bys the first to eliminate the dups or a distinct to eliminate the > > dups lest you double count. Count will count ALL the records that it > > sees won't it? > > > > On Thu, Nov 3, 2011 at 4:08 PM, David McAfee <davidmcafee at gmail.com> > > 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 > > > > > > > > > > > -- > > Gary Kjos > > garykjos at gmail.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 >