Creating auto loan database

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

www.onthegomotors.net

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.


--

Ken Snell
MS ACCESS MVP




wrote in message
...
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

www.onthegomotors.net

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?

On Nov 24, 10:27 am, "Ken Snell \(MVP\)"
wrote:
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.

--

Ken Snell
MS ACCESS MVP

wrote in message

...



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

www.onthegomotors.net- Hide quoted text -

- Show quoted text -

Comments inline...

--

Ken Snell
MS ACCESS MVP


wrote in message
...
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.



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

Yes.



On Nov 24, 10:27 am, "Ken Snell \(MVP\)"
wrote:
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.

--

Ken Snell
MS ACCESS MVP

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


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).

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.



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

Yes.





On Nov 24, 10:27 am, "Ken Snell \(MVP\)"
wrote:
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.

--

Ken Snell
MS ACCESS MVP- Hide quoted text -

- Show quoted text -

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




wrote in message
...
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


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).

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.



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

Yes.

On Nov 25, 12:57 pm, "Ken Snell \(MVP\)"
wrote:
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....

wrote in message
...
On Nov 25, 12:57 pm, "Ken Snell \(MVP\)"
wrote:
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

On Nov 26, 5:58 pm, "Ken Snell \(MVP\)"
wrote:
wrote in message

...





On Nov 25, 12:57 pm, "Ken Snell \(MVP\)"
wrote:
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.

wrote in message
...
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.

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