Not Counting Duplicates in a Report

[cjb2y]

I know you can set you Query to not count duplicates, but my problem is that
I need the duplicates to show up in the Report, but I only want to count the
duplicates as one in the report, while still having them in the report. I
have Access 2003.

 

[Allen Browne]

How is the report sorted/grouped? Could you create a Group Footer section
(in the Sorting And Grouping box) for the field you want to count unique
values for?

You don't have to show this section, but in the group footer, include a text
box with properties like this:
Control Source =1
Running Sum Over All
Name txtGroupCountRS

Then in the Report Footer section, you can show the unique count with a text
box bound to:
=[txtGroupCountRS]

 

[cjb2y]

It is grouped by a district name. It is sorted by date after that. Your
recommendation gave me a total of all work tickets in the report. In the
group footer all it did was count up from 1, by district.

How is the report sorted/grouped? Could you create a Group Footer section
(in the Sorting And Grouping box) for the field you want to count unique
values for?

You don't have to show this section, but in the group footer, include a text
box with properties like this:
Control Source =1
Running Sum Over All
Name txtGroupCountRS

Then in the Report Footer section, you can show the unique count with a text
box bound to:
=[txtGroupCountRS]

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I know you can set you Query to not count duplicates, but my problem is
that
I need the duplicates to show up in the Report, but I only want to count
the
duplicates as one in the report, while still having them in the report. I
have Access 2003.

 

[Allen Browne]

If you can't do it that way (because of sorting/grouping), you may need a
DCount() expression to get the count.

It can be messy to get the Criteria of the DCount() to match the filter of
the report. If DCount() is not up to the task this ECount() might help:
http://allenbrowne.com/ser-66.html
as it can accept the criteria and give a distinct count.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

It is grouped by a district name. It is sorted by date after that. Your
recommendation gave me a total of all work tickets in the report.
In the group footer all it did was count up from 1, by district.

How is the report sorted/grouped? Could you create a Group
Footer section (in the Sorting And Grouping box) for the field
you want to count unique values for?

You don't have to show this section, but in the group footer,
include a text box with properties like this:
Control Source =1
Running Sum Over All
Name txtGroupCountRS

Then in the Report Footer section, you can show the unique count
with a text box bound to:
=[txtGroupCountRS]

I know you can set you Query to not count duplicates, but my
problem is that I need the duplicates to show up in the Report,
but I only want to count the duplicates as one in the report,
while still having them in the report.
I have Access 2003.