Access 97 : Getting a count for multiple entries

[kenshorin]

Hi all -

I am new to Access 97, and I need help! I have a query running from a large database to pull records by date, and what I need to do is find some way to get a count using two fields; I have a field for ID# and a field for CODE.

I need to find a way to get a count of all the records with the same ID# and CODE entries, and am hoping that there's a way to make it smart enough to auto-detect the unique values rather than having to input them manually.

 

[BFG10K]

That's easy. Build a select query with the table, add the ID# field and in its criteria put

.

Then either put a count in front of it or manually scroll to the end record and see what it is.

 

[Mucman]

umm or even easier... use the Count function

SELECT Count(*)
FROM table
WHERE id = '123'

 

[kenshorin]

Originally posted by: Mucman
umm or even easier... use the Count function

SELECT Count(*)
FROM table
WHERE id = '123'

Yeah I was figuring it would use the Count function, but I was having some trouble with the syntax to use it. The problem with this is, I have two fields by which I need to get the counts. Basically, the CODE field is for a status code (active, inactive, etc.) and ID# is for the town ID. I need to get a count of the number of actives and inactives, suspended, etc. for each town during a specified date range (to be inputted by the user). Theres a couple hundred town ID#s so I don't want to have to specify each one of those. I need to have a way of the query / macro being smart enough to give a count of the different codes for each town ID# in a specified time period. Is this going to be capable of doing that?

Originally posted by: BFG10K
That's easy. Build a select query with the table, add the ID# field and in its criteria put

.

Then either put a count in front of it or manually scroll to the end record and see what it is.[/quote]

Its not so much about getting a count, thats easy enough. I know how many I have by looking at the number of records; I need it to generate a solid number that can be exported to a number of different reports. I am really trying to use the Count function, but still having some issues with getting it to work with the multiple fields that need to be considered.

 

[Haircut]

You can do this:

Select count(*), code
from table
group by code

or if you want it done by item as well

Select count(*), item, code
from table
group by item, code

 

[kenshorin]

That was exactly what I was looking for, it managed to separate everything nicely. Thanks!