How do I perform a calculation in a report and/or query?

[Guest]

I'm trying to produce a report that I need to count the number of member
id's, but the member id could be listed more than once. I only want to count
it once. My report looks like this:

Provider Id
GrpNo. MemberId CaseNo. FromDate ThruDate


Any suggestions would be great.

 

[Duane Hookom]

Create a totals query (or series of queries) that is similar to your
report's record source. Have it return only one record that is the count
that you need. Add this query to your report's record source and don't feel
obligated to join it to any other table (as long as it returns only a single
record). Add your count field to the query grid and it will be available in
your report.

 

[Sazed]

Hi Duane,
Your suggestion works partially. It gives the total number of the field
to count, but, the other record duplicates itself for many times. How
can this problem be solved? Please let us know.

Thanks,
Sazed

 

[Duane Hookom]

Does your new "totals query" return only one row?