Help with specific expression

[Guest]

I need to create a query that shows 2 amounts. The first amount should be
the sum of 'it_amount' where 'it_date' is within the last 12 months from
today's date. The second amount should also be the sum of 'it_amount' but
where 'it_date' is between 12 and 24 months prior to today's date. Therefore
at any given time, I can run the query and identify/compare the amount of
business transacted by any one company during a) the past 12 months and b)
the 12 months prior to that.
Any ideas as to how I can write this as an expression in Access?

 

[Guest]

Hi Sarah

Try something like...

SELECT sum(iif(it_date > dateadd("m", -12, date()), it_amount, 0)) AS [Last
12 Months],
sum(iif(it_date <= dateadd("m", -12, date()), it_amount, 0)) AS [Prior 12
Months]
FROM tblTableName
WHERE it_date > dateadd("m", -24, date())

hth

Andy Hull

 

[John Spencer]

Try something like the following

SELECT CompanyID
, Sum(IIF(It_Date Between Date() and
DateAdd("yyyy",-1,Date()),IT_Amount,Null)) as CurrentYear
,Sum(IIF(It_Date <DateAdd("yyyy",-1,Date()) and >
DateAdd("yyyy",-2,Date()),IT_Amount,Null)) as PriorYear
FROM YourTable
GROUP BY CompanyID

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..