Access query - sum quarterly debt payments for different mortgages

Posted on 2008-11-11
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

    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 40

    Expert Comment

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

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    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…

    729 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now