Help with this count query - SOLVED

[sharks13]

Hey all. So I'm relatively new to the query world. I just recently learned how incredibly helpful they can be. However, I'm having trouble with a query that should be somewhat simple. Basically, all I want to do is count the number of records based on date (one years time).

Right now I have a form with about 1300 records over 5 years or so. Using the field "Completion Date", I want to have a list of the years (on one query if possible) and I want the query to count the records for each year. So it would look something like this:

2002 143
2003 200
2004 211

And so on. I've been messing around with the totals sections and been looking online but so far I've had no luck.

 

[Joe4]

Create a new query in Query Builder. Assuming that your "Completion Date" field is in valid date format, enter the following calculated field to your query:
MyYear:Year([Completion Date])
Then add any other field from your table to the query (doesn't matter which one).

Now, click on the Totals icon (looks like a Sigma or Summation sign). This will add a Totals row under each field in your query. Leave the "Group By" value under your "MyYear" calculated field. But under the other field, change "Group By" to "Count".

View the results, and you will have what you are after!

 

[sharks13]

Ah that's exactly what I wanted. I wonder why I couldn't find that solution online. Must have had poor wording in the search. Oh well. Thanks for the quick response!

Edit: Actually if you have a bit more time I'd like to ask one more question. Is it possible to take out certain rows of a query. The reason I'm asking is because I'm making charts and graphs based off these queries and some of the data I would rather just elminate rather than having it show up.

 

[Joe4]

Sure. Simply add Criteria under the appropriate field in Query Builder to filter out unwanted data.

For example, let's say that you want to exclude years less than 2000. Then under your Year field in Query Builder, in the Criteria row under that field, enter:
>2000

By the way, for more information on the first part I helped you out with, check out "Aggregate Query" in Access's built-in help.

 

[sharks13]

Ah Ok. That's exactly what I needed. Thanks again for the help.