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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Steve HoggITCommented:
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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SharathData EngineerCommented:
Go ahead and write your query. Experts are always there to help you.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.