Re: Aging Function
From: Graham Mandeno (Graham.Mandeno_at_nomail.please)
Date: 03/08/04
- Next message: Natalia: "Re: run time error '2465' / Form issue"
- Previous message: Art: "Re: Running code from a form"
- In reply to: Melody: "Aging Function"
- Next in thread: Melody: "Re: Aging Function"
- Reply: Melody: "Re: Aging Function"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 9 Mar 2004 11:01:49 +1300
Hi Melody
First, it sounds like you are storing quite a lot of calculated information
in your table. This is a Bad Idea.
Both Age and AgeBucket can be calculated on the fly in a query as follows:
Age: DateDiff( "d", [InvoiceDate], Date() )
AgeBucket: Partition([Age],1,90,30)
The Partition function will return one of the following strings, depending
in the Age value:
" : 0"," 1:30","31:60","61:90","91:" (note the spaces in the first two)
If you create a crosstab query based on the AccountNumber as row header and
AgeBucket as column header, you can easily make a subreport to appear at the
bottom of your activity statement to show the AgeBuckets for each account.
Now, to your main question:
I think you need to have an extra table (say, "InvoiceReceipts") between
your Credits (payments) and Debits (invoices) to match what payment amounts
are being applied to what invoices. It needs three fields: CreditID,
DebitID, and Amount.
The amount received against each invoice is given by a query like this:
Select DebitID, Sum(Amount) as Received from InvoiceReceipts group by
DebitID;
(Let's call this query "qryInvoiceReceiptTotals")
The current Accounts Receivable are given by:
Select Debits.DebitID, Debits.AccountNumber,
Debits.InvoiceDate, Debits.DebitAmount,
qryInvoiceReceiptTotals.Received,
[DebitAmount]-Nz([Received]) as Outstanding
from Debits left join qryInvoiceReceiptTotals
on Debits.DebitID = qryInvoiceReceiptTotals.DebitID
where [DebitAmount]-Nz([Received]) > 0
order by Debits.InvoiceDate;
(Call this one "qryAccountsReceivable" and change the fieldnames to match
yours where necessary)
Now you need a VBA procedure to receive a payment for a given account on a
given date. It needs to:
1. Start a transaction (BeginTrans) to ensure that all the records are
written successfully.
2. Open a recordset based on qryAccountsReceivable selecting only thoise
records for the given account.
3. Write a record to the Credits table to record the total payment.
4. Set a variable equal to the amount paid.
5. For as long as the amount variable is >= the outstanding amount in the AR
record, write a record to the InvoiceReceipts table for the outstanding
amount, subtract that amount from the variable, and move to the next record.
6. If the amount variable is still positive, write another InvoiceReceipts
record for that amount (a partial payment) to be applied to the current AR
record.
7. Finish the transaction (CommitTrans)
As you say you are new to Access, you might find this exercise challenging,
but having a well designed structure without stored calculated fields will
pay off in the end :-)
Post back a reply if you need further help.
--
Good Luck!
Graham Mandeno [Access MVP]
New Zealand - Home of Lord of the Rings
"Melody" <anonymous@discussions.microsoft.com> wrote in message
news:91cd01c40540$c0661d60$a501280a@phx.gbl...
> 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: Natalia: "Re: run time error '2465' / Form issue"
- Previous message: Art: "Re: Running code from a form"
- In reply to: Melody: "Aging Function"
- Next in thread: Melody: "Re: Aging Function"
- Reply: Melody: "Re: Aging Function"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
