[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Access query - sum quarterly debt payments for different mortgages

Posted on 2008-11-11
Medium Priority
Last Modified: 2012-05-05
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.
Question by:Phereklos
LVL 17

Accepted Solution

HoggZilla earned 2000 total points
ID: 22937069
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

LVL 41

Expert Comment

ID: 22944048
Go ahead and write your query. Experts are always there to help you.

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question