Get a Running Sum in a Query

[magicdds]

I have a Query based on another Query, based on another Query that should
produce the following results:

PtID RPID Charge Payment LineBal RunSum
20 143 $100.00 $0.00 $100.00 100
20 144 $95.00 $0.00 $95.00 195
23 61 $0.00 $40.00 ($40.00) (40)
23 61 $0.00 $50.00 ($50.00) (90)
23 61 $0.00 $35.00 ($35.00) (125)
23 61 $55.00 $0.00 $55.00 (70)
23 61 $90.00 $0.00 $90.00 20
23 62 $0.00 ($20.00) $20.00 20
23 62 $5.00 $0.00 $5.00 25
23 62 $10.00 $0.00 $10.00 35

PTID and RPID are ID#s and LineBal is calculated as
LineBal = Charge - Payment.

But I can't get the column RunSum to work as a running sum for each PTID and
RPID. I tried

RunSum: DSum("[LineBal]","StatementQuery2","PTID=" & [PTID] & " And RPID=" &
[RPID])

But that gave me the same result for rows 1 & 2,
the same result for rows 3,4,5,6,7,
an the same result for rows 8,9,10.

Is there a way to get the RunSum column to work properly as shown above?

Thanks for any help.
Mark

 

[scubadiver]

insert '<=' into the statement after PTID so it is...

RunSum: DSum("[LineBal]","StatementQuery2","PTID<=" & [PTID] & " And RPID="
& [RPID])

 

[magicdds]

I tried changing the DSum statement but got the same result. Here is the
result:

RunningSum
100
95
20
20
20
20
20
695
695
695

Does anyone have any other suggestions?

Thanks
Mark

insert '<=' into the statement after PTID so it is...

RunSum: DSum("[LineBal]","StatementQuery2","PTID<=" & [PTID] & " And RPID="
& [RPID])

I have a Query based on another Query, based on another Query that should
produce the following results:

PtID RPID Charge Payment LineBal RunSum
20 143 $100.00 $0.00 $100.00 100
20 144 $95.00 $0.00 $95.00 195
23 61 $0.00 $40.00 ($40.00) (40)
23 61 $0.00 $50.00 ($50.00) (90)
23 61 $0.00 $35.00 ($35.00) (125)
23 61 $55.00 $0.00 $55.00 (70)
23 61 $90.00 $0.00 $90.00 20
23 62 $0.00 ($20.00) $20.00 20
23 62 $5.00 $0.00 $5.00 25
23 62 $10.00 $0.00 $10.00 35

PTID and RPID are ID#s and LineBal is calculated as
LineBal = Charge - Payment.

But I can't get the column RunSum to work as a running sum for each PTID and
RPID. I tried

RunSum: DSum("[LineBal]","StatementQuery2","PTID=" & [PTID] & " And RPID=" &
[RPID])

But that gave me the same result for rows 1 & 2,
the same result for rows 3,4,5,6,7,
an the same result for rows 8,9,10.

Is there a way to get the RunSum column to work properly as shown above?

Thanks for any help.
Mark

 

[Michel Walsh]

You need a field that defines the ordering of the running. The RECORD ORDER
is NOT a valid candidate, since records may MOVE AROUND as soon as you
append, modify, or delete some other records. Your DSUM expression would be
good if PTID was such a field, but it is not. (it has duplicated values).


Vanderghast, Access MVP

I tried changing the DSum statement but got the same result. Here is the
result:

RunningSum
100
95
20
20
20
20
20
695
695
695

Does anyone have any other suggestions?

Thanks
Mark

insert '<=' into the statement after PTID so it is...

RunSum: DSum("[LineBal]","StatementQuery2","PTID<=" & [PTID] & " And
RPID="
& [RPID])

I have a Query based on another Query, based on another Query that
should
produce the following results:

PtID RPID Charge Payment LineBal RunSum
20 143 $100.00 $0.00 $100.00 100
20 144 $95.00 $0.00 $95.00 195
23 61 $0.00 $40.00 ($40.00) (40)
23 61 $0.00 $50.00 ($50.00) (90)
23 61 $0.00 $35.00 ($35.00) (125)
23 61 $55.00 $0.00 $55.00 (70)
23 61 $90.00 $0.00 $90.00 20
23 62 $0.00 ($20.00) $20.00 20
23 62 $5.00 $0.00 $5.00 25
23 62 $10.00 $0.00 $10.00 35

PTID and RPID are ID#s and LineBal is calculated as
LineBal = Charge - Payment.

But I can't get the column RunSum to work as a running sum for each
PTID and
RPID. I tried

RunSum: DSum("[LineBal]","StatementQuery2","PTID=" & [PTID] & " And
RPID=" &
[RPID])

But that gave me the same result for rows 1 & 2,
the same result for rows 3,4,5,6,7,
an the same result for rows 8,9,10.

Is there a way to get the RunSum column to work properly as shown
above?

Thanks for any help.
Mark