Not counting duplicates

[Guest]

The setup is: Regions have region managers, RMs have Sales guys, Sales guys
have Ops guys who work with them. An Ops guy has a 1 to many relationship
with Sales guys.
The report shows RMs and the Ops guys (1 RM to many Ops guys) and the Sales
guys related to each Ops guy. This means that Ops guys are repeated on the
report. No problem (Hide Duplicates). I want a count of the Ops guys for
each RM and not count the duplicate appearances of the Ops guy. =count (of
any reported field) returns a count of the Sales guys
Any ideas are appreciated.
Thanks in advance.
WAL50

 

[Graham Mandeno]

Hi Wal

Count() will always give you a count of the records with a non-null value in
the counted field. If that field is in the middle table of a
one-to-many-to-many query, then you will get the number of related records
in the right-hand table, not the middle table.

The solution to this in SQL involves using subqueries as "virtual tables"
and is unnecessarily complex for your requirements in this report.

Instead, use DCount. Instead of =Count([SalesGuyID]) in your textbox, use:

=DCount("*", "SalesGuyTable", "[RMField]=" & [RM-ID Field])