Running Sum In A Form

[Guest]

I jus don't know where to start on this one.

I'm relatively new to this, i'll explain as clearly as i can.

What i'm trying to achieve is on my form i want to calculate a current
payout figure based on the current date. I can do this in an Excel
Spreadsheet but, just don't how to reproduce with just a simple field, if
it's at all possible.

My form is called : Cars

My fields are:-
FleetNo: (text field - primarykey)
StartDate:
EndDate:
PaymentsMade:
FinanceAmount:
InterestAmount:
InterestRate:
Payment:
Subtotal:
Total:

I have the following calculation which is correct based on 1 payment, but
need to repeat based how many payments have been made but using the new total
in each calculation.

[FinanceAmount ]*[InterestRate] = [InterestAmount]
$15002.00*0.0729 = $91.14

[FinanceAmount]+[InterestAmount] = [Subtotal]
$15002.00+$91.14=$15093.14

[subtotal]-[Payment] = [Total]
$15093.14-$359.08 = $14734.06

I hope this is clear enough guys, any suggestions?

Greg.

 

[Guest]

You should be able to do this using an update query. Start a new query (in
design view), add your table, then close the dialog box. Go to the Query
menu, and select update query. Drag the InterestAmount, Subtotal and Total
fields onto the query.
Then in the update to box, type in:
=[Cars]![FinanceAmount]*[Cars]![InterestRate]

Use a similar formula for the other fields

 

[Guest]

Hi,

What i would like is on the form itself i would like [Total] to display the
current total owing and not in a query if that's possible.

Maybe i'm misunderstanding your instructions.

Greg

You should be able to do this using an update query. Start a new query (in
design view), add your table, then close the dialog box. Go to the Query
menu, and select update query. Drag the InterestAmount, Subtotal and Total
fields onto the query.
Then in the update to box, type in:
=[Cars]![FinanceAmount]*[Cars]![InterestRate]

Use a similar formula for the other fields

I jus don't know where to start on this one.

I'm relatively new to this, i'll explain as clearly as i can.

What i'm trying to achieve is on my form i want to calculate a current
payout figure based on the current date. I can do this in an Excel
Spreadsheet but, just don't how to reproduce with just a simple field, if
it's at all possible.

My form is called : Cars

My fields are:-
FleetNo: (text field - primarykey)
StartDate:
EndDate:
PaymentsMade:
FinanceAmount:
InterestAmount:
InterestRate:
Payment:
Subtotal:
Total:

I have the following calculation which is correct based on 1 payment, but
need to repeat based how many payments have been made but using the new total
in each calculation.

[FinanceAmount ]*[InterestRate] = [InterestAmount]
$15002.00*0.0729 = $91.14

[FinanceAmount]+[InterestAmount] = [Subtotal]
$15002.00+$91.14=$15093.14

[subtotal]-[Payment] = [Total]
$15093.14-$359.08 = $14734.06

I hope this is clear enough guys, any suggestions?

Greg.

 

[John Vinson]

I jus don't know where to start on this one.

I'm relatively new to this, i'll explain as clearly as i can.

What i'm trying to achieve is on my form i want to calculate a current
payout figure based on the current date. I can do this in an Excel
Spreadsheet but, just don't how to reproduce with just a simple field, if
it's at all possible.

You can't, not in a "simple field" or in a Table. However you can do
so in a Form.

My form is called : Cars

My fields are:-
FleetNo: (text field - primarykey)
StartDate:
EndDate:
PaymentsMade:
FinanceAmount:
InterestAmount:
InterestRate:
Payment:
Subtotal:
Total:

Well... this is a good spreadsheet design; it's NOT a good relational
design!

If you have multiple payments for a given FleetNo, then I would expect
you might want instead a one to many relationship between the Cars
table and a CarPayment table. The terms of the loan - start date, end
date, finance amount, interest rate - would be in the Cars table; the
fields which apply to an individual payment would be in the Payments
table. The Total field should not be stored in ANY table - it should
be calculated on the fly, probably on the form footer.

To get the running sum of a field (say Payment), you would again
calculate it dynamically, not store it. One way would be to have a
calculated field in the Query

Subtotal: DSum("[Payment]", "[YourTableName]", "[FleetNo] = " &
[FleetNo] & " AND PaymentDate <= #" & [PaymentDate] & "#")

I have the following calculation which is correct based on 1 payment

And since the FleetNo is the Primary Key, you'll find that you cannot
add a second payment.

John W. Vinson[MVP]

 

[Guest]

Hi John,

Sorry, i did mean on/in a form with the result displayed in a single field.
Any thoughts on how you would attack this?

Please use the KISS method as i'm only a beginner.

Greg.

I jus don't know where to start on this one.

I'm relatively new to this, i'll explain as clearly as i can.

What i'm trying to achieve is on my form i want to calculate a current
payout figure based on the current date. I can do this in an Excel
Spreadsheet but, just don't how to reproduce with just a simple field, if
it's at all possible.

You can't, not in a "simple field" or in a Table. However you can do
so in a Form.

My form is called : Cars

My fields are:-
FleetNo: (text field - primarykey)
StartDate:
EndDate:
PaymentsMade:
FinanceAmount:
InterestAmount:
InterestRate:
Payment:
Subtotal:
Total:

Well... this is a good spreadsheet design; it's NOT a good relational
design!

If you have multiple payments for a given FleetNo, then I would expect
you might want instead a one to many relationship between the Cars
table and a CarPayment table. The terms of the loan - start date, end
date, finance amount, interest rate - would be in the Cars table; the
fields which apply to an individual payment would be in the Payments
table. The Total field should not be stored in ANY table - it should
be calculated on the fly, probably on the form footer.

To get the running sum of a field (say Payment), you would again
calculate it dynamically, not store it. One way would be to have a
calculated field in the Query

Subtotal: DSum("[Payment]", "[YourTableName]", "[FleetNo] = " &
[FleetNo] & " AND PaymentDate <= #" & [PaymentDate] & "#")

I have the following calculation which is correct based on 1 payment

And since the FleetNo is the Primary Key, you'll find that you cannot
add a second payment.

John W. Vinson[MVP]

 

[John Vinson]

Hi John,

Sorry, i did mean on/in a form with the result displayed in a single field.
Any thoughts on how you would attack this?

Please use the KISS method as i'm only a beginner.

Einstein said when criticized that his theories were too complex: "A
theory should be as simple as possible, *but no simpler*.

Your table structure is too simple for what you want to accomplish.
You need two tables.

I still don't understand what you mean by "the result" - the subtotal?
the total? what?

John W. Vinson[MVP]

 

[Guest]

Hi John,

The "result" i'm looking for is the Total to be displayed on the form, i
guess a query of some type will be required, but can't get my head around how
to do it.

Greg.