Totaling Zip Codes

[HelenWM]

I am working on a database for a Free Store at my church. We want to know how many members we serve from each zip code. I have a Member's table which includes zip codes. I am able to sort by zip code but cannot figure out how to get a count for the number of members in each zip code. Thanks for any help with this simple issue.

[JoeM]

You want to do an Aggregate Query. Here's how:

1. Create a new query based on your table
2. Add the Zip code and any other field that is not blank (you could add the member ID field or the Zip Code field a second time)
3. Click on the Totals icon (looks like a Sigma)
4. This will add a Totals Row under each field. Leave the phrase "Group By" under the Zip Code field, but under your other field, change the Totals Row value to "Count".
5. View your results.

[HelenWM]

You want to do an Aggregate Query. Here's how:

1. Create a new query based on your table
2. Add the Zip code and any other field that is not blank (you could add the member ID field or the Zip Code field a second time)
3. Click on the Totals icon (looks like a Sigma)
4. This will add a Totals Row under each field. Leave the phrase "Group By" under the Zip Code field, but under your other field, change the Totals Row value to "Count".
5. View your results.

Thank you so much! Problem solved immediately!

[JoeM]

Great! Glad to help! Aggregate Queries are a great tool for doing counts, sums, mins, and maxes.

[HelenWM]

I have another question. Can you tell I am still learning!

I am trying to find the number of clients who shopped per year. I have a Clients table and an Items Received table that shows what date each client received items. The dates are m/d/yyyy - 11/12/2015.

[JoeM]

In your Aggregate Query, add the following calculated field (where "DateField" is the actual field name of your date field):

CalcYear: Year([DateField])

Now, if you "Group By" this field too, it will give you the counts for each Member/Year combination.

[HelenWM]

Feeling stupid!

Where do I put the code: CalcYear: Year([DateRec])? Criterion? Where? Under Client or Items Rec in query? I am able to get by month but need for year.

[JoeM]

See: http://www.fontstuff.com/access/acctut02.htm

[HelenWM]

I really appreciate all you have done for me. I cannot thank you enough. The link above took care of something I have tried to figure out for a long time. Keep up the good work! Your patience with a Novice is commendable!

[JoeM]

You are welcome. And thank you for the kind words!