How do you loop this with a for loop?

[vtsao]

How do you loop this with a for loop? The goal is to create a report with payments, charges, credits, and adjustments . Carryover and Total due are obtained through manipulation of the data in the columns.

Problem is: Carryover = Total Due = [Carryover] – [Payment] + [charges] – [credits] + or – [adjustments] for the current month. That is easy on the surface.

“Carryover” = “Total Due” from the previous month, i.e., Month 2’s “Carryover” of 100 is Month 1’s carryover, and Month 3’s “Carryover” of 150 is last month’s “Total Due.”

Neither “Carryover” nor “Total Due” are records from the database, but pulling data from the previous query. At the same time, “Total Due” includes the “Carryover” so that if you don’t this carefully, the queries are circular.



Victor


MO CARRYOVER PAY CHARGES CREDITS ADJUST TOTAL DUE
1 0 0 100 0 0 100
2 100 50 100 0 0 150
3 150 50 100 0 0 200
4 200 50 100 50 0 200

create table payments(
month varchar2(2),
carryover number(10),
payments number(10),
charges number(10),
credits number(10),
adjustment number(10),
total number(10)
)

set X=2

<loop X till x = 13a>

select Month from payments

<if Month = 12>
then X= X-1

insert into payments
(month, Carryover)
select X, total from payments where month = X-1

<else>

insert into payments
(month, Carryover)
select X, total from payments where month = X-1

<endif>

set X = X+1

<Loop>

 

[swaybright]

I'm a little confused, but I'll give it a shot.
Assuming that you have some Table/Query that has Fields for Month, Payment, Charges, Credit, Adjust, Total, and Carryover...

Dim sng_carryover, sng_payment, sng_charges as single 
Dim sng_credits, sng_adjust, sng_total as single
Dim ctr as integer
Dim str_SQL as string 

sng_carryover=0
For ctr = 1 to DMax("Month","TableName")
sng_payment = DLookup("Payment", "TableName", "Month = " & ctr)
sng_charges = DLookup("Charges", "TableName", "Month = " & ctr)
sng_credits= DLookup("Credits", "TableName", "Month = " & ctr)
sng_adjust= DLookup("adjust", "TableName", "Month = " & ctr)

sng_Total = sng_carryover – sng_Payment + sng_charges – sng_credits + sng_adjust

str_SQL = "UPDATE TableName SET Total = " & sng_Total & ", Carryover = " & sng_carryover
DoCmd.RunSql str_SQL

sng_carryover = sng_total
Next
[code]
Of course I am assuming that months are numbered consecutively from 1 to infinity (no year information)

Hope that gives you something to work from.

Shane