Tags: access, accounting, aging, applied, client, database, followsinvoices, generateddebit, handle, invoices, microsoft, mysql, oracle, payments, sql, working

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

On Database » Microsoft Access

4,559 words with 3 Comments; publish: Wed, 12 Dec 2007 09:20:00 GMT; (25062.50, « »)

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

All Comments

Leave a comment...

  • 3 Comments
    • 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,

      #1; Tue, 11 Dec 2007 21:04:00 GMT
    • 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!

      #2; Tue, 11 Dec 2007 21:05:00 GMT
    • I attached a sample database with the above queries..

      Thanks! - AB

      #3; Tue, 11 Dec 2007 21:06:00 GMT