Access Table Formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table in access used to work out payments against a loan amount and would like to take the original balance in the table and deduct each payment that has been marked paid to achieve a current balance number.

Can you help me with this please?
 
I'm sure one of the MVP's can give you the SQL directly,
but my approach to this would be, assuming you have a one-
to-many relationship between a Loan table (that includes
the original principle amount) and a Payments table (that
includes the Loan primary key as a foreign key) would be
to:

1. Create a Totals query, grouped by the Loan PK, and
summing the PaymentAmount field.
2. Create a second query, based on the first query and the
Loans table, with the calculated field:

Balance: [Loans].[Principle]-[qryPayments].[SumofPayments]

HTH
Kevin Sprinkel
-----Original Message-----
I have a table in access used to work out payments
Click to expand...
against a loan amount and would like to take the original
balance in the table and deduct each payment that has been
marked paid to achieve a current balance number.
 
Back
Top