total entries

[kitty77]

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

 

[plog]

What you want is called a totals query:

Count data by using a query - Microsoft Support

Understand ways to count data, count data by using a Total row or by using a totals query, and view the Aggregate function reference in Access.

support.microsoft.com

Give that link a read and apply it to your data

 

[kitty77]

Not sure which to choose to count my data. When I group by and use count, I get 1 for all?

 

[plog]

What is your SQL? Sounds like you are including more fields than necessary to get the results you stated

 

[arnelgp]

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)

 

[Pat Hartman]

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.