Cross tab with running sum

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table post dates that span many years and an amount column. I need
a cross tab that handles the amount in a running sum format. For example,
the first payment was 1/1/06 and the amount was 100, on 2/1/06 the amount
posted is 100, but I want the amount for that column to show 200. How do I
make this work?
 
Hi,


Make the crosstab first, TEHN , used it as if it was a table and make a
running sum "over" it, "as if" it was a standard table... ?


Hoping it may help,
Vanderghast, Access MVP
 
HI,



If you can make a running sum over a table named t1, then just change the
name, t1, by the crosstab query name.


To make a running sum over fields f1, and f2, over the increasing value of
field f3 (generally a date_time_stamp field, so the running sum will be the
"historical" running sum), you can do:


SELECT a.f3, SUM(b.f1), SUM(b.f2)
FROM myTable As a INNER JOIN myTable As b
ON a.f3 >= b.f3
GROUP BY a.f3



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top