[AccessD] unique patients

Stuart McLachlan stuart at lexacorp.com.pg
Thu Nov 3 17:24:22 CDT 2011


You've changed the data set to suit your code, Essentially doing wht the suggested 
"pre-query" does.

The original data was:

A       ee      101
A       ee      101
A       ee      102
...



On 3 Nov 2011 at 14:08, David McAfee 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
> 






More information about the AccessD mailing list