Query Help

[Bremen217]

Sum of Records by Month Query Help

Hi Gang!

I have raw data that (in part) looks like this in an access database:

Contract release date Update Resource
01/26/10 Lisa
12/22/10 Lisa
12/07/10 Lisa
03/09/10 Linda
09/24/10 Linda
04/06/10 Linda
03/18/10 Linda
10/07/10 Linda
07/17/09 Julie
07/26/10 Gayle
04/27/10 Dianne
12/01/10 Lisa

And continues on and on throughout the year. Records may be added almost daily. I need a query to show me the toal number of records for each person for each month so the output would look something like this:

Jan Feb Mar (etc, etc)
Lisa 4 5 3
Dianne 3 6 4
Gayle 12 7 3

(Please note the numbers in that chart are example only.)

Basically it looks at the date and the name and totals the records for each employee by each month. The query would be run once a month to accomodate adds or changes. Obviously this data will in turn be used in an Access report.

Can anyone help?
Thanks!

 

[John Big Booty]

Have a look at a Totals Query.

You will first need to group by the month using the DatePart() function to extract the months, and then group by user, and finally sum or count the records for each person.

 

[Bremen217]

Thank you very much John! I did the reading you suggested (I had played around with the Total Query prior to your post and have that concept). I started playing around in an access query with the datepart as well. I am the first to admit that I am by far no SQL master, so please bear with me if a question seems stupid.

So I get date part to pull just the month and then I can group like months together to get a count. However this database hase records for 2009, 2010, and now 2011. Can I get it to only group up the last 12 months somehow instead of the entire database?

Thanks!