asked on

Counting distinct records

Hello
I have a (probably simple) question about access reporting.

I have a report that uses a query, resulting in some rows having the same value for one field (Job ID).
I have a text box in the report footer which has the Control source set to =Count([Job ID])

This works of course, but gives the total number of records, where I want the total of distinct records.

Is there any syntax that I can insert into that report text box to give me the Distinct value. I tried Dcount([Job ID]) but it didn’t like that!!

Best regards

Richard

rltomalin

ASKER

I also have the issue where I want to use a Sum function, but only applying to the distinct records.
I assume the solution will be similar.

Regards

Richard

SOLUTION

als315

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

peter57r

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Tobias

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rltomalin

ASKER

Thanks to all three of you for your rapid feedback. I have chosen to use the solution from Peter because it looked easiest to follow (for me) and simple. It seems to be working OK.
I gave some points to the others beacuse I am sure they are valid solutions.

Hope that is OK.

Regards

Richard