Access query - sum quarterly debt payments for different mortgages

I need to write a query in my Access 2007 database to find total debt payments made by a property in a quarter.  This involves three tables:
     tblProperties - PropertyID is primary key
     tblDebts - DebtID is primary key, PropertyID is a foreign key - 1-to-many relationship
     tlkpValidDate - list of dates used throughout the database & related info, in this case quarter-ending dates.
     tblDebtService - DebtServiceID is primary key, DebtID & ValidDate are foreign keys.  Other fields are DebtPrincipal, DebtInterestPaid, and DebtInterestAccrued.

I have the user pick the quarter of interest by selecting the quarter-end date in a pop-up form.  I can create a calculated field to determine total debt payment for each mortgage for the quarter.  Now I have a list of properties and the debt payments made for each mortgage, but what I want is the sum of all debt payments, or one observation for each property for that quarter so I can use it later (in another query possibly?) and calculate debt coverage ratio by bringing in income.

Also, comments on my structure or logic are appreciated, as I am not a database expert.
Who is Participating?
HoggZillaConnect With a Mentor Commented:
See if this does it.

SELECT a.PropertyID, d.Quarter, SUM(c.DebtPayment)
FROM tblProperties a
INNER JOIN tblDebts b
  ON a.PropertyID = b.PropertyID
INNER JOIN tblDebtService c
  ON b.DebitID = c.DebitID
INNER JOIN tlkpValidDate d
  ON c.ValidDate >= d.QuarterStartDate
 AND c.ValidDate <= d.QuarterEndDate
 AND d.Quarter= ?

Open in new window

SharathData EngineerCommented:
Go ahead and write your query. Experts are always there to help you.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.