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
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
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
GROUP BY Transactions.CustomerNo;
and another that groups and sums Credits by Account
SELECT Transactions.CustomerNo, Sum(Transactions.Credit) AS SumOfCredit
GROUP BY Transactions.CustomerNo;
Then - another query that calculates the balance due, which is
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