Re: Creating auto loan database
- From: "Ken Snell \(MVP\)" <kthsneisllis9@xxxxxxxxxxxxxxxxxx>
- Date: Sat, 24 Nov 2007 15:31:46 -0500
Comments inline...
--
Ken Snell
<MS ACCESS MVP>
<gensicki@xxxxxxxxx> wrote in message
news:95e544ba-07e8-4be2-9560-549c9ca0181c@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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\)"
<kthsneisll...@xxxxxxxxxxxxxxxxxx> 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>
.
- Follow-Ups:
- Re: Creating auto loan database
- From: gensicki
- Re: Creating auto loan database
- References:
- Creating auto loan database
- From: gensicki
- Re: Creating auto loan database
- From: Ken Snell \(MVP\)
- Re: Creating auto loan database
- From: gensicki
- Creating auto loan database
- Prev by Date: Re: Access 2003 - Leap year 2008
- Next by Date: Re: Show list of data from query than can be selected.
- Previous by thread: Re: Creating auto loan database
- Next by thread: Re: Creating auto loan database
- Index(es):
Relevant Pages
|
