Results 1 to 4 of 4

Thread: Aging Buckets

  1. #1
    Join Date
    Sep 2009
    Posts
    1

    Unanswered: Aging Buckets

    I need help in creating aging buckets in access:
    Current, >=30days, >=60days, >=90days, >120days

    I have two tables - tblrequests is what we send out to the collection agency
    tblcollections is what the collection agency collects on our behalf. There are two columns in that table - one paid to agency and one paid to us. I've created a query to add those two columns - qrysumbycustomercollections. Another query is created to sum all the requests sent out to the collection agency - qrysumbycustomerrequests. A third query is created to give me the outstanding balance due by each customer. This needs to be divided into aging buckets.

    I have attached by file for reference

    I would appreciate any help.

    Thanks
    Attached Files Attached Files

  2. #2
    Join Date
    Apr 2009
    Posts
    14
    Which field (date) do you want to use for the aging buckets?

    I'd also make some assumptions to do this, can you please confirm that I've got it right...
    You will end up with an extra field in your last query that shows which bucket the record falls into (possibly also in the other queries, depending on where the date is coming from).
    Your buckets are actually:
    <30days
    <60days and >=30days
    <90days and >=60days
    <120days and >=90days
    >=120days

  3. #3
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    Or maybe...

    SELECT
    IIF(datDateField<=Now()+30,fldAmountField, 0) AS 30DaysAmount,
    IIF(datDateField> Now()+30 AND <=()+60, fldAmountField, 0) AS 60DaysAmount,
    IIF(datDateField > Now()+60 AND <=Now()+90, fldAmountField, 0) AS 90DaysAmount,
    IIF(datDateField >Now()+90, fldAmountField, 0) AS 120PlusDaysAmount
    FROM YourTableOrQuery

    (If you have an SQL backend, use CASE statements and GetDate() instead)

    CASE WHEN datDate<=GetDate()+30 Then fldAmountField ELSE 0 END as 30DaysAmount...etc

    You can then use these amounts in summation for reports.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    How about datefield mod 30?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •