Ms-access – Query by month from date field

datems-access

I have a set of Access d/b's grouped already by year. within a given year, I have a field caleld REPORTDATE which is a standard mm/dd/yyyy field. However, I need to produce queries that return data by the month. For example, I just want to see records for Jan, recs for Feb, Recs for March, etc., so that I can sum them and work wwith thm.

Do I use an expression in the query design view Criteria field?

Thanks in advance.

Best Solution

I just want to see records for Jan, recs for Feb, Recs for March, etc., so that I can sum them and work wwith thm.

You can do all of that in one sql statement:

select month(reportdate), sum( the column you wish to sum )
from tablename
group by month(reportdate);

BUT WAIT THERE'S MORE!

Further say that there are several salepersons selling stuff, and you wish to show each salesperson's sales by month

select month(reportdate), salesperson, sum( the column you wish to sum )
from tablename
group by month(reportdate), salesperson;

That shows the sum per month per salesperson.

You know the Germans always make good stuff!

What it you wanted to see the same sums, but rtaher than comparing salespeople against each other in each month, you wanted to compare, for each salesperson, how they did from one month to another?

Just reverse the order of the group by:

select month(reportdate), saleperson, sum( the column you wish to sum )
from tablename
group by salesperson, month(reportdate);

Tacos, Fettuccini, Linguini, Martini, Bikini, you're gonna love my nuts!

The power of SQL! As seen on TV! Order now!

"select month(reportdate), sum( the column you wish to sum )from tablenamegroup by month(reportdate);" THIS IS VERY HELPFUL, THANK YOU. AND YOU ARE HILARIOUS. HOWEVER, can you clarify for me where the heck this code goes?! In the expresison Builder or what? Thank you SO much. – rick (19 mins ago)

In Access, I think from the graphical Query Builder thing's menu, select edit|SQL, and just type. And never go back to graphical!

You're a hard-charging forward-thinking entrepreneurially-minded man on the move! This is not your father's Oldsmobile! You wouldn't use an on-screen keyboard to type a document, dragging and dropping letters on the page, would you?! So why do that to build a SQL Query? Get into SQL! AS SEEN ON TV! All the cool kids and hep cats are doin' it! Order NOW!

Related Question