CODEWITHSUNDEEP
ms-accessdate
rick
rick

Reputation:

Query by month from date field

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.

Upvotes: 1

Views: 50855

Answers (3)

tpdi
tpdi

Reputation: 35171

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!

Upvotes: 8

David-W-Fenton
David-W-Fenton

Reputation: 23067

I wouldn't do this in the report's recordsource. I'd make the recordsource a regular SELECT statement and use the report's sorting/grouping. If you group on a date field (one that is really date type), you get the choice to GROUP ON:

Each Value (default) Year Qtr Month Week Day Hour Minute

I think this is faster than a GROUP BY on a function, but someone who was interested should actually try it.

Certainly if your SELECT with GROUP BY has no WHERE clause, it's going to be a lot more efficient if you run the report with filtered values.

Upvotes: 0

Fionnuala
Fionnuala

Reputation: 91376

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

Upvotes: 2

Related Questions