# Help with this count query - SOLVED

#### sharks13

##### Board Regular
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.

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### 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

##### Board Regular
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

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

Replies
5
Views
293
Replies
5
Views
2K
Replies
3
Views
3K
Replies
3
Views
1K
Replies
5
Views
1K

1,191,534
Messages
5,987,149
Members
440,082
Latest member
belodelokelo

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back