Query to count contacts but no duplicates?

[YNWA]

Hi, I have a query to count the number of contacts received in a specific date period, this works fine.

However sometimes patients (patientID) can have more than 1 contact (contactID).

If I run a query which displays the number of contacts, how do I refine that to not include duplicate patientID's? So the results only have 1 patientID per patient.

Thanks
Will

 

[RuralGuy]

Have you tried using the DISTINCT key word?

 

[YNWA]

Have you tried using the DISTINCT key word?

No, how do I use this?

Thanks
Will

 

[RuralGuy]

Select DISTINCT patientID, ...

 

[YNWA]

Do I need to use SQL to do this?

I am using query design view but not SQL.

My current SQL of query of counting contacts is...

SELECT Count([contactID]) AS [Number of Contacts]
FROM (tblPatients LEFT JOIN tblPatReferrals ON tblPatients.fldpatientID = tblPatReferrals.fldpatientID) LEFT JOIN tblContact ON tblPatReferrals.fldreferID = tblContact.referID
WHERE (((tblContact.ContactDate)>=[From Date] And (tblContact.ContactDate)<=[To Date]));

Any ideas?

Cheers

 

[RuralGuy]

I would create a separate query that uses the DISTINCT keyword and make sure it returns what you want. The use that query instead of the table in your other query. Yes, after creating the whole SELECT query you switch to SQL view and add the DISTINCT keyword.

 

[DocWebb]

I tried this to produce a list of UNIQUE email addresses out of our ms access db (tablename db fields include email, list and date):

SELECT DISTINCT tablename.email, *
FROM tablename
WHERE (((tablename.email) Is Not Null) AND ((tablename.list) Like "yes"))
ORDER BY tablename.date;

BUT I am still getting duplicate emails address in the list.

Any suggestions???

Thanks.

Doc