Running Sum In A Form - Help

[Guest]

I originally posted this some months ago and now trying to attack it again.

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 and start date of lease. I can do
this in an Excel Spreadsheet but, just don't how to reproduce on a form.
Currently i paste the Excel Spreadsheet into a report, however this is too
complicated for new users and the database is growing too quickly.

Each payment doesn't have it's own record.

My form is called : Cars

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

If i need more fields to achieve this please advise.

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?

GregInOz.

 

[Guest]

Not sure what you are tring to do, but does any of these help:-

1) Q. Do you need to know the number of months between start date & End

A. (DateDiff("m", StartDate, EndDate) + 1) 'Add 1 to include
both dates

if so, can you now multiply out the answer?

2) Q. If not Rather than create s sub total why not in full ie.

a. [FinanceAmount]*([InterestRate]+1) = SubTotal

need any more answes just ask.

Trev

ps I don't think this is a Runninng sum as we know them.

A running sum is a total of recordsets not calculated fields within 1
recordset

 

[Guest]

GregInOz

Also can you give a two payment answer as well to clearly identify your needs.

Not sure what way you want to calculate.

Trev B In Oz (Perth)

 

[Guest]

Hi Trever B,

Thank you in advance for any assistance.

I've been working on this database for months now and I keep coming back to
this one hurdle. I know i'm not using the correct terminology and I find it
difficult to explain exactly what i'm trying to achieve, which is totally
frustrating. Yes you are correct i'm calculation fields. Maybe i should
refer to it as a re-occurring calculation.

If you could see the excel spreadsheet i'm sure you'll get what i'm trying
to do here, nevertheless let's assume a monthly payment of $359.08 is made on
15th of each month. The lease and first payment was made 15/03/06.

This is how the calculation would look for the first month.

[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

The calculation for the second and consecutive months would be as follows.

[Total]*[InterestRate]/12 = [InterestAmount]
$14734.06*0.0729/12 = $89.50

[Total]+[InterestAmount] = [Subtotal]
$14734.06+$89.50=$14823.06

[subtotal]-[Payment] = [Total]
$14823.06-$359.08 = $14463.98

So the calculation only occurs on or after the 15th of each month, thus of
course reducing the Total.

If open the form on 14th April the Total or Payout Figure would be
$14734.06, however if i opened the form on 15th April the Total or Payout
Figure is $14463.98. Of course this assumes payments are never missed and
this assumption isn't an issue.

Sorry for the long winded response, this problem is really doing my head in.

Regards,

Greg.


PS,

The string below tells me how many payments are remaining and also takes
into consideration the 15th day of the month has already pasted. Assuming
EndDate is say for example 15/01/07 and it's a 12 month lease.

=-IIf(IsNull([EndDate]),"",DateDiff("m",[EndDate],Now())-(DateSerial(Year(Now()),Month([EndDate]),Day([EndDate]))>Now()))+2

Not sure what you are tring to do, but does any of these help:-

1) Q. Do you need to know the number of months between start date & End

A. (DateDiff("m", StartDate, EndDate) + 1) 'Add 1 to include
both dates

if so, can you now multiply out the answer?

2) Q. If not Rather than create s sub total why not in full ie.

a. [FinanceAmount]*([InterestRate]+1) = SubTotal

need any more answes just ask.

Trev

ps I don't think this is a Runninng sum as we know them.

A running sum is a total of recordsets not calculated fields within 1
recordset

I originally posted this some months ago and now trying to attack it again.

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 and start date of lease. I can do
this in an Excel Spreadsheet but, just don't how to reproduce on a form.
Currently i paste the Excel Spreadsheet into a report, however this is too
complicated for new users and the database is growing too quickly.

Each payment doesn't have it's own record.

My form is called : Cars

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

If i need more fields to achieve this please advise.

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?

GregInOz.