Query Sum based on date range - Access Developer Forum


	Back to Access Developer Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Query Sum based on date range

Rose Lavoie

2 years ago

I am building a query for my database to return the total value of the following:

Manual Hours
Wire Hours
CNC Hours

During specific time frames (previous month, currently month and next month).

The 2 tables I am pulling from are:

QuoteHRS
tblAPQP

Linked Fields= WONumber & Part Numbers

I want to write an expression to give me the total of each field for previous month, current month and next month. I've tried in my query:

IIF(Year([DueDate])* 12 + DatePart("m",[DueDate]) = Year(Date())* 12 + DatePart("m",Date())-1

However, not sure how to return the total value of each field for each time period.

Any help would be great.

Thank you
Rosie

	Sami Shamma @Reply 2 years ago
Hi Rosie. You are going to need two queries. The first query will link the required tables and produce all the records that match your criteria, with the individual three values that you want to total later. Then, you feed this query into an aggregate query in which you will get the sum for those three fields that contain your values.

	Rose Lavoie OP @Reply 2 years ago
I have a query already that totals the Manual Hours, CNC Hours and Wire Hours via a TOTAL Row at the bottom. This is called Scheduled HRS - FINAL. I need to pull each of those totals based on what still falls within a range: Previous Month, Current Month and Next Month. I can't seem to figure out the right expression to write into my query.

	Sami Shamma @Reply 2 years ago
so you want three rows with row headings : LastMonth, CurrentMonth, and nextMonth. and three columns with headings Manual Hours, Wire Hours, CNC Hours. If so, you need a crosstab query for that. Crosstab

	Rose Lavoie OP @Reply 2 years ago
I don't even know why I didn't think of using that query...I'll give it a try. Thank you

	Sami Shamma @Reply 2 years ago
It is straight forward, if you run into a problem, just let me know.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Developer Forum.

Next Unseen

New Feature: Comment Live View