Phereklos
asked on
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sharath S
Go ahead and write your query. Experts are always there to help you.