David McAfee
davidmcafee at gmail.com
Thu Nov 3 17:06:03 CDT 2011
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 >