Counting Unique records 1

[postmanphat]

Hi I posted a similar problem a little while ago (

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

whilst you kind people successfully sorted it out then, unfortuantely I can't seem to fix a similar problem.

I use a db to record how many people come into a drop-in centre. This code counts how many males and females contact us and how (InterventionID)

SELECT D.InterventionID, Sum(IIf(C.Gender='M',1,0)) AS Males, Sum(IIf(c.gender='f',1,0)) AS Females
FROM tblDay INNER JOIN (tblContacts AS C INNER JOIN tblDropIn AS D ON C.ContactID = D.ContactID) ON tblDay.DayID = D.DayID
WHERE (((tblDay.SessionDate) Between [Forms]![frmStatDate]![Text1] And [Forms]![frmStatDate]![Text3]) AND ((D.Adult)=False))
GROUP BY D.InterventionID;

This gives me a count of how many people contact the centre. If 4 different males come in on one day, the query quite correctly returns '4' in the males column. However, if the same male comes in 4 times, the query still says 4 males contacted us that day, but in fact I want it to report that only 1 male came in that day. The fact he came in 4 times is irrelevant.

Hope thats clear.

Many many thanks in advance.

 

[randy700]

Have you tried SELECT DISTINCT?


Randy

 

[postmanphat]

I have tried that but it made no different. I believe the answer is very similar to the solution offered in the thread I've linked to in my initial post, but I can't quite get it working.

 

[PHV]

Similar answer?
SELECT D.InterventionID, Sum(IIf(C.Gender='M',1,0)) AS Males, Sum(IIf(c.gender='f',1,0)) AS Females
FROM (tblDay INNER JOIN (
SELECT DISTINCT DayID, InterventionID, ContactID FROM tblDropIn WHERE Adult=False
) AS D ON tblDay.DayID = D.DayID)
INNER JOIN tblContacts AS C ON D.ContactID = C.ContactID
WHERE tblDay.SessionDate Between [Forms]![frmStatDate]![Text1] And [Forms]![frmStatDate]![Text3]
GROUP BY D.InterventionID;

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

 

[postmanphat]

PHV, one day I'm gonna think of something that you don't know the answer to.

Cheers!

 

[postmanphat]

When you copy the code into the query it runs fine, as soon as I try to create a subform from that query, it says:

"Cannot find the Input table or query 'SELECT DISTINCT DayID, InterventionID, ContactID FROM tblDropIn WHERE Adult=False'

Any ideas?

 

[postmanphat]

Update:

Try it yourself, copy PHV's code above into a query + save it - it will run fine. Now just create a quick autoform based on that query and it will throw up that error message. Even if you just now try and run the query on its own it says it can't, even though it hasn't changed at all and it worked perfectly about 20 seconds ago!?

 

[PHV]

Create a query named, say, qryDistinctDropIn:
SELECT DISTINCT DayID, InterventionID, ContactID FROM tblDropIn WHERE Adult=False

And now your actual query:
SELECT D.InterventionID, Sum(IIf(C.Gender='M',1,0)) AS Males, Sum(IIf(c.gender='f',1,0)) AS Females
FROM (tblDay INNER JOIN qryDistinctDropIn AS D ON tblDay.DayID = D.DayID)
INNER JOIN tblContacts AS C ON D.ContactID = C.ContactID
WHERE tblDay.SessionDate Between [Forms]![frmStatDate]![Text1] And [Forms]![frmStatDate]![Text3]
GROUP BY D.InterventionID;

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