Re: Creating auto loan database

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

On Nov 28, 12:07 pm, "Ken Snell \(MVP\)"
<kthsneisll...@xxxxxxxxxxxxxxxxxx> wrote:
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>

<gensi...@xxxxxxxxx> wrote in message

news:75588590-7a07-40e6-a306-882053f283db@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx



On Nov 27, 6:32 pm, "Ken Snell \(MVP\)"
<kthsneisll...@xxxxxxxxxxxxxxxxxx> wrote:
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>

<gensi...@xxxxxxxxx> wrote in message

news:626969dc-78b1-4808-bf1a-8cc4caff8a3d@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

On Nov 27, 8:32 am, "Ken Snell \(MVP\)"
<kthsneisll...@xxxxxxxxxxxxxxxxxx> wrote:
<gensi...@xxxxxxxxx> wrote in message

news:42bd041b-cb88-434a-8955-cfb6e98b12a3@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

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>- 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.- Hide quoted text -

- Show quoted text -

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

Relevant Pages

  • Re: Creating auto loan database
    ... Do for all queries involved in the "outstanding balance" ... query. ... into which you enter the total payment in order to run the VBA code ... Sum(CustomerLoanPayments.PrincipalPaid) AS ...
    (microsoft.public.access.forms)
  • Re: Running Total
    ... The following query will compute the ... ordering the rows by customer and within reverse date ... TransactionID column to allow for two or more transactions by one customer ... OR T2.TransactionDate T1.TransactionDate)) AS Balance ...
    (microsoft.public.access.queries)
  • Re: Creating auto loan database
    ... I am assuming that this query will return a balance ... depend upon the presence of at least one payment by the customer? ... Outstanding Balance query; and, if it's using one or more queries in it, the ...
    (microsoft.public.access.forms)
  • Re: Update Table
    ... turn the select query into an insert into query (or add a first ... INSERT INTO table3(code, name, date, balance) ... I need your help to make an update querrie. ... each customer if the customer exist for that date in the ...
    (microsoft.public.access.queries)
  • Re: Update Table
    ... turn the select query into an insert into query (or add a first ... INSERT INTO table3(code, name, date, balance) ... I need your help to make an update querrie. ... each customer if the customer exist for that date in the ...
    (microsoft.public.access.queries)