Aging Function
From: Ted Allen (anonymous_at_discussions.microsoft.com)
Date: 03/08/04
- Next message: Alex Ivanov: "Re: query def as datasource for a query"
- Previous message: Graham Mandeno: "Re: Table thinks there's another user"
- In reply to: Melody: "Aging Function"
- Next in thread: Ted Allen: "Aging Function"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 8 Mar 2004 14:44:16 -0800
Hi Melody,
I want to check back once more for a little more
clarification. I agree with the earlier post that it
sounds like the database could likely use some structural
changes, but you mentioned that information will not be
added to the tables in the future so I want to check to
see what your end purpose is. Are you just trying to
resolve the outstanding debits for existing data (one
time deal) and then you are done with this database for
good, or will you be modifying the database to be able to
handle future credits and debits as well?
-Ted Allen
>-----Original Message-----
>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
>
>.
>
- Next message: Alex Ivanov: "Re: query def as datasource for a query"
- Previous message: Graham Mandeno: "Re: Table thinks there's another user"
- In reply to: Melody: "Aging Function"
- Next in thread: Ted Allen: "Aging Function"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
