Count of Groups

  • Thread starter Thread starter angellijah
  • Start date Start date
A

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?
 
"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.
 
angellijah said:
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.
Click to expand...


Try something like:

SELECT Count(*) As CaseCount
FROM (SELECT DISTINCT CaseNum FROM table)
 
Try this --
SELECT Count(SELECT [XX].[Case #] FROM YourTable AS [XX] GROUP BY [XX].[Case
#] ) AS Number_of_cases
FROM YourTable;
 
I'm having errors. What is the XX representing? My table name is Client
Info, but i figured that is to replace "yourtable".

KARL DEWEY said:
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.


angellijah said:
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?
Click to expand...
Click to expand...
 
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.


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

KARL DEWEY said:
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.


angellijah said:
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?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top