Results 1 to 3 of 3
  1. #1
    Gina Maylone is offline Always learning
    Windows 7 64bit Access 2013
    Join Date
    Jun 2013
    Location
    Afton, MN
    Posts
    544

    30/60/90 aging query on amoritzation table

    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!


  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  3. #3
    Gina Maylone is offline Always learning
    Windows 7 64bit Access 2013
    Join Date
    Jun 2013
    Location
    Afton, MN
    Posts
    544
    Thanks Orange, but these aren't bad debts. They are a special assessment for a homeowners association. I must admit, working with an amortization schedule has been a challenge. I've managed to get what I need with this;
    Code:
    SELECT OWNERS.LASTNAME, OWNERS.OwnerKey, LoanT.OwnerKey, ScheduleT.LoanID, ScheduleT.AmountDue, ScheduleT.AmountPaid, ScheduleT.DueDate, DateDiff("d",[duedate],Date()) AS aging, ScheduleT.PaymentNumber
    FROM (OWNERS INNER JOIN LoanT ON OWNERS.OwnerKey = LoanT.OwnerKey) INNER JOIN ScheduleT ON LoanT.LoanID = ScheduleT.LoanID
    WHERE (((ScheduleT.AmountDue)<>0) AND ((ScheduleT.AmountPaid)=0) AND ((ScheduleT.DueDate)<Date()))
    ORDER BY OWNERS.OwnerKey;
    Gives me:
    LASTNAME OwnerKey AmountDue DueDate aging
    MILLER 1403 $72.21 7/1/2016 29

    Thanks again!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Stock aging code
    By UncleKay in forum Access
    Replies: 29
    Last Post: 06-05-2016, 07:31 PM
  2. Aging Report
    By Jblackbelt in forum Reports
    Replies: 10
    Last Post: 10-08-2014, 08:02 PM
  3. Case Aging report
    By mavisyew in forum Reports
    Replies: 1
    Last Post: 09-02-2014, 05:35 AM
  4. Designing A/R aging Form
    By Ray67 in forum Database Design
    Replies: 22
    Last Post: 08-21-2014, 06:07 PM
  5. Aging A/P function
    By nim73 in forum Programming
    Replies: 0
    Last Post: 05-07-2009, 01:23 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums