Calculation Help

[Guest]

Hello,

I am creating a database for Construction Loans. There are four fields I
need to calculate an amount.

Loan Amount, Unpaid Principal Balance, Current Request and Remaining
Availibility.

The user enters the Loan Amount and from that the customer want funds
upfront and then additonal funds during the payback time. So:
If the loan amount is $200,000 and they want $100,000 upfront (Unpaid
Principal Balance), then may another $20,000 later (Current Request). As the
Unpaid Principal Balance and the Current Request amounts change, I need to
have the Remaining Availibility field update automatically with the changes
so that all three fields no matter the amounts in each always total the
original Loan Amount field. I know how to do the calculation if it were one
field but not two.

Any and all help would be most appreciated and please simplify answer to
make it easier to follow.

Thank you in advance!!

 

[Steve]

It's incorrect to have a Remaining Availability field; it can be calculated
at any time in a form or report. You need the following tables:
TblLoan
LoanID
LoanDate
<<Customer contact fields>>
LoanAmount
PreConstructionLoanAmount

TblPostConstructionLoan
PostConstructionLoanID
LoanID
PostConstructionLoanDate
PostConstructionLoanAmount

The customer secures a loan on the LoanDate for the Loan Amount and requests
a preconstruction loan amount. This is recorded in TblLoan. After
construction commences, the customer makes multiple requests for post
construction loan amoounts as construction progresses. These amounts are
recorded in TblPostConstructionLoan. You can set up a form/subform to record
all the above data.

The unpaid principle balance = PreConstructionLoanAmount

The Remaining Availability is calculated in a query that includes both
tables and equals Loan Amount -
PreConstructionLoanAmount - Sum(PostConstructionLoanAmount).

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Guest]

I need it to calculate on the form. So when someone pulls up this record, the
information is there. I there some formula that is used to put in the Reming
Availibility field?

 

[Steve]

It's incorrect to have a Remaining Availability field in a table; it can be
calculated at any time in a form or report.

On your loan form, FrmLoan, add an unbound textbox labeled Remaining
Availability. Put the following expression in the Control Source property:
=[LoanAmount] - [PreConstructionLoanAmount] -
DSum("[PostConstructionLoanAmount]","TblPostConstructionLoan","[LoanID] = "
& Forms!FrmLoan!LoanID)

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Guest]

Steve,

Thank for your help but this was all I needed: = Nz(Value1) + NZ(Value2) +
NZ(Value3) + NZ(Value4). It works exactly like I needed it to in the Control
Source of the Unbound TextBox Remaining Availibility. I was not saving the
calculation to a table just needed to show it on the form and then do the
same for the reports. I apreciate you staying with me though and being the
only one who responded.

Thanks!!

It's incorrect to have a Remaining Availability field in a table; it can be
calculated at any time in a form or report.

On your loan form, FrmLoan, add an unbound textbox labeled Remaining
Availability. Put the following expression in the Control Source property:
=[LoanAmount] - [PreConstructionLoanAmount] -
DSum("[PostConstructionLoanAmount]","TblPostConstructionLoan","[LoanID] = "
& Forms!FrmLoan!LoanID)

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

I need it to calculate on the form. So when someone pulls up this record,
the
information is there. I there some formula that is used to put in the
Reming
Availibility field?