YTD, Prior YTD, and % difference in report

  • Thread starter Brian Goldhammer via
  • Start date

Brian Goldhammer via

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:

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)


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

function PercentDiff(yr as integer)
dim thisyear, lastyear
if isnull(lastyear) then
percentdiff=default value you require when no 'last year'
end function

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


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
Select Manufacturer,Total Order Amount, Week Ending Date2 from Table

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question