asked on

Query one and two years from today's date

I have a query that looks like this
SELECT
Query12.IM_Key AS PartNumber, Query12.IM_DESCR, Query12.ML_LOCATION, Sum(IIf(Year([TX_Date])=2015,1,0)) AS [# of Shipments 2015],
Sum(IIf(Year([TX_Date])=2015,[TX_Qty],0)) AS [Total Quantity 2015], Sum(IIf(Year([TX_Date])=2016,1,0)) AS [# of Shipments 2016],
Sum(IIf(Year([TX_Date])=2016,[TX_Qty],0)) AS [Total Quantity 2016], Sum(IIF(Year([TX_Date])=2017,1,0)) AS [# of Shipments 2017],
Sum(IIf(Year([TX_Date])=2017,[TX_Qty],0)) AS [Total Quantity 2017]
FROM Query12
GROUP BY Query12.IM_Key, Query12.IM_DESCR, Query12.ML_LOCATION;

Basically instead of having separate columns for each year and having to edit the query every year I would like to have columns for 1 year prior to todays date and two years prior to todays date. Basically I want to see what we shipped and the qty for 1 year ago from today and what we shipped and qty two years ago from today. Any help would be appreciated!

SOLUTION

crystal (strive4peace) - Microsoft MVP, Access

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

HOTWATT

ASKER

When I do Sum( IIf( Year([TX_Date])=Year(Date())-1, nz([TX_Qty],0) ,0) ) AS [# Shipments Yr-1] does that give me a year from todays date? I don't seem to be getting anything from 2017 in that column just stuff from 2016. I need 1 year ago from todays date and then two years ago from todays date.

crystal (strive4peace) - Microsoft MVP, Access

> "I don't seem to be getting anything from 2017 in that column just stuff from 2016"

if you want THIS year, then don't subtract 1 ... instead of Year(Date())-1, use Year(Date())

HOTWATT

ASKER

I was looking for more like 1 year from todays date so I wanted to include stuff between 04/03/16 and 04/03/17. Is that possible?

crystal (strive4peace) - Microsoft MVP, Access

( Date() - [TX_Date] ) <= 365

Gustav Brock

My code works, of course. It's even tested.

Year(Date())-Year([TX_Date])) Between 0 And 2

will return any TX_Date of the years 2015, 2016, and 2017.

I was looking for more like 1 year from todays date

Well, you must make up your mind. That's very different from your original question.

/gustav

HOTWATT

ASKER

I apologize for the confusion. My original post was I would like to have columns for 1 year prior to todays date and two years prior to todays date. I am now realizing that it will be easier just to have three separate columns for each year which you both have given me options for. Thanks for all the help!

Gustav Brock

You are welcome!

/gustav

crystal (strive4peace) - Microsoft MVP, Access

you're welcome ~ happy to help