AR Aging

[Melody]

I would like to use Access to create an A/R aging report
but I am stuck. So far, I have debits and credits listed
by account number and transaction date. I have created a
column called 'Age' which calculates the age of the
transaction and now I would like to create a field
called 'Bucket', which sets the field as '0-30', '31-60',
etc for each record, based on the age I calculated. Can
this be done in Access? If so, how?

To complete the aging report, I would also like to
subtract the total amount of credits per account number
from each line of transactions, so as to only age the
transactions left outstanding after the all the credits
have been applied to the debits (using the First-In, First
Out method. How can this be done? Any help would be
greatly appreciated.

Thanks,
Melody

 

[Melody]

I was able to create Age buckets using the IIf command in
a query. However I am still puzzled on how to match
credits to the debits to ultimately age out only the
records with outstanding debits. Logically I'd like to
sort the records by account number and then by age
(descending order), and use the following logic:

reference the following query tables:
debit transactions by account
credit totals by account

if debit <= total credit,
remove debit transaction record
set total credit = credit - debit
go to next line if total credit > 0.

if debit >= total credit or total credit = 0,
stop.

then i would take my remaining records and use a pivot
table to classify by account number and Age bucket.

I assume I will need to write SQL code for this. Can this
even be performed in Access? Since I have no SQL
experience, do you have any tips on how to write this?

Thanks,
Melody

 

[Chris]

To do what you want requires a procedural approach which
access queries don't provide. However all is not lost.
This can be done in VBA.
If you would like help with this, contact me direct on
chris
at
mercury-projects
dot
co
dot
nz