Distinct Count (XP)

Topic

New Reply

Hi,

I have a report that I’m trying to get a count of records by [client name]. When I use =count([client name]) I get the total number but what I need is the total number of distinct records.

Thanks,
Leesha

Reply | Quote

Replies

Lets’s say your report is based upon a query called [qry rpt Clients].
You could build another query that counts the number of Clients, eg:
SELECT [client name], Count(*)
FROM Tablename
GROUP BY [client name]
Then in query [qry rpt Clients] include this latest query joined on Client.

Reply | Quote

Hi Pat and Wendell!

I should’ve been more clear. I need a formula for the report vs doing it in the query as if I obtain the unique records in the query it throws off other values. What I’m looking for is a formula that I can use in the report itself that will count the number of unigue [names].

Thanks,
Leesha

Reply | Quote

In that case I think you will have to put some code behind the report to determine the value and then populate an unbound control. You can probably use the DCount() function but it should be based on a query that returns the unique records for your report data source.

Reply | Quote

Hi Wendell,

I will try dcount() as you mentioned. Ive seen that used but was not sure the exact purpose.
Thanks,
Leesha

Reply | Quote

Hi Wendell,

I will try dcount() as you mentioned. Ive seen that used but was not sure the exact purpose.
Thanks,
Leesha

Reply | Quote

In that case I think you will have to put some code behind the report to determine the value and then populate an unbound control. You can probably use the DCount() function but it should be based on a query that returns the unique records for your report data source.

Reply | Quote

Hi Pat and Wendell!

I should’ve been more clear. I need a formula for the report vs doing it in the query as if I obtain the unique records in the query it throws off other values. What I’m looking for is a formula that I can use in the report itself that will count the number of unigue [names].

Thanks,
Leesha

Reply | Quote

By distinct, I presume you mean unique records. There is a property for queries that can be set to return just the unique records – it is called Unique Records. There is also a property Unique Values, which is mutually exclusive, but you want Unique Records. That in turn creates a SELECT DISTINCTROW statement in the SQL string, and when you sum or count with that property set, you should get the correct values.

Reply | Quote

By distinct, I presume you mean unique records. There is a property for queries that can be set to return just the unique records – it is called Unique Records. There is also a property Unique Values, which is mutually exclusive, but you want Unique Records. That in turn creates a SELECT DISTINCTROW statement in the SQL string, and when you sum or count with that property set, you should get the correct values.

Reply | Quote