[AccessD] unique patients

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
> 






More information about the AccessD mailing list