Sum Months with Previous Year Totals

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Supe - 27 Feb 2007 20:48 GMT

I have a table that has data from July 2005 though the end of 2006. I
created a report that sums the totals by month. I tried to create a YTD
Total in the footer that would only give the total for 2006 of the months
that 2005 had shipments in. So only would total July 2006 - December 2006.
I entered the formula =Sum(IIf([2005]>0,[2006],0)), but this doesn't work
because if an item didn't have a sale in the month of 2005, it doesn't
include that item that 2006 month. I have the monthly sales data in the
monthly footer. Is there a way to tell it to say if the 2005 monthly total
is >0 then to sum all 2006 for that month? Everything I've tried give me the
entire total for a months in 2006.

Reply to this Message

Al Campagna - 27 Feb 2007 21:45 GMT

Supe,
Try adding these calculated fields in your report query...
YearGrouping : Year(YourDate) ...and... YearMonthGrouping : Year(YourDate) &
Month(YourDate)
Now the report can be grouped on
YearGrouping Header
YearMonthGrouping Header
Detail............
YearMonthGrouping Footer (w/totals)
YearGrouping Footer (w/totals)

Signature

hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

>I have a table that has data from July 2005 though the end of 2006. I
> created a report that sums the totals by month. I tried to create a YTD
[quoted text clipped - 6 lines]
> is >0 then to sum all 2006 for that month? Everything I've tried give me the
> entire total for a months in 2006.

Reply to this Message