[AccessD] unique patients

David McAfee davidmcafee at gmail.com
Thu Nov 3 16:08:39 CDT 2011


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
>



More information about the AccessD mailing list