Count of Groups

[angellijah]

I have a database that lists clients, the case # related to that client, etc.
multiple clients may have the same case #. I want to count the number of
different cases, but I can only seem to get a record count which counts the
same case once for each client. Any suggestions?

 

[Jeff Boyce]

"How" depends on "what", as in "what table structure are you using"?

If you have a well-normalized table structure, you'll probably have a
tblCase table. You could use a query to find the unique cases.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Marshall Barton]

I have a database that lists clients, the case # related to that client, etc.
multiple clients may have the same case #. I want to count the number of
different cases, but I can only seem to get a record count which counts the
same case once for each client.

Try something like:

SELECT Count(*) As CaseCount
FROM (SELECT DISTINCT CaseNum FROM table)

 

[KARL DEWEY]

Try this --
SELECT Count(SELECT [XX].[Case #] FROM YourTable AS [XX] GROUP BY [XX].[Case
#] ) AS Number_of_cases
FROM YourTable;

 

[angellijah]

I'm having errors. What is the XX representing? My table name is Client
Info, but i figured that is to replace "yourtable".

Try this --
SELECT Count(SELECT [XX].[Case #] FROM YourTable AS [XX] GROUP BY [XX].[Case
#] ) AS Number_of_cases
FROM YourTable;

--
Build a little, test a little.

I have a database that lists clients, the case # related to that client, etc.
multiple clients may have the same case #. I want to count the number of
different cases, but I can only seem to get a record count which counts the
same case once for each client. Any suggestions?

 

[KARL DEWEY]

I'm having errors.
That does not tell me anything. What kind of errors? What is the error
message?
Is the data wrong? In what manner is the data wrong?
[XX] is an alias for the table name.


--
Build a little, test a little.

I'm having errors. What is the XX representing? My table name is Client
Info, but i figured that is to replace "yourtable".

Try this --
SELECT Count(SELECT [XX].[Case #] FROM YourTable AS [XX] GROUP BY [XX].[Case
#] ) AS Number_of_cases
FROM YourTable;

--
Build a little, test a little.

I have a database that lists clients, the case # related to that client, etc.
multiple clients may have the same case #. I want to count the number of
different cases, but I can only seem to get a record count which counts the
same case once for each client. Any suggestions?