Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2004
    Posts
    173

    Unanswered: AR Aging - When Payments are Applied to Account vs to Invoices

    I am working on a database for a client in MS Access 2003 - the way they handle their accounting is as follows:

    Invoices are Generated
    Debit May be Generated - Interest - Service Charge, Etc
    Payments are Rceived - Payments are not applied to any particular Invoice, - just to the Account
    Credits may be Generated Against the Account - refund, adjustment

    Invoice terms are 30 days - from Invoice date
    Interest is Applied to Balances over 30 days


    I am trying to show Aging Balances on a Form

    Current - 30 Days - 60 days - 90+ Days

    How do I create a query, or set of queries that will allow me to do this?
    If a payment is applied to the account, I would like it to credit the oldest invoice - is this FIFO???????

    If I add the debits between now() and now()-30 minus the credits between now() and now()-30
    I get balances for 30 days when there were payments made... I also have to consider any debits/credits generated in the periods??


    My Transactions Table has the following fields

    TransID CustomerNo TransDate TransType Debit Credit
    100 100 1/22/07 Invoice 1060 125.00 0
    101 100 1/31/07 statement 0 0
    102 100 2/06/07 payment 0 125.00
    103 100 2/14/07 invoice 2010 325.00 0
    104 100 2/15/07 invoice 2015 125.00 0
    104 100 2/16/07 invoice 2400 125.00 0
    105 100 2/23/07 invoice 2402 125.00 0
    106 100 2/25/07 payment 0 400.00
    107 100 2/28/07 statement 0 0
    108 100 3/01/07 invoice 2600 250.00 0



    Any Help in How to Best Approach this is Appreciated - Thanks! - AB

  2. #2
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Is this FIFO????? Yes
    First, let me define the terms and assumptions I'm going to use here. First, just use credits to calculate the current balance, then ignore them afterwards. That way, all credits are applied without any date consideration (FIFO) and the charges are the only things that will be considered in this aging process.
    Because all money received is against the account only, what you need first is the balance due. Then subtract the most recent charges that are within the previous 30 days. If the amount due is more than the charges, then the charges total is the amount in the <=30day column. Do the same with the remainder of the current balance with the charges in the period of >30 and <=60days. That is the amount due for this column, etc...
    Hope this helps,

  3. #3
    Join Date
    Aug 2004
    Posts
    173

    Need Some Additional Help with Your Solution

    Because all money received is against the account only, what you need first is the balance due.
    ---------------

    I created 2 queries - one that groups and sums Debits by Account

    SELECT Transactions.CustomerNo, Sum(Transactions.Debit) AS SumOfDebit
    FROM Transactions
    GROUP BY Transactions.CustomerNo;

    and another that groups and sums Credits by Account

    SELECT Transactions.CustomerNo, Sum(Transactions.Credit) AS SumOfCredit
    FROM Transactions
    GROUP BY Transactions.CustomerNo;

    Then - another query that calculates the balance due, which is
    sumofdebits -sumofcredits

    SELECT qryAccounts_Debits.CustomerNo, qryAccounts_Debits.SumOfDebit, qryAccounts_Credits.SumOfCredit, [SumOfDebit]-[SumOfCredit] AS Balance
    FROM qryAccounts_Debits INNER JOIN qryAccounts_Credits ON qryAccounts_Debits.CustomerNo = qryAccounts_Credits.CustomerNo;

    This gives me the Balance Due for each account

    when you say the most recent charges - are you referring to Debits?

    I could use some help completing the issues below...

    ------------------

    Then subtract the most recent charges that are within the previous 30 days. If the amount due is more than the charges, then the charges total is the amount in the <=30day column.


    Do the same with the remainder of the current balance with the charges in the period of >30 and <=60days. That is the amount due for this column, etc...


    Hope this helps,

    ----------
    So far - so good :-) Thanks!

  4. #4
    Join Date
    Aug 2004
    Posts
    173

    Database Attached

    I attached a sample database with the above queries..

    Thanks! - AB
    Attached Files Attached Files
    Last edited by abinboston; 03-03-07 at 14:23. Reason: misspelling

Posting Permissions

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