A
abinboston via AccessMonster.com
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
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