[AccessD] unique patients

You can do this in TSQL, but access doesn't allow it

SELECT Uitv, Sub, COUNT(DISTINCT 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)




On Thu, Nov 3, 2011 at 4:12 PM, Asger Blond <ab-mi at post3.tele.dk> wrote:

> I see - thanks Stuart.
> Asger
>
> -----Oprindelig meddelelse-----
> Fra: accessd-bounces at databaseadvisors.com [mailto:
> accessd-bounces at databaseadvisors.com] På vegne af Stuart McLachlan
> Sendt: 3. november 2011 23:36
> Til: Access Developers discussion and problem solving
> Emne: Re: [AccessD] unique patients
>
> No typos,  he wants the number of Unique patients having the same Uitv and
> Sub.
>
> A ee  has three records,
>
> A ee 101
> A ee 101
> A ee 102
>
> but two unique patients, 101 and 102.
>
> Hence:
> A ee 2
>
> Similary B ee has two unique patients 201 and 301, hence:
>
> B ee 2
>
> --
> Stuart
>
> On 3 Nov 2011 at 23:25, Asger Blond wrote:
>
> > Pedro,
> > I don't understand your example with the data provided. Could you
> > please tell if you made some typos - especially: Why is the data "A ee
> > 102" not listed or counted in the result, and why is the data "B ee
> > 201" and "B ee 301" listed as a count of 2 in the result? IOW: What's
> > defining a unique entity for the counting in your example: Uitv + Sub
> > or Uitv + Sub + Patient? Asger
> >
> > -----Oprindelig meddelelse-----
> > Fra: accessd-bounces at databaseadvisors.com
> > [mailto:accessd-bounces at databaseadvisors.com] På vegne af
> > pedro at plex.nl Sendt: 3. november 2011 09:44 Til:
> > accessd at databaseadvisors.com Emne: [AccessD] unique patients
> >
> > 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
>