Counting Distinct Values - People Who Appear in Related Table

[MatthewR]

I think this is pretty basic, but I can't figure it out. I want to count the number of people from a table, tblVictimsPerps, that appear in a related table, tblRelationships, where a few criteria are met. If a person appears multiple times, my current query counts each time they appear. But, I only want to count them once. I tried changing the query's "Unique Values" property to Yes, but that did not work. What should I do? Here is the SQL:

SELECT tblInjunctionHistory.ActiveOrExpiredInjunction, Count(tblVictimsPerps.PersonID) AS CountOfPersonID
FROM ((tblFatalIncidents INNER JOIN tblVictimsPerps ON tblFatalIncidents.[Fatal Incident #] = tblVictimsPerps.Incident) LEFT JOIN tblInjunctionHistory ON tblVictimsPerps.PersonID = tblInjunctionHistory.Person) INNER JOIN tblRelationships ON tblVictimsPerps.PersonID = tblRelationships.Perpetrator


WHERE (((tblVictimsPerps.VictimOrPerp)="Perpetrator") AND ((tblFatalIncidents.DataComplete)=True) AND ((tblRelationships.Intimate)=1))
GROUP BY tblInjunctionHistory.ActiveOrExpiredInjunction;





Thanks in advance for your help!

Matthew

[June7]

Build a DISTINCT query that returns each person only once.

Then reference that query in another query to do the count.


Did you try deleting the second attachment with Manage Attachments in the Advanced post editor?

[MatthewR]

Thanks very much, June. Let me give that a try, and I'll let you know if I have any issues.

Also, thanks for the tip on the extra attachment. I deleted it with the Advanced post editor.

[MatthewR]

Woo hoo! It worked! Thanks so much.