Group Report Monthly and Repeat Previous Months?

[misscrf]

OK, I started searching this issue and I don't see anything even close.

I have a report based on a query giving me totals by month like this:

MyDate Amount
01/09 100
02/09 35
03/09 50

What I want is a report that will let me group each month and report for that month and all previous months with a running sum. It would look like this:

Month 01/09

MyDate Amount CumAmount
01/09 100 100

-----
Month 02/09

MyDate Amount CumAmount
01/09 100 100
02/09 35 135

-----
Month 03/09
MyDate Amount CumAmount
01/09 100 100
02/09 35 135
03/09 50 185


When I lay that out visually it looks like it shouldn't be too hard with a running sum, but I cant figure out how to bring previous months. Any ideas?

Thanks!!!



misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

ok, I got a little further into this and I need to ammend what I have. I think this just got more complicated lol.

My source data actually looks like this:

ActiveDate RepeatDate Amount
December-08 December-08 1500
January-09 December-08 1500
January-09 January-09 2000
February-09 December-08 1500
February-09 January-09 2000
March-09 December-08 1500
March-09 January-09 2000
March-09 March-09 900
April-09 December-08 1500
April-09 January-09 2000
April-09 March-09 900
April-09 April-09 800

------

I think I have the layout working where I group on the active date and report on the repeat date with amount in the detail and have the amount a second time as a running sum over group.

Here is the next challenge. I am thinking I should do this in the query. If a month has no amount, so it is not showing, i.e. there is no repeat date or amount for february. When this happens, I need to (IIF date missing) I need to add a value of that date and amount 0. Any thoughts? Bueller?






misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

I figured this out. I found a site (lost it now, but got the info) where I needed to fill a table with all dates going well enough into future and then left join that to add the dates missing. Part of this is that I have to do that in a query just for the detail date without the grouping date in the query. I do another query with just the grouping date, and then I join the two together with criteria that the detail date is <= the grouping date.

Hope this helps someone else!!


misscrf

It is never too late to become what you could have been ~ George Eliot