Hello all!
I need to create an aging query on an amortization table (it's a 5 year plan). Right now I have this:
Code:
SELECT OWNERS.lastname, (SELECT sum(Amountdue) FROM schedulet WHERE datediff("d", dueDate, now) < 31 AND paidDate is NULL AND owners.ownerkey ) AS [30 Days or Less], (SELECT sum(Amountdue) FROM schedulet WHERE datediff("d", dueDate, now) > 30 AND datediff("d",dueDate, now) < 61 AND paidDate is NULL AND owners.ownerkey ) AS [31-60 Days], (SELECT sum(Amountdue) FROM schedulet WHERE datediff("d", dueDate, now)> 60 AND datediff("d", duedate, now) < 91 AND paidDate is NULL AND owners.ownerkey ) AS [61-90 Days], (SELECT sum(Amountdue) FROM schedulet WHERE datediff("d", dueDate, now) > 90 AND datediff("d", duedate, now) < 91 AND paidDate is NULL AND owners.ownerkey ) AS [91-120 Days], (SELECT sum(Amountdue) FROM schedulet WHERE datediff("d", dueDate, now) > 120 AND datediff("d", duedate, now) < 91 AND paidDate is NULL AND owners.ownerkey ) AS [121 Days or More]
FROM (OWNERS INNER JOIN LoanT ON OWNERS.OwnerKey = LoanT.OwnerKey) INNER JOIN ScheduleT ON LoanT.LoanID = ScheduleT.LoanID
WHERE (((OWNERS.ownerkey)=[Forms]![Owners]![OwnerKey]))
ORDER BY OWNERS.ownerkey;
Which results in this:
lastname 30 Days or Less 31-60 Days 61-90 Days 91-120 Days 121 Days or More
Smith $1,728,862.40 $3,131.54 $0.00
Obviously not what I'm looking for. When I take out "Sum" from "sum(Amountdue)" I get an error,"At most one record can be returned by this subquery. (Error 3354)" and the query doesn't run.
What I WANT is (their past due monthly payments [AmountDue]):
lastname 30 Days or Less 31-60 Days 61-90 Days 91-120 Days 121 Days or More
Smith $72.21 $72.21 $72.21
A few more details. The basic query contains the tables: Owners, LoanT and ScheduleT (loant is used just for linking the owners to their schedules).
Any thoughts on how I can get the numbers I need? Thank you in advance! I hope you all have a fantastic weekend!