Cross tab with running sum

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

TMGreen - 26 Jul 2006 04:55 GMT

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?

Reply to this Message

Michel Walsh - 26 Jul 2006 23:36 GMT

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

>I have a table post dates that span many years and an amount column. I
>need
[quoted text clipped - 3 lines]
> I
> make this work?

Reply to this Message

TMGreen - 26 Jul 2006 23:51 GMT

I don't really understand what you mean... please explain further

> Hi,
>
[quoted text clipped - 11 lines]
> > I
> > make this work?

Reply to this Message

Michel Walsh - 27 Jul 2006 00:43 GMT

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

>I don't really understand what you mean... please explain further
>
[quoted text clipped - 16 lines]
>> > I
>> > make this work?

Reply to this Message