total entries

kitty77

Registered User.
Local time
Today, 01:52
Joined
May 27, 2019
Messages
715
What would be the best way to figure out how many (count) entries are for each entry.
Example, I have a table that has a column that has the color car that they bought. So, I would to know how many records have blue, red, white, etc..
The table has 55K records.

Thanks
 
What you want is called a totals query:


Give that link a read and apply it to your data
 
Not sure which to choose to count my data. When I group by and use count, I get 1 for all?
 
What is your SQL? Sounds like you are including more fields than necessary to get the results you stated
 
you can also try using Crosstab query (say, Query1):

TRANSFORM Count(Table1.ColorColumn) AS CountOfcolor
SELECT "count" AS Expr1
FROM Table1
GROUP BY "count"
PIVOT Table1.ColorColumn;

then, you can use Dlookup to get the count:

Nz(Dlookup("Red", "Query1"), 0)
Nz(Dlookup("Blue", "Query1"), 0)
 
When you create a Totals query and get a count of 1 for all rows, you have included a column or columns in the Select that makes the row unique and so it cannot be summarized. Start by removing any primary key.

The query would probably be as simple as

Select Color, Count(*) As CarCount
From YourTable
Group by Color;

Something like the following would give you a count by model, by color
Select Model, Color, Count(*) As CarCount
From YourTble
Group By Model, Color;

If I were to include CarID, in the Select clause, I would get one row for each unique CarID and CarCount would be 1 for each row.

Using DCount() is NOT the qay to approach this problem.
 

Users who are viewing this thread

Back
Top Bottom