To count records in a query (1 Viewer)

JRPMD · Apr 2, 2019

Hello, I have a field in a table with a restricted number of values from 1 to 10.
I consider each number a category and I want to count how many records there are in each one.
I can group and count the records in a query but there are only nine criteria to fill in , so the last number cannot be counted.
Is there another or a better way to do this?
For example , a visual basic code?
Thank you very much!

Ranman256 · Apr 2, 2019

Can you not use a count query?
Or
Use DCOUNT()

plog · Apr 2, 2019

I consider each number a category and I want to count how many records there are in each one.

That can be accomplished with a simple aggregate query:

Code:

SELECT CategoryNumber, COUNT(CategoryNumber) AS TotalRecords
FROM YourTableNameHere
GROUP BY CategoryNumber;

But this part doesn't make sense:

I can group and count the records in a query but there are only nine criteria to fill in , so the last number cannot be counted.

You lost me there. What does criteria have to do with this? Please demonstrate your issue with data please.

JRPMD · Apr 4, 2019

Thank you very much for your useful answers.
Sorry if a I lost you with the criteria I´m trying to use ; I don't know the exact translation of "criterios" from my spanish version of MS Access.
Finally I grouped a field in a query in this way : "1" or "2" or "3" or "4" , etc .
Thanks again!

The_Doc_Man · Apr 4, 2019

OK, if you wanted something shorter AND (for example) 1 to 9 would work, you could use the BETWEEN ... AND relational operator:

Code:

A BETWEEN 1 AND 9

Easier than a chain of OR segments.

JRPMD · Apr 5, 2019

Thank you very much!

Pat Hartman · Apr 7, 2019

Use the crosstab query wizard to build this query if you want the columns displayed in a single row. The crosstab will support a maximum of 254 values and so if you add an 11th value, you won't have to change anything.

To display the counts in a list, use the following.

Select Category, Count(*) As CatCount From YourTable

The_Doc_Man · Apr 7, 2019

For future reference: If you are doing from 1 to 9, then A BETWEEN 1 AND 9 works great. But suppose you wanted to do 1, 3, 5, 7, 9? Try A IN (1,3,5,7,9) which works ok for short lists. If the list is a lot longer, you can also use a reference table that contains your list in, say, field B. Then use A IN ( SELECT B FROM ReferenceTable ) as a way to select when you have a lot of non-contiguous options.

To count records in a query (1 Viewer)

JRPMD

Registered User.

Ranman256

Well-known member

plog

Banishment Pending

JRPMD

Registered User.

The_Doc_Man

Immoderate Moderator

JRPMD

Registered User.

Pat Hartman

Super Moderator

The_Doc_Man

Immoderate Moderator

Similar threads

Users who are viewing this thread