Eliminate duplicates from expression

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Blair - 14 May 2006 01:05 GMT

I have this expression to find the number of females, it totals 338 but 67
of the count is dups or trips of the same female number. the count should be
271 if I can exclude the duplicates and triples.

Total Females: Sum(IIf([MaleReport1]![WHELPING DATE] Is Not Null Or
[MaleReport1]![WHELPING DATE] Is Null,1,0))

[Female#] field set as count will also result in a count of 338 also.

any ideas on how to eliminate the duplicates?
Thanks Blair

Reply to this Message

kasab - 14 May 2006 02:23 GMT

If you add another field to the query, containing the female# and group by
this field, does this help? This new field may need to go before the field
containing your expression. If this doesn't help, open your query in SQL
view, insert the works DISTINCT Female# between select and the first field
you already have in the query. If you don't want to display the female# field
you added, you can turn off the display by unchecking the box in the field's
column in Design View.

HTH
kasab

> I have this expression to find the number of females, it totals 338 but 67
> of the count is dups or trips of the same female number. the count should be
[quoted text clipped - 7 lines]
> any ideas on how to eliminate the duplicates?
> Thanks Blair

Reply to this Message

kasab - 14 May 2006 02:26 GMT

Oops, sorry. Typo: What I should have typed is "... insert the words DISTINCT
..."

> If you add another field to the query, containing the female# and group by
> this field, does this help? This new field may need to go before the field
[quoted text clipped - 18 lines]
> > any ideas on how to eliminate the duplicates?
> > Thanks Blair

Reply to this Message