Query by month from date field

Question

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.

Solution 1

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!

Solution 2

You can use format, for example:

Format([REPORTDATE],"mmm yy")

Or Month:

SELECT * FROM Table WHERE Month([REPORTDATE]) = 10

An outline of query that may suit, paste this into the SQL view of the query design window, changing table to the name of your table:

SELECT Format([REPORTDATE],"yyyy mm"), Count([ReportDate])
FROM Table
GROUP BY Format([REPORTDATE],"yyyy mm")

Comments

[Sergej Andrejev]

groups by month and this is what you want

[Admin]

OK, but exactly where do I put that expression?! If I enter it like you have it above into the Criteria box of the REPORTDATE field and then run the Q, it returns nothing. I am clueless.

[Admin]

OK maybe I am not saying htis right. Here's what I need to do: I have records with dates, but I want to create a new field, I guess, called ReportMonth, so that I can generate fields/reports grouped by month.

[Fionnuala]

The first example would be used for Group By (the sum button) and the expression would go on the Field line, instead of REPORTDATE. The second example would be pasted in the SQL view of the query design window, you would have to change Table to the name of your table.

[Admin]

In other words, I need to automatically generate a Q or report of all the records broken down (grouped) by month.

[Fionnuala]

Your comment crossed with mine. use the first example, on the field line, it will create the new field you want. It will return Jan 09, Feb 09, but you can change to Format([REPORTDATE],"mm") or Format([REPORTDATE],"m") or Format([REPORTDATE],"yyyy mm") and so on.

[Admin]

"The second example would be pasted in the SQL view of the query design window, you would have to change Table to the name of your table" THAT IS HELPFUL!!! THANK YOU SO MUCH, Remou.

[Admin]

Actually, THIS is what works: "The first example would be used for Group By (the sum button) and the expression would go on the Field line, instead of REPORTDATE. "

[Admin]

Oh boy, I hate to sound like a complete dummy, but is the "SQL view of the Query design window" the same as the expression builder?!

[Admin]

"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.

[Fionnuala]

No. Use the View menu to select SQL View, or choose SQL view from the dropdown list on the toolbar - it will probably be the first item on the far left of the toolbar. SQL view will give you a large blank box.

[Admin]

Hey I got it to work--thanks again. That is very powerful--I hoe you only use these powers for good!