Count without the duplicates

Question

On my report I have some records that have duplicates by Customer Number. Since they are duplicates I hid all leaving only the first one to show.

Now, I wanna put a total at the end of the report to count the number of Customers ignoring the duplicates. For example, if a customer's name shows up 3 times or 7 times on the report it should still count as 1. So that way at the end of the report I know how many customers and not how many incidents.

Any help?

Answer 1

Robbie Doo wrote:

Yes, RLTrapp. But that gives me the count of that client (as many as

there is). I need to show that client as 1 time even though the

client shows up more than once. :)

Yep, but in this group footer (or header) you can:

• create an (invisible) textbox "txtCountCust" with control source:

=1

• set its property "Running Sum" to:

Over All

• create a text box in the report footer or where you want to show it

with control source:

=[txtCountCust]

---

cu

Karl

*******

Access-FAQ (German/Italian): http://www.donkarl.com

Answer 2

Robbie Doo wrote:

Yes, RLTrapp. But that gives me the count of that client (as many as

there is). I need to show that client as 1 time even though the

client shows up more than once. :)

Yep, but in this group footer (or header) you can:

• create an (invisible) textbox "txtCountCust" with control source:

=1

• set its property "Running Sum" to:

Over All

• create a text box in the report footer or where you want to show it

with control source:

=[txtCountCust]

---

cu

Karl

*******

Access-FAQ (German/Italian): http://www.donkarl.com

I am trying to accomplish the same thing, I have the set up you mentioned, with the expression "=Count([ID_NUMBER])"  this reports all the entries, i need to exclude duplicates.

does the "=1" follow a command?

Did you read what I wrote at AWF?  Funny how I wrote almost the exact same thing as Karl did even though I hadn't seen his answer.

Again, you put a group on the ID_Number field.  And I would use a Group Footer. 

Put a text box in that footer and name it txtMyCountofID

Put

=1

EXACTLY AS SHOWN IN BOLD in that text box's CONTROL SOURCE

Select the text box while in design view and

find the RUNNING SUM property (should be on the DATA tab of the properties)

Change it from NONE to OVER ALL.

Then put a text box in your REPORT FOOTER.

Set its control source to

=txtMyCountofID

Answer 3

Robbie,

That depends on how the data is being pulled in from the query that you create. Can you post the SQL for your query?

---

Lynn Trapp MCP, MOS, MCAS

Answer 4

Yes, RLTrapp. But that gives me the count of that client (as many as there is). I need to show that client as 1 time even though the client shows up more than once.  :)

Answer 5

You need to group your report on Customer Number and put the count of Customer Number in the Customer Number Footer.

---

Lynn Trapp MCP, MOS, MCAS