By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,644 Members | 2,439 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,644 IT Pros & Developers. It's quick & easy.

Access 2000: Need a count of entries by state.

P: n/a
I have an Access 2000 database (42,000+ records) for which I need a
count of records by state (AL=320, AK=92, NY=1,932, etc). Obviously,
I don't want to (and probably can't...) do the count manually. I know
there is a Count function, but I don't know how to make it work. Can
anyone help me, please?

As an aside, I tried to export the data to an Excel spreadsheet, but
it will NOT save as a new file. I have no idea why that is the case
(not read only, no macros or extraneous code that I can see), but then
I did not create the db.

Thanks again!!!
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
C. Homey previously wrote:
I have an Access 2000 database (42,000+ records) for which I need a
count of records by state (AL=320, AK=92, NY=1,932, etc). Obviously,
I don't want to (and probably can't...) do the count manually. I know
there is a Count function, but I don't know how to make it work. Can
anyone help me, please?
Create a new query based on the table whose records you want to count.
Add the State field and any other field to the query grid. (The second
field must be one that contains an entry for every record).
Convert the query to a 'Totals' query by clicking the Totals button on the
toolbar.
A Total row now appears in the query grid. It says 'Group By' for both
columns. Click into the second column, in the Group By entry. Now click
the drop-down arrow that appears. Select Count from the list.
Save the query.
Run the query.
As an aside, I tried to export the data to an Excel spreadsheet, but
it will NOT save as a new file. I have no idea why that is the case
(not read only, no macros or extraneous code that I can see), but then
I did not create the db.

Thanks again!!!


Not enough information.
What happens when you try to export it?
Is there an error message?
Regards

Peter Russell

Nov 12 '05 #2

P: n/a
Create a query using GROUP BY clause:

SELECT COUNT(*) FROM MyTable GROUP BY State

Cheers,
Pavel

"C. Homey" wrote:

I have an Access 2000 database (42,000+ records) for which I need a
count of records by state (AL=320, AK=92, NY=1,932, etc). Obviously,
I don't want to (and probably can't...) do the count manually. I know
there is a Count function, but I don't know how to make it work. Can
anyone help me, please?

As an aside, I tried to export the data to an Excel spreadsheet, but
it will NOT save as a new file. I have no idea why that is the case
(not read only, no macros or extraneous code that I can see), but then
I did not create the db.

Thanks again!!!

Nov 12 '05 #3

P: n/a
THANK YOU BOTH!!! I got the count to work and what a time/life saver!!!!
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.