Distinct Counts in Report

[Hacktastic]

Hello All,

I have a report that is the following

Customer Item amount
Chris - Apples - $100
Chris - Oranges - $100
Bobby - Apples - $1000
Jim - Apples - $1000
Total
# of people 4 - Total $ 2200

I would like to count distinctly the # of customers. regardless of the # of times they appear in the report. Namely # of people = 3

Please note that the report query in the back ground cannot change as I would like to view everyone's order. In addition I cannot split it into another report.

Any guidance would be greatly appreciated.

Chris

 

[randy700]

I think the DCount function will give you the answer you want.

Randy

 

[Hacktastic]

Hi Randy

No i tried Dcount and it isn't built to work in a report.

 

[dhookom]

You could group the report by Customer and use a text box in the group header:
[tt]
Name: txtCustCount
Control Source: =1
Running Sum: Over All
Visible: No
[/tt]
Add a text box in the report footer section:
[tt]
Control Source: =txtCustCount
[/tt]


Duane
Hook'D on Access
MS Access MVP

 

[Hacktastic]

Hi i dont get the above?

 

[dhookom]

How far did you get? What don't you get? Did you create a sorting and groupoing level on Customer?

Duane
Hook'D on Access
MS Access MVP

 

[Hacktastic]

Hi dhookom,

I placed the txtCustCount within the lists of the names(detail separator) and i placed the other txt box source to where I count the names(footer). count(name).

However it did not work, what do you mean by grouping?

 

[dhookom]

Grouping and Sorting" provides the functionality of displaying your records in a particular order and possibly creating groups or sections for aggregating your records. You generally shouldn't create a report without some type of sorting.

If you don't create a grouping and sorting level on your customer field, this solution will not work.

Duane
Hook'D on Access
MS Access MVP