Aging Function

From: Melody (anonymous_at_discussions.microsoft.com)
Date: 03/08/04 

Date: Mon, 8 Mar 2004 11:08:33 -0800

Hi,

I am new to Access and I am trying to use Access to create
an A/R aging report for old accounts but I am stuck. So
far, in my debits table I have debits, the Age (in days),
and Age Bucket ('0-30', '31-60', etc) listed by account
number and transaction date. In my credits table I have
the total amount of credits per account number.

At this point, I would like to subtract the total credit
amount per account number from each line of debit
transactions starting with the oldest debit transactions,
so as to only age on those records left with outstanding
debits after the all the credits have been applied, using
the First-In, First Out method.

Somebody from the queries group recommended that a simple
VBA procedure be written to do the above, however I have
no VBA experience and limited programming knowledge so I'm
hoping somebody can help me. Here's what I'd like to do:

Reference and join the following query tables by account
number:
-debit transactions by account (which has debit
transactions by account number and transaction date,age in
descending order, and the debit amount per transaction
date)
-credit totals by account

for each account number,
while debit <= total credit,
set creditnew = total credit - debit
set credit = creditnew
set debit = 0
go to next debit record

if debit > total credit
set debitnew = debit - total credit
set debit = debitnew
stop

Since new information will not be added to the existing
tables, I would like to run this procedure once to
ultimately have a table that consists of only the records
left with outstanding debits after all the credits have
been applied. I assume I can take my remaining records
where debit <> 0 and use a pivot table to classify by
account number and Age bucket for the final aging report.

The person from the query group also suggested I specify
that I'd like to use transactions to make sure that all
updates are applied or otherwise roll them all back (so
that if the code will verify that all updates can be made
before committing them, this will avoid revising a credit
amount without an equal change to a debit amount).

Do you have any tips on how to write this? Again I have no
Visual Basic experience so any help would be appreciated.

Thanks,
Melody

Relevant Pages

  • Re: Aging Function
    ... bottom of your activity statement to show the AgeBuckets for each account. ... DebitID, and Amount. ... I would like to subtract the total credit ... > transactions starting with the oldest debit transactions, ...
    (microsoft.public.access.modulesdaovba)
  • Re: New Rules On Cheque Clearance .
    ... but do not credit the target account for 3 days. ... The credit the target acount 3 days later. ... No you are confusing the Debit & Credit clearings. ... ITYWF that it is the drawer's bank that debits the drawer's account, ...
    (uk.legal)
  • Can somebody please help me?
    ... to use transactions to make sure that all updates are ... this will avoid revising a credit amount ... >credits listed by account number and transaction date. ... >calculates the age of the transaction, ...
    (microsoft.public.access.queries)
  • RE: Still Trying re: formula or function
    ... I am doing the summary query ... > do a summary query to get the debit total you want. ... > really need to have a debit and credit column incidentally. ... >> The relevant fields are entity, amount, account, and debit or credit (call it ...
    (microsoft.public.access.modulesdaovba)
  • Can somebody please help me?
    ... >to use transactions to make sure that all updates are ... >without an equal change to a debit amount). ... >>credits listed by account number and transaction date. ... >>calculates the age of the transaction, ...
    (microsoft.public.access.queries)
Quantcast