Creating auto loan database

[gensicki]

Very rusty beginning access user wants to create db to track, record,
and calculate car payments made by customers at used car lot.

Currently using excel spreadsheet for each loan.

Need help with structure of db. I think I need the following tables:

Customer information (Name, address, original loan amount, etc..)
Transactions (Date pmt made, amt of payment)

Would like to create form to record payments, with a subform, that
will show previous payments made and showing current outstanding
balance.

I suspect I have to create a one to many relationship between
customers and transactions. But don't know if a query is used to
compile the subform data or which function to use to calculate
outstanding balance.

Any help or suggestions to which web template or information I can use
to do this would be appreciated.

I don't have or want the money required to buy proprietary loan
database, and want the challenge of creating my own database.

Thank you

Greg
(e-mail address removed)
www.onthegomotors.net

 

[Ken Snell \(MVP\)]

Customers table:
CustomerID
CustomerName
CustomerAddress
CustomerPhone
(etc.)

LoanTypes table
LoanTypeID
LoanTypeName
(etc.)

CustomerLoans table:
CustomerLoanID
CustomerID (foreign key to Customers table)
LoanTypeID (foreign key to LoanTypes table)
LoanCreateDate
LoanAmount
LoanMonthsToPay
LoanInterestRate
LoanPaymentDayInMonth
(etc.)

CustomerLoanPayments table:
CustomerLoanPaymentID
CustomerLoanID (foreign key to CustomerLoans table)
DatePaid
PrincipalPaid
InterestPaid

and so on.

 

[gensicki]

Ken,

Thank you for your quick response.

I have created the aforementioned tables and have a couple follow up
questions.

Do I create a query and related form to record each customer's
transaction? If so, how do I make it auto-calculate the interest
charged and principle applied from each payment. (I can do this
formula in Excel, but don't know how to apply it to Access).

How do I create a current balance value. Does this value become part
of the CustomerLoans table?

I assume a subform showing all transactions can be created (ala a
statement) and placed in a customer's form screen?

 

[Ken Snell \(MVP\)]

Comments inline...

--

Ken Snell
<MS ACCESS MVP>

Ken,

Thank you for your quick response.

I have created the aforementioned tables and have a couple follow up
questions.

Do I create a query and related form to record each customer's
transaction?

That would be my approach. I would consider a form and a subform
combination. The main form would allow you to select the specific customer
and loan; the subform would allow entry of payment information for that
customer's loan. (A more sophisticated setup would involve a main form and
two subforms -- the main form would allow selection of the customer, the
first subform would show all the loans for that customer, and the second
subform would show the payments for the specific loan selected in the first
subform. This setup requires you to "link" the two subforms to each other
via an invisible textbox on the main form that holds the CustomerLoanID
value from the first subform, and then the invisible textbox is used in the
LinkMasterFields property for the second subform.)

If so, how do I make it auto-calculate the interest
charged and principle applied from each payment. (I can do this
formula in Excel, but don't know how to apply it to Access).

With the setup that I provided initially, the table is storing just the
principal and interest amounts, using the assumption that you can always
calculate the total amount paid by adding the two fields' data together. If
you want to enter a single amount for the total, then I still would
encourage the storing of the two individual amounts instead of storing the
total amount paid; or you could add a third field to store the total amount
if you want. But you'd need to have the form run programming to calculate
the interest and principal from the total amount, based on whatever
calculation expression you're using. This could be done using the
AfterUpdate event of the textbox into which you enter the total amount paid,
or you could use the BeforeUpdate event of the form (that is the subform).

How do I create a current balance value. Does this value become part
of the CustomerLoans table?

No, you should not have a "balance due" field in any table. Instead, create
a query that would calculate this for you (based on the difference between
the Original Amount Due and the sum of the Principal amounts), and then use
that query to provide you with the balance amount when you want it.

 

[gensicki]

I have succesfully created:

Customer form with Loan Payment subform.
Query to total principal paid

However, my lack of programming/expression building experience is
preventing me from utilizing the "afterupdate" control (i.e. I know
what I want to do, but don't know how to tell access to do it.). Is
it possible for more information regarding this area?

Additionally, where is the "=loanamount-Sum(principalpaid) placed in
the query?

Please bear with me. I haven't used access in a long time.

Thank you

 

[Ken Snell \(MVP\)]

There is no "afterupdate" control. My reference is to the AfterUpdate event
for the control into which you're entering the total amount. The AfterUpdate
event for that control will occur right after you've entered a changed/new
value into the control, and you can use it to run programming. Note that, if
you do not have a total amount field in the table so that you can bind this
control to that field in the subform, you may see some "weird" visuals with
that control in the records that are not the "current" one in the subform --
namely, that whatever you type into that control in one record will show up
in the other records' copy of that control - however, any programming that
runs in the AfterUpdate event of the control will "work" only on the value
for the current record.

You don't use an expression such as this in a query:
"=loanamount-Sum(principalpaid)"

Instead, if you're in the design grid view of the query, you create a new
field (called a calculated field) on the grid, with this in the "Field" box:
NameOfNewField: [loanamount]-Sum([principalpaid])

--

Ken Snell
<MS ACCESS MVP>

 

[gensicki]

There is no "afterupdate" control. My reference is to the AfterUpdate event
for the control into which you're entering the total amount. The AfterUpdate
event for that control will occur right after you've entered a changed/new
value into the control, and you can use it to run programming. Note that, if
you do not have a total amount field in the table so that you can bind this
control to that field in the subform, you may see some "weird" visuals with
that control in the records that are not the "current" one in the subform --
namely, that whatever you type into that control in one record will show up
in the other records' copy of that control - however, any programming that
runs in the AfterUpdate event of the control will "work" only on the value
for the current record.

You don't use an expression such as this in a query:
"=loanamount-Sum(principalpaid)"

Instead, if you're in the design grid view of the query, you create a new
field (called a calculated field) on the grid, with this in the "Field" box:
NameOfNewField: [loanamount]-Sum([principalpaid])

Almost there!

I've been able to create Outstanding balance query.

Concerning the interestcharge/principal paid, I know what I want the
formula to calculate, but using Expression Builder to create the
programming is beyond my ability. I don't know how to insert the
formula using Expression Builder....

 

[Ken Snell \(MVP\)]

There is no "afterupdate" control. My reference is to the AfterUpdate
event
for the control into which you're entering the total amount. The
AfterUpdate
event for that control will occur right after you've entered a
changed/new
value into the control, and you can use it to run programming. Note that,
if
you do not have a total amount field in the table so that you can bind
this
control to that field in the subform, you may see some "weird" visuals
with
that control in the records that are not the "current" one in the
subform --
namely, that whatever you type into that control in one record will show
up
in the other records' copy of that control - however, any programming
that
runs in the AfterUpdate event of the control will "work" only on the
value
for the current record.

You don't use an expression such as this in a query:
"=loanamount-Sum(principalpaid)"

Instead, if you're in the design grid view of the query, you create a new
field (called a calculated field) on the grid, with this in the "Field"
box:
NameOfNewField: [loanamount]-Sum([principalpaid])

Almost there!

I've been able to create Outstanding balance query.

Concerning the interestcharge/principal paid, I know what I want the
formula to calculate, but using Expression Builder to create the
programming is beyond my ability. I don't know how to insert the
formula using Expression Builder....

I don't know what algorithm you want to use, so it's impossible to make a
suggestion unless you can tell us... < smile >.

 

[gensicki]

There is no "afterupdate" control. My reference is to the AfterUpdate
event
for the control into which you're entering the total amount. The
AfterUpdate
event for that control will occur right after you've entered a
changed/new
value into the control, and you can use it to run programming. Note that,
if
you do not have a total amount field in the table so that you can bind
this
control to that field in the subform, you may see some "weird" visuals
with
that control in the records that are not the "current" one in the
subform --
namely, that whatever you type into that control in one record will show
up
in the other records' copy of that control - however, any programming
that
runs in the AfterUpdate event of the control will "work" only on the
value
for the current record.
You don't use an expression such as this in a query:
"=loanamount-Sum(principalpaid)"
Instead, if you're in the design grid view of the query, you create a new
field (called a calculated field) on the grid, with this in the "Field"
box:
NameOfNewField: [loanamount]-Sum([principalpaid])

Almost there!

I've been able to create Outstanding balance query.

Concerning the interestcharge/principal paid, I know what I want the
formula to calculate, but using Expression Builder to create the
programming is beyond my ability. I don't know how to insert the
formula using Expression Builder....

I don't know what algorithm you want to use, so it's impossible to make a
suggestion unless you can tell us... < smile >.

--

Ken Snell
<MS ACCESS MVP>- Hide quoted text -

- Show quoted text -

The current interest charged algorithm = balance*InterestRate/12.
As additional interest is not charged when payments are paid late, it
isn't necessary to calculate more than a month's interest, therefore
the algorithm ignores number of days between payments.

 

[Ken Snell \(MVP\)]

The current interest charged algorithm = balance*InterestRate/12.
As additional interest is not charged when payments are paid late, it
isn't necessary to calculate more than a month's interest, therefore
the algorithm ignores number of days between payments.

In order to assist you with the placement and use of the algorithm, are you
wanting to use it to calculate the principal and interest amounts after you
enter a "total amount" payment into the subform? Or are you wanting to use
it to calculate these values in a query for a report?

 

[gensicki]

In order to assist you with the placement and use of the algorithm, are you
wanting to use it to calculate the principal and interest amounts after you
enter a "total amount" payment into the subform? Or are you wanting to use
it to calculate these values in a query for a report?

--

Ken Snell
<MS ACCESS MVP>- Hide quoted text -

- Show quoted text -

Either way would work. Quite often, when accepting a a payment from a
customer, they ask what their current balance is.

 

[Ken Snell \(MVP\)]

To do the calculation, you'll need to use the Outstanding balance query to
get the current balance. I am assuming that this query will return a balance
amount even if no payments have been made by the customer yet? Or does it
depend upon the presence of at least one payment by the customer?

For the form calculation, you would use the AfterUpdate event of the textbox
into which you enter the total payment in order to run the VBA code that
will calculate the correct interest and principal distribution (based on
current outstanding balance prior to the application of this payment) and to
write those values into the correct textboxes that are bound to the
principal and interest fields.

To suggest the code to be used, I need to know the SQL statement of your
Outstanding Balance query; and, if it's using one or more queries in it, the
SQL statement of those queries. It's possible to write the code without
using your outstanding balance query, but it's good to use that query when
you're doing the same calculation in more than one place so that you avoid
the possibility of having different "calculation" methods for the same value
in the database.
--

Ken Snell
<MS ACCESS MVP>

 

[gensicki]

To do the calculation, you'll need to use the Outstanding balance query to
get the current balance. I am assuming that this query will return a balance
amount even if no payments have been made by the customer yet? Or does it
depend upon the presence of at least one payment by the customer?

For the form calculation, you would use the AfterUpdate event of the textbox
into which you enter the total payment in order to run the VBA code that
will calculate the correct interest and principal distribution (based on
current outstanding balance prior to the application of this payment) and to
write those values into the correct textboxes that are bound to the
principal and interest fields.

To suggest the code to be used, I need to know the SQL statement of your
Outstanding Balance query; and, if it's using one or more queries in it, the
SQL statement of those queries. It's possible to write the code without
using your outstanding balance query, but it's good to use that query when
you're doing the same calculation in more than one place so that you avoid
the possibility of having different "calculation" methods for the same value
in the database.
--

Ken Snell
<MS ACCESS MVP>







- Show quoted text -

This is the formulas from my balance query: balance: [CustomerLoans]!
[loanamt]-[Principal paid]![Sum Of PrincipalPaid]

 

[Ken Snell \(MVP\)]

I need to see the entire SQL statement. Open your query in Design view,
click on "query view" icon at far left of toolbar, select "SQL View", and
copy all the text that you see in that next window. Paste that text into
your post here. Do for all queries involved in the "outstanding balance"
query.

--

Ken Snell
<MS ACCESS MVP>

To do the calculation, you'll need to use the Outstanding balance query
to
get the current balance. I am assuming that this query will return a
balance
amount even if no payments have been made by the customer yet? Or does it
depend upon the presence of at least one payment by the customer?

For the form calculation, you would use the AfterUpdate event of the
textbox
into which you enter the total payment in order to run the VBA code that
will calculate the correct interest and principal distribution (based on
current outstanding balance prior to the application of this payment) and
to
write those values into the correct textboxes that are bound to the
principal and interest fields.

To suggest the code to be used, I need to know the SQL statement of your
Outstanding Balance query; and, if it's using one or more queries in it,
the
SQL statement of those queries. It's possible to write the code without
using your outstanding balance query, but it's good to use that query
when
you're doing the same calculation in more than one place so that you
avoid
the possibility of having different "calculation" methods for the same
value
in the database.
--

Ken Snell
<MS ACCESS MVP>













- Show quoted text -

This is the formulas from my balance query: balance: [CustomerLoans]!
[loanamt]-[Principal paid]![Sum Of PrincipalPaid]

 

[gensicki]

I need to see the entire SQL statement. Open your query in Design view,
click on "query view" icon at far left of toolbar, select "SQL View", and
copy all the text that you see in that next window. Paste that text into
your post here. Do for all queries involved in the "outstanding balance"
query.

--

Ken Snell
<MS ACCESS MVP>

This is the formulas from my balance query: balance: [CustomerLoans]!
[loanamt]-[Principal paid]![Sum Of PrincipalPaid]- Hide quoted text -

- Show quoted text -

Balance query SQL:
SELECT Customers.Namelast, [CustomerLoans]![loanamt]-[Principal paid]!
[Sum Of PrincipalPaid] AS balance
FROM Customers INNER JOIN ([Principal paid] INNER JOIN CustomerLoans
ON [Principal paid].CustomerLoanID = CustomerLoans.CustomerLoanID) ON
Customers.CustomerID = CustomerLoans.CustomerID;

Principal paid query SQL:
SELECT DISTINCTROW CustomerLoanPayments.CustomerLoanID,
Sum(CustomerLoanPayments.PrincipalPaid) AS [Sum Of PrincipalPaid]
FROM CustomerLoans INNER JOIN CustomerLoanPayments ON
CustomerLoans.CustomerLoanID = CustomerLoanPayments.CustomerLoanID
GROUP BY CustomerLoanPayments.CustomerLoanID;

 

[Ken Snell \(MVP\)]

I need to see the entire SQL statement. Open your query in Design view,
click on "query view" icon at far left of toolbar, select "SQL View", and
copy all the text that you see in that next window. Paste that text into
your post here. Do for all queries involved in the "outstanding balance"
query.

Balance query SQL:
SELECT Customers.Namelast, [CustomerLoans]![loanamt]-[Principal paid]!
[Sum Of PrincipalPaid] AS balance
FROM Customers INNER JOIN ([Principal paid] INNER JOIN CustomerLoans
ON [Principal paid].CustomerLoanID = CustomerLoans.CustomerLoanID) ON
Customers.CustomerID = CustomerLoans.CustomerID;

Principal paid query SQL:
SELECT DISTINCTROW CustomerLoanPayments.CustomerLoanID,
Sum(CustomerLoanPayments.PrincipalPaid) AS [Sum Of PrincipalPaid]
FROM CustomerLoans INNER JOIN CustomerLoanPayments ON
CustomerLoans.CustomerLoanID = CustomerLoanPayments.CustomerLoanID
GROUP BY CustomerLoanPayments.CustomerLoanID;

OK, thanks. We won't be able to use your existing queries because they may
include a current payment in the calculation of balance and interest. So,
create a new query (save it with the name "qry_LoanPaymentInfo") using the
following SQL statement (I'm using the initial table structure that I'd
posted back at the beginning of this thread to build this query, so you'll
need to change names if you used different field and table names) -- note
that this query gives us a complete set of payments for all customer loans,
which we'll then use in the form's programming to get the information we
need:

SELECT Customers.CustomerID, CustomerLoans.CustomerLoanID,
CustomerLoans.LoanAmount, CustomerLoans.LoanInterestRate,
CustomerLoanPayments.CustomerLoanPaymentID,
CustomerLoanPayments.DatePaid,
CustomerLoanPayments.PrincipalPaid,
CustomerLoanPayments.InterestPaid
FROM (Customers LEFT JOIN CustomerLoans
ON Customers.CustomerID = CustomerLoans.CustomerID)
LEFT JOIN CustomerLoanPayments
ON CustomerLoans.CustomerLoanID =
CustomerLoanPayments.CustomerLoanID;


Now, we'll use the AfterUpdate event of the textbox into which you type the
total payment amount to run programming that will calculate the interest and
principal amounts for that total payment (note that I'm using generic names
for the objects for which I don't know your exact names) -- this is untested
code:

' *** Start of code
Private Sub NameOfTotalPaymentAmountTextbox_AfterUpdate()
Dim curPaymentAmount As Currency, curInterestAmount As Currency
Dim curPrincipalAmount As Currency, curPriorBalance As Currency
Dim curOriginalBalance As Currency, curPrincipalPaid As Currency
Dim datPaymentDate As Date
Dim dblInterestRate As Double

Const strQueryName As String = "qry_LoanPaymentInfo"

If Len(Me.NameOfTotalPaymentAmountTextbox.Value & "") > 0 Then
curPaymentAmount = Me.NameOfTotalPaymentAmountTextbox.Value
' Use today's date as the payment date if no payment date has been entered
' on the form
datPaymentDate = Nz(Me.NameOfPaymentDateTextbox.Value, Date())
' Get original loan amount
curOriginalBalance = DLookup("LoanAmount", "CustomerLoans", _
"CustomerLoanID=" & Me.CustomerLoanID.Value)
' Get total principal paid prior to this new payment
curPrincipalPaid = CCur(Nz(DSum("PrincipalPaid", _
strQueryName, "CustomerLoanID=" & Me.CustomerLoanID.Value & _
" And DatePaid<" & Format(datPaymentDate, "\#mm\/dd\/yyyy\#")),0))
' Get balance prior to this new payment
curPriorBalance = curOriginalBalance - curPrincipalPaid
' Get interest rate for loan
dblInterestRate = DLookup("LoanInterestRate", "CustomerLoans", _
"CustomerLoanID=" & Me.CustomerLoanID.Value)
' Calculate interest amount in this new payment
curInterestAmount = CCur(dblInterestRate / 12# * curPriorBalance)
' Round interest amount to cents
curInterestAmount = CCur(CInt(curInterestAmount * 100#) / 100#)
' Calculate principal amount in this new payment
curPrincipalAmount = curPaymentAmount - curInterestAmount
' Write principal and interest amounts into textboxes on form
Me.NameOfInterestAmountTextbox.Value = curInterestAmount
Me.NameOfPrincipalAmountTextbox.Value = curPrincipalAmount
' Show user what the new "balance amount" will be after this payment
MsgBox "After this payment is applied, the remaining loan balance amount
" & _
"will be " & Format((curOriginalBalance - curPrincipalPaid -
curPrincipalAmount), _
"$#,##0.00;-$#,##0.00") & ".", vbOK, "Remaining Balance Amount"
Else
Me.NameOfInterestAmountTextbox.Value = Null
Me.NameOfPrincipalAmountTextbox.Value = Null
End If
End Sub
' *** End of code