Tring to create a sum expression while excluding any duplicates

[Blair]

I have field that is a yes/no field, if yes it is a missed female, if no she
has babies.Problem is this female can show as missed twice because she could
have mated with two different males. But in reality she can only miss the
once. So if the [female#] is in the query more than once I still only want
it to be counted once not 2 or 3 times.
I tried this expr. Misses: Sum(IIf([MaleReport1]![MISSED]=True,1,0)) but it
is not accurate because it includes the duplicates.
I tried something like this, but it didn't work Misses:
Sum(IIf([MaleReport1]![MISSED]=True And [MaleReport1]![FEMALE #] <>
[MaleReport1]![FEMALE #] ,1,0))

can anyone help
Thanks Blair

 

[KARL DEWEY]

You can do it with two queries. First query named Query104.
SELECT MaleReport1.FemaleID
FROM MaleReport1
WHERE (((IIf([MISSED]=True,1,0))=1))
GROUP BY MaleReport1.FemaleID;

SELECT Count(Query104.FemaleID) AS CountOfFemaleID
FROM Query104;

You can do it with one if you know how to do subqueries.

 

[Blair]

Thanks for your help, I got what I wanted using this in an expression. Your
solution probably is simpler, but I have been all night figuring it out and
just check my post. This is my solution, but not simple.
Misses: Sum(IIf([MaleReport1]![3rd MATING] Is Not Null And
[MaleReport1]![4TH MATING] Is Not Null And [MaleReport1]![4TH
MATING]=[MaleReport1]![MALE NUMBER] And
[MaleReport1]![MISSED]=True,1,0))+Sum(IIf([MaleReport1]![2nd MATING] Is Not
Null And [MaleReport1]![3rd MATING]=[MaleReport1]![MALE NUMBER] And
[MaleReport1]![4TH MATING] Is Null And
[MaleReport1]![MISSED]=True,1,0))+Sum(IIf([MaleReport1]![1st MATING] Is Not
Null And [MaleReport1]![2nd MATING]=[MaleReport1]![MALE NUMBER] And
[MaleReport1]![3rd MATING] Is Null And
[MaleReport1]![MISSED]=True,1,0))+Sum(IIf([MaleReport1]![1st MATING] Is Not
Null And [MaleReport1]![1st MATING]=[MaleReport1]![MALE NUMBER] And
[MaleReport1]![2nd MATING] Is Null And [MaleReport1]![MISSED]=True,1,0))

You can do it with two queries. First query named Query104.
SELECT MaleReport1.FemaleID
FROM MaleReport1
WHERE (((IIf([MISSED]=True,1,0))=1))
GROUP BY MaleReport1.FemaleID;

SELECT Count(Query104.FemaleID) AS CountOfFemaleID
FROM Query104;

You can do it with one if you know how to do subqueries.

I have field that is a yes/no field, if yes it is a missed female, if no she
has babies.Problem is this female can show as missed twice because she could
have mated with two different males. But in reality she can only miss the
once. So if the [female#] is in the query more than once I still only want
it to be counted once not 2 or 3 times.
I tried this expr. Misses: Sum(IIf([MaleReport1]![MISSED]=True,1,0)) but it
is not accurate because it includes the duplicates.
I tried something like this, but it didn't work Misses:
Sum(IIf([MaleReport1]![MISSED]=True And [MaleReport1]![FEMALE #] <>
[MaleReport1]![FEMALE #] ,1,0))

can anyone help
Thanks Blair