Need SQL help - Select Distinct, Count, Group By

[Chris]

Hello all,

I'm having some trouble setting up a query.

Background: The table TBLSCREEN stores data about screenings of
patients/subjects for eligibility to participate in a health study. A
subject can be screened multiple times.

The simplified table structure is

SITE - location of subject
SUBJECTID - ID of subject
SCREENNUM - the number of the screening (1 represents first screening,
etc)
SCREENDATE - the date the subject was screened

A subject is uniquely identified by the combination of SITE and
SUBJECTID. A screening is uniquely identified by the combination of
SITE, SUBJECTID, and SCREENNUM.

I need to create a query that shows the number of unique subjects
screened per month.

If a subject is screened twice in two different months, only the first
screening should count.

I've tried variations of something like this, but nothing has worked
so far:

SELECT COUNT(*) AS NumSubj FROM
(SELECT DISTINCT SITE, SUBJECTID
FROM TBLSCREEN)
GROUP BY Format(SCREENDATE,"yyyy-mm");

Thanks in advance for any help you may be able to provide,
Chris

[John Winterbottom]

"Chris" <ch*****@mailinator.com> wrote in message
news:a0**************************@posting.google.c om...

I need to create a query that shows the number of unique subjects
screened per month.

If a subject is screened twice in two different months, only the first
screening should count.

When you work with groupings of temporal data it's often better to build and
populate a calendar table to hold the individual date groups, and then join
to this table in your query. I don't have time now but see if this puts you
on the right track, (do a search in google for calendar table) - if you
can't solve it post back and i'll write the sql.

[Nicole]

> A subject is uniquely identified by the combination of SITE and

SUBJECTID. A screening is uniquely identified by the combination of
SITE, SUBJECTID, and SCREENNUM.

SELECT COUNT(*) AS NumSubj FROM
(SELECT DISTINCT SITE, SUBJECTID
FROM TBLSCREEN)
GROUP BY Format(SCREENDATE,"yyyy-mm");

Is this what you are looking for?

SELECT COUNT(*) AS NumSubj, site
FROM
(SELECT COUNT(subjectid), site, screennum
FROM site
GROUP BY site, screennum) as subquery
GROUP BY site