Count Unique on a Report when Query is in Code

[camidon]

I have a report that is running from a form. I'm building the query from code using criteria specified on the form. I have a field that I want to count both the total number of customers and also the number of unique customers.

IE....

John
John
John
Chris
Chris
Chris
Chris
Frank
Frank

I would get 2 numbers that I could put into fields on the report like:

9
3

Because there are 9 total customers, but 3 unique ones.

 

[scriverb]

In your report if you perform Sorting and Grouping according to these Customer Name and create a control with a value of 1 you can easily in either the Group Header or Group Footer have a calculated control that sums these values.
=Sum(Me.NameValue1)

This can also be done at the Report Header and Footer for a total records in the report.

If that doesn't meet your needs you can certain perform a DCount function on the query results and count the unique records that meet your needs.

Let me know if I can be of more assistance.

Bob Scriver

 

[camidon]

I can't seem to get the syntax right for dcount. I tried to use that, but since I'm retarted, I can't get it.



Thanks,

Chris

 

[scriverb]

Please post the name of your query and its SQL code. Also, post your attempt at using DCount as that will help me with field names and report control names. I need to know the report control name for Name .

I will then send you back a DCount to use at both levels.

Bob Scriver

 

[GingerR]

to get the number 3:
write a new query based on this table.
bring down the Name (that you want to count)
make it a TOTALS query by selecting VIEW+TOTALS.
leave the Total as Group By.
When you run this query, it will show you how many unique customers there are.
Name the query qryUniqueCustomers

in your report, put a text box.
put

=dcount("Name","qryUniqueCustomers")

substitute your field name for "Name" above.

for getting the 9, we will do the same but just count all the customers in your table.

=dcount("Name","TableName")

where 'Name' = your field name,and of course TableName = your table name.

if there is criteria on your form that you are building the report from, you can put it in this same code with a Where clause:

=dcount("Name","TableName","[CompanyID] = " & forms!formname!CompanyID)

so this will only choose the customer names whose company ID was chosen or otherwise entered/selected on your form. substitite your form name for formname. you'll have to tweak the rest. play around with it...you'll figure it out )

g