Saving a calcuation

  • Thread starter Thread starter Emma
  • Start date Start date
E

Emma

I thought I needed the following to happen on the fly
=([AmountofLoan])-([Loans - Payment History]![TotalPayment2]). It appears
correct and shows the right number on the form. However I would like to save
this value as OutstandingLoanDue if possible. Is there a way to have the
number appear to be on the fly and save it at the same time?
 
While it's possible, it's definitely not recommended.

What happens if somehow you end up changing the value in [TotalPayment2] but
forget to change the calculated value? How will you know which one's
correct?

Just recalculate as required.
 
Emma said:
I thought I needed the following to happen on the fly
=([AmountofLoan])-([Loans - Payment History]![TotalPayment2]). It appears
correct and shows the right number on the form. However I would like to
save
this value as OutstandingLoanDue if possible. Is there a way to have the
number appear to be on the fly and save it at the same time?
Click to expand...

It is possible, but usually not desirable, the OutstandingLoanDue will
change with each payment and can be calculated correctly with each view of
the form or report. Saving the results of calculations is in violation of
Normalization rules for database design.

If you really need to do it, and I STRONGLY urge that you don't, bind the
calculated control to the field in the table and do the calculations in each
of the controls that comprise the calculation for instance:

Sub TotalPayment2_AfterUpdate()
If Len(AmountifLoan) > 0 Then
OutstandingLoanDue = ([AmountofLoan])-([Loans - Payment
History]![TotalPayment2])
End If
End Sub
 
I'll come back to whether you actually need to save the value or not, but to
do so the control on the form would have to be bound to the
OutstandingLoanDue field in the underlying table and in the AmountofLoan
control's AfterUpdate event procedure you'd assign the computed value to it
with (all as one line)

Me.OutstandingLoanDue = Me.AmountofLoan – Me.TotalPayment2

assuming that all three are fields in the form's underlying table or query.

Whether you should be doing this or computing the OutstandingLoanDue value
on the fly depends on whether it can always be computed from other values in
the database. If it can then you should not store it but compute it in a
control on a form or report or a column in a query. This is because to store
it would introduce redundancy, which is not merely wasteful and inefficient,
but more importantly leaves the door wide open to inconsistent data in that
the computed value can be changed so that it does not reflect the other
values, or vice versa.

In the above scenario computing the outstanding amount due is done by
subtracting the sum of all payments up to and including the current payment
from the total amount loaned. This sort of thing can be pretty complex as it
can involve computing the outstanding interest due on the loan, but if we
assume a simple situation were the AmountofLoan is a fixed amount which
includes the total interest then we can forget about computing the interest
on the balance due over the remaining period of the loan. Lets say you have
a table Loans with columns LoanID and AmountofLoan and a table Repayments
with columns LoanID, RepaymentDate and RepaymentAmount then the balance due
after each repayment can be computed by means of the DSum function (a
subquery could also be used but this would make the query non-updatable, so
it could not be used as the basis for a repayments data entry form). So the
query would be something like this:

SELECT Loans.LoanID, AmountofLoan, RepaymentDate,
RepaymentAmount, AmountofLoan –
DSum("RepaymentAmount","Repayments","RepaymentDate
<= #" & FORMAT(RepaymentDate,"yyyy-mm-dd") & "# And LoanID = "
& LoanID) AS OutstandingLoanDue
FROM Loans INNER JOIN Repayments
ON Loans.LoanID = Repayments.LoanID
ORDER BY LoanID, RepaymentDate;

The above does assume that there will be no more than one repayment on any
one loan on any particular date. The reason for formatting the date in the
DSum fuction BTW is that date literals in Access must either be in US short
date format or an otherwise internationally unambiguous format. I've used
the ISO standard of YYYY-MM-DD above. I've also assumed that LoanID is a
number data type, not text.

If on the other hand it cannot always be computed from other values in the
database, then you should store it in the way I've described above. The sort
of situation where it is correct to store a computed value would be the gross
price of an item in an invoice which is computed from the unit price of the
product multiplied by the quantity, plus tax for instance. As the prices of
products will change over time it is necessary to compute and store the gross
price for each invoice; otherwise invoices would always reflect the current
unit price not that at the time the invoice was raised.

Ken Sheridan
Stafford, England

Emma said:
I thought I needed the following to happen on the fly
=([AmountofLoan])-([Loans - Payment History]![TotalPayment2]). It appears
correct and shows the right number on the form. However I would like to save
this value as OutstandingLoanDue if possible. Is there a way to have the
number appear to be on the fly and save it at the same time?
Click to expand...
 
Hi Ken,

I already hav a query which the form is based on. I tried putting in a
version of your SQL in the Query but it's just coming up with a blank form.
Must say this is a little beyond my knowledge base so I'm trying this out.

Ken Sheridan said:
I'll come back to whether you actually need to save the value or not, but to
do so the control on the form would have to be bound to the
OutstandingLoanDue field in the underlying table and in the AmountofLoan
control's AfterUpdate event procedure you'd assign the computed value to it
with (all as one line)

Me.OutstandingLoanDue = Me.AmountofLoan – Me.TotalPayment2

assuming that all three are fields in the form's underlying table or query.

Whether you should be doing this or computing the OutstandingLoanDue value
on the fly depends on whether it can always be computed from other values in
the database. If it can then you should not store it but compute it in a
control on a form or report or a column in a query. This is because to store
it would introduce redundancy, which is not merely wasteful and inefficient,
but more importantly leaves the door wide open to inconsistent data in that
the computed value can be changed so that it does not reflect the other
values, or vice versa.

In the above scenario computing the outstanding amount due is done by
subtracting the sum of all payments up to and including the current payment
from the total amount loaned. This sort of thing can be pretty complex as it
can involve computing the outstanding interest due on the loan, but if we
assume a simple situation were the AmountofLoan is a fixed amount which
includes the total interest then we can forget about computing the interest
on the balance due over the remaining period of the loan. Lets say you have
a table Loans with columns LoanID and AmountofLoan and a table Repayments
with columns LoanID, RepaymentDate and RepaymentAmount then the balance due
after each repayment can be computed by means of the DSum function (a
subquery could also be used but this would make the query non-updatable, so
it could not be used as the basis for a repayments data entry form). So the
query would be something like this:

SELECT Loans.LoanID, AmountofLoan, RepaymentDate,
RepaymentAmount, AmountofLoan –
DSum("RepaymentAmount","Repayments","RepaymentDate
<= #" & FORMAT(RepaymentDate,"yyyy-mm-dd") & "# And LoanID = "
& LoanID) AS OutstandingLoanDue
FROM Loans INNER JOIN Repayments
ON Loans.LoanID = Repayments.LoanID
ORDER BY LoanID, RepaymentDate;

The above does assume that there will be no more than one repayment on any
one loan on any particular date. The reason for formatting the date in the
DSum fuction BTW is that date literals in Access must either be in US short
date format or an otherwise internationally unambiguous format. I've used
the ISO standard of YYYY-MM-DD above. I've also assumed that LoanID is a
number data type, not text.

If on the other hand it cannot always be computed from other values in the
database, then you should store it in the way I've described above. The sort
of situation where it is correct to store a computed value would be the gross
price of an item in an invoice which is computed from the unit price of the
product multiplied by the quantity, plus tax for instance. As the prices of
products will change over time it is necessary to compute and store the gross
price for each invoice; otherwise invoices would always reflect the current
unit price not that at the time the invoice was raised.

Ken Sheridan
Stafford, England

Emma said:
I thought I needed the following to happen on the fly
=([AmountofLoan])-([Loans - Payment History]![TotalPayment2]). It appears
correct and shows the right number on the form. However I would like to save
this value as OutstandingLoanDue if possible. Is there a way to have the
number appear to be on the fly and save it at the same time?
Click to expand...
Click to expand...
 
Emma:

With the table structure I assumed for my example you would get an empty
form if no repayments have yet been made. Change it to the following to
allow for loans for which no repayments have yet been made:

SELECT Repayments.LoanID, AmountofLoan, RepaymentDate,
RepaymentAmount, AmountofLoan -
DSUM("RepaymentAmount","Repayments","RepaymentDate
<= #" & FORMAT(RepaymentDate,"yyyy-mm-dd") & "# And LoanID = "
& Repayments.LoanID) AS OutstandingLoanDue
FROM Loans LEFT JOIN Repayments
ON Loans.LoanID = Repayments.LoanID
ORDER BY Repayments.LoanID, RepaymentDate;

Note that it’s the LoanID column from the Repayments table which this query
includes. This allows a new repayment record to be entered into a form based
on the query by entering the LoanID value, or more likely selecting from a
combo box bound to the loan ID field.

If you still have problems can you describe your table and field names/data
types *exactly*.

Ken Sheridan
Stafford, England

Emma said:
Hi Ken,

I already hav a query which the form is based on. I tried putting in a
version of your SQL in the Query but it's just coming up with a blank form.
Must say this is a little beyond my knowledge base so I'm trying this out.

Ken Sheridan said:
I'll come back to whether you actually need to save the value or not, but to
do so the control on the form would have to be bound to the
OutstandingLoanDue field in the underlying table and in the AmountofLoan
control's AfterUpdate event procedure you'd assign the computed value to it
with (all as one line)

Me.OutstandingLoanDue = Me.AmountofLoan – Me.TotalPayment2

assuming that all three are fields in the form's underlying table or query.

Whether you should be doing this or computing the OutstandingLoanDue value
on the fly depends on whether it can always be computed from other values in
the database. If it can then you should not store it but compute it in a
control on a form or report or a column in a query. This is because to store
it would introduce redundancy, which is not merely wasteful and inefficient,
but more importantly leaves the door wide open to inconsistent data in that
the computed value can be changed so that it does not reflect the other
values, or vice versa.

In the above scenario computing the outstanding amount due is done by
subtracting the sum of all payments up to and including the current payment
from the total amount loaned. This sort of thing can be pretty complex as it
can involve computing the outstanding interest due on the loan, but if we
assume a simple situation were the AmountofLoan is a fixed amount which
includes the total interest then we can forget about computing the interest
on the balance due over the remaining period of the loan. Lets say you have
a table Loans with columns LoanID and AmountofLoan and a table Repayments
with columns LoanID, RepaymentDate and RepaymentAmount then the balance due
after each repayment can be computed by means of the DSum function (a
subquery could also be used but this would make the query non-updatable, so
it could not be used as the basis for a repayments data entry form). So the
query would be something like this:

SELECT Loans.LoanID, AmountofLoan, RepaymentDate,
RepaymentAmount, AmountofLoan –
DSum("RepaymentAmount","Repayments","RepaymentDate
<= #" & FORMAT(RepaymentDate,"yyyy-mm-dd") & "# And LoanID = "
& LoanID) AS OutstandingLoanDue
FROM Loans INNER JOIN Repayments
ON Loans.LoanID = Repayments.LoanID
ORDER BY LoanID, RepaymentDate;

The above does assume that there will be no more than one repayment on any
one loan on any particular date. The reason for formatting the date in the
DSum fuction BTW is that date literals in Access must either be in US short
date format or an otherwise internationally unambiguous format. I've used
the ISO standard of YYYY-MM-DD above. I've also assumed that LoanID is a
number data type, not text.

If on the other hand it cannot always be computed from other values in the
database, then you should store it in the way I've described above. The sort
of situation where it is correct to store a computed value would be the gross
price of an item in an invoice which is computed from the unit price of the
product multiplied by the quantity, plus tax for instance. As the prices of
products will change over time it is necessary to compute and store the gross
price for each invoice; otherwise invoices would always reflect the current
unit price not that at the time the invoice was raised.

Ken Sheridan
Stafford, England

Emma said:
I thought I needed the following to happen on the fly
=([AmountofLoan])-([Loans - Payment History]![TotalPayment2]). It appears
correct and shows the right number on the form. However I would like to save
this value as OutstandingLoanDue if possible. Is there a way to have the
number appear to be on the fly and save it at the same time?
Click to expand...
Click to expand...

Hi Ken,

I already hav a query which the form is based on. I tried putting in a
version of your SQL in the Query but it's just coming up with a blank form.
Must say this is a little beyond my knowledge base so I'm trying this out.

Ken Sheridan said:
I'll come back to whether you actually need to save the value or not, but to
do so the control on the form would have to be bound to the
OutstandingLoanDue field in the underlying table and in the AmountofLoan
control's AfterUpdate event procedure you'd assign the computed value to it
with (all as one line)

Me.OutstandingLoanDue = Me.AmountofLoan – Me.TotalPayment2

assuming that all three are fields in the form's underlying table or query.

Whether you should be doing this or computing the OutstandingLoanDue value
on the fly depends on whether it can always be computed from other values in
the database. If it can then you should not store it but compute it in a
control on a form or report or a column in a query. This is because to store
it would introduce redundancy, which is not merely wasteful and inefficient,
but more importantly leaves the door wide open to inconsistent data in that
the computed value can be changed so that it does not reflect the other
values, or vice versa.

In the above scenario computing the outstanding amount due is done by
subtracting the sum of all payments up to and including the current payment
from the total amount loaned. This sort of thing can be pretty complex as it
can involve computing the outstanding interest due on the loan, but if we
assume a simple situation were the AmountofLoan is a fixed amount which
includes the total interest then we can forget about computing the interest
on the balance due over the remaining period of the loan. Lets say you have
a table Loans with columns LoanID and AmountofLoan and a table Repayments
with columns LoanID, RepaymentDate and RepaymentAmount then the balance due
after each repayment can be computed by means of the DSum function (a
subquery could also be used but this would make the query non-updatable, so
it could not be used as the basis for a repayments data entry form). So the
query would be something like this:

SELECT Loans.LoanID, AmountofLoan, RepaymentDate,
RepaymentAmount, AmountofLoan –
DSum("RepaymentAmount","Repayments","RepaymentDate
<= #" & FORMAT(RepaymentDate,"yyyy-mm-dd") & "# And LoanID = "
& LoanID) AS OutstandingLoanDue
FROM Loans INNER JOIN Repayments
ON Loans.LoanID = Repayments.LoanID
ORDER BY LoanID, RepaymentDate;

The above does assume that there will be no more than one repayment on any
one loan on any particular date. The reason for formatting the date in the
DSum fuction BTW is that date literals in Access must either be in US short
date format or an otherwise internationally unambiguous format. I've used
the ISO standard of YYYY-MM-DD above. I've also assumed that LoanID is a
number data type, not text.

If on the other hand it cannot always be computed from other values in the
database, then you should store it in the way I've described above. The sort
of situation where it is correct to store a computed value would be the gross
price of an item in an invoice which is computed from the unit price of the
product multiplied by the quantity, plus tax for instance. As the prices of
products will change over time it is necessary to compute and store the gross
price for each invoice; otherwise invoices would always reflect the current
unit price not that at the time the invoice was raised.

Ken Sheridan
Stafford, England

Emma said:
I thought I needed the following to happen on the fly
=([AmountofLoan])-([Loans - Payment History]![TotalPayment2]). It appears
correct and shows the right number on the form. However I would like to save
this value as OutstandingLoanDue if possible. Is there a way to have the
number appear to be on the fly and save it at the same time?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top