count unique records on a report

mkobey
mkobey used Ask the Experts™
on
I am creating a report where each record may be repeated multiple times under a different group.  I would like to have a field that will sum the number of unique records, as each record may be repeated multiple times.

I put this text box in the header with control source set to:
=count(distinct[recordID]  where recordID is the unique record Identifier on the master table, but was unable to gain a result.

Please advise.

Thanks, mark
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Post what is in the report's record Source and filter properties.
Commented:
i think you'll have to calculate this outside the report's scope
say the report's underlying recordset is a query named qry1
add another query, qry1Count, that says
Select Distinct RecordID From qry1
then set the control at the header to: dcount("RecordID","qry1Count")
 

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial