duplicates in query

[gregatvrm]

I am trying to find how many people of a certain sex returned in the same
year and it keeps giving me both records of the same person. How do i get it
to return only the one record. Also if there is a null response it needs to
return a zero.

SELECT Count(History.RESID) AS NumberOfDups
FROM RESIDENT INNER JOIN History ON RESIDENT.RESID = History.RESID
WHERE (((History.ExitDate) Between #1/1/2007# And #12/31/2007#) AND
((RESIDENT.Sex)="f")) OR (((History.ExitDate) Is Null))
GROUP BY RESIDENT.LastName, RESIDENT.FirstName, History.RESID
HAVING (((Count(History.RESID))>1));

 

[KARL DEWEY]

Maybe this is what you want.
SELECT Sum(IIf(([History].[RESID]) Is Null,0,1)) AS NumberOfDups,
RESIDENT.LastName, RESIDENT.FirstName
FROM RESIDENT LEFT JOIN History ON RESIDENT.RESID = History.RESID
WHERE (((History.ExitDate) Between #1/1/2007# And #12/31/2007#) AND
((RESIDENT.Sex)="f")) OR (((History.ExitDate) Is Null))
GROUP BY RESIDENT.LastName, RESIDENT.FirstName, History.RESID
HAVING (((Sum(IIf(([History].[RESID]) Is Null,0,1)))<>1));

 

[gregatvrm]

Sorry for the late response. This works great. Your Awesome!

Maybe this is what you want.
SELECT Sum(IIf(([History].[RESID]) Is Null,0,1)) AS NumberOfDups,
RESIDENT.LastName, RESIDENT.FirstName
FROM RESIDENT LEFT JOIN History ON RESIDENT.RESID = History.RESID
WHERE (((History.ExitDate) Between #1/1/2007# And #12/31/2007#) AND
((RESIDENT.Sex)="f")) OR (((History.ExitDate) Is Null))
GROUP BY RESIDENT.LastName, RESIDENT.FirstName, History.RESID
HAVING (((Sum(IIf(([History].[RESID]) Is Null,0,1)))<>1));

I am trying to find how many people of a certain sex returned in the same
year and it keeps giving me both records of the same person. How do i get it
to return only the one record. Also if there is a null response it needs to
return a zero.

SELECT Count(History.RESID) AS NumberOfDups
FROM RESIDENT INNER JOIN History ON RESIDENT.RESID = History.RESID
WHERE (((History.ExitDate) Between #1/1/2007# And #12/31/2007#) AND
((RESIDENT.Sex)="f")) OR (((History.ExitDate) Is Null))
GROUP BY RESIDENT.LastName, RESIDENT.FirstName, History.RESID
HAVING (((Count(History.RESID))>1));