Counting DISTINCT records in a query 1

[postmanphat]

I want to count how many females are coming to a drop-in centre and I want to know how old they are. The follwing query calculates the ages of all the females who have attended from their dob which is stored in the table 'tblcontacts' along with their gender. We know who attended a drop in session because tblContacts.ContactID is recorded in tblDropIn which is the attendance table if you like. This works perfectly - here (basically) is the SQL:

SELECT DateDiff("yyyy",[tblcontacts].[dob],Now())+Int(Format(Now(),"mmdd")<Format([tblcontacts].[dob],"mmdd")) AS Age, Sum(IIf([tblcontacts].[gender]='f',1,0)) AS Female
FROM tblContacts INNER JOIN tblDropIn ON tblContacts.ContactID = tblDropIn.ContactID
WHERE (((tblContacts.ContactID) In (select ContactiD from tblDropIn)))
GROUP BY DateDiff("yyyy",[tblcontacts].[dob],Now())+Int(Format(Now(),"mmdd")<Format([tblcontacts].[dob],"mmdd")), tblDropIn.Adult
HAVING (((tblDropIn.Adult)=False));

Now then, the problem is this: Assume only 1 20 yr old female comes in that day, but she comes into the centre twice. My query above will report that on that day we had 2 20 yr old females come through the door, when it really should only tell me that 1 20 yr old female came through the door. The fact that she came in twice is unimportant. I want to know how many unique people came on a given day.

Many many many thanks in advance

 

[PHV]

SELECT DateDiff("yyyy",[tblcontacts].[dob],Now())+Int(Format(Now(),"mmdd")<Format([tblcontacts].[dob],"mmdd")) AS Age
, Sum(IIf([tblcontacts].[gender]='f',1,0)) AS Female
FROM tblContacts INNER JOIN (
SELECT DISTINCT ContactID, [your date field here] FROM tblDropIn WHERE Adult=False
) AS D ON tblContacts.ContactID = D.ContactID
GROUP BY DateDiff("yyyy",[tblcontacts].[dob],Now())+Int(Format(Now(),"mmdd")<Format([tblcontacts].[dob],"mmdd"))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[postmanphat]

I've said it before, and I'll say it again: PHV, I love you

 

[postmanphat]

I now have a similar problem again here:

http://www.tek-tips.com/viewthread.cfm?qid=1458998&page=1