Count Unique Records in a Report

[BrookeLouw]

Hello!
I have a report created that is grouped by Customer. I need to count the
number of UNIQUE Rail Cars that were sent to the customer.
I'm using the =Count([Rail Car ID]) to count the total BUT I also need one
that counts the unique records.

Thanks in advance for your help...
Brooke Louw

 

[Allen Browne]

Perhaps you can use the Sorting And Grouping box to create a group footer on
the [Rail Car ID] field. In this footer, place a text box with properties:
Control Source: =1
Running Sum: Over All
Format: General Number
Visible: No
Name: txtRailCarIdRS

Then in the Report Footer section, place a text box to show the distinct
count, and set its Control Source to:
=[txtRailCarIdRS]

 

[BrookeLouw]

Thanks for your reply Allen!
This doesn't work... When I do =[txtRailCarIdRS] it gives me a parameter
value box when I switch to Report view.

My other issue is, there are several customers on this report and I need to
capture the unique records for each of these customers.... And Rail Car ID is
in Text format ex: HLMX9080

 

[Allen Browne]

If Access asks for the parameter, you have not spelled it the same way as
the text box you created, or you have it in the wrong section.

Doesn't matter whether it is Text or Number. The Count will still be a
number.

The suggested approach will not work if a [Rail Car ID] turns up multiple
times under different customers in the report (as from multiple rows all
together.)

 

[BrookeLouw via AccessMonster.com]

When I do your suggestion, I just get a Count... I need a distinct count.
Check it out..
The Report is as follows..

Plant
Customer/Customer Location
Product
List of Rail Cars used

Let's say there are 50 Detail Records meaning 50 Rail Cars BUT there could be
duplicate cars used in that count. I want to determine the distinct count of
Rail Cars.
I just created 2 Queries.. 1 that gives me the Distinct Count and a second
that gives me the Sum of that Distinct Count. But when I try and bring that
into my report, I get an error. I'm thinking it doesn't like my usage of a
querry of a querry.
UGH! I'm a my wits end with this madness

If Access asks for the parameter, you have not spelled it the same way as
the text box you created, or you have it in the wrong section.

Doesn't matter whether it is Text or Number. The Count will still be a
number.

The suggested approach will not work if a [Rail Car ID] turns up multiple
times under different customers in the report (as from multiple rows all
together.)

Thanks for your reply Allen!
This doesn't work... When I do =[txtRailCarIdRS] it gives me a parameter

[quoted text clipped - 5 lines]

is
in Text format ex: HLMX9080

 

[Allen Browne]

When you say there could be duplicates, where?
In the *same* grouping? Or in a different one?

The technique I suggested will work if the duplicates are in the same
grouping. It will not work if they are in a different one.

An alternative approach would be to use something like this ECount()
function to read the non-duplicate count from a suitable query:
http://allenbrowne.com/ser-66.html

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

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

When I do your suggestion, I just get a Count... I need a distinct count.
Check it out..
The Report is as follows..

Plant
Customer/Customer Location
Product
List of Rail Cars used

Let's say there are 50 Detail Records meaning 50 Rail Cars BUT there could
be
duplicate cars used in that count. I want to determine the distinct count
of
Rail Cars.
I just created 2 Queries.. 1 that gives me the Distinct Count and a second
that gives me the Sum of that Distinct Count. But when I try and bring
that
into my report, I get an error. I'm thinking it doesn't like my usage of
a
querry of a querry.
UGH! I'm a my wits end with this madness

If Access asks for the parameter, you have not spelled it the same way as
the text box you created, or you have it in the wrong section.

Doesn't matter whether it is Text or Number. The Count will still be a
number.

The suggested approach will not work if a [Rail Car ID] turns up multiple
times under different customers in the report (as from multiple rows all
together.)

Thanks for your reply Allen!
This doesn't work... When I do =[txtRailCarIdRS] it gives me a
parameter

[quoted text clipped - 5 lines]

is
in Text format ex: HLMX9080