YTD, Prior YTD, and % difference in report

[Brian Goldhammer via AccessMonster.com]

Hello All,

I am trying to generate a report that lists the current YTD totals, prior YTD
totals and the % difference between the two. The fields I'm working with are
as follows:

Manufacturer
Total Order Amount
Week Ending Date

I have looked at previous posts and have been able to get one report for
either the current YTD or the prior but not both at the same time.

I apologize in advance if this is confusing.......(welcome to my world)

Thanks,

 

[Rob Oldfield]

You would need to do something like create a grouped query - called
YearTotals for example, grouping by year([week ending date]) which will give
you totals by year. You'd then need to write a function something like
(aircode):

function PercentDiff(yr as integer)
dim thisyear, lastyear
thisyear=dlookup("[total]","yeartotals","year="&cstr(yr))
lastyear=dlookup("[total]","yeartotals","year="&cstr(yr-1))
if isnull(lastyear) then
percentdiff=default value you require when no 'last year'
else
percentdiff=thisyear/lastyear
endif
end function

You can then add another query based on yeartotals with a calculated field
percentdiff([year]) and base your report on that.

 

[BarryC]

Why don't you try a Union query? Basically, you can write a query for
each figure you want, then view the SQL. Copy and paste the SQL from
one query to the other, inserting the "UNION" keyword between them.
Like this:

Select Manufacturer,Total Order Amount, Week Ending Date from Table
UNION
Select Manufacturer,Total Order Amount, Week Ending Date2 from Table