Re: Aging Function

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

Date: Tue, 9 Mar 2004 07:01:16 -0800

Graham,

Thanks for your help. I still have a few questions which
I'm hoping you can help me out with. I'm confused as to
what the following code does.
 
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;

All my accounts are old so is this needed? Also, I do not
currently have a DebitID or CreditID in my tables. Is
this a sequential number that I should add? And in the
VBA procedure narrated, does this call for a do
while/until loop? Also for the Age and AgeBuckets, I
initially used the datediff function for age and the Iif
function to calculate the buckets (since some of my ranges
are > 30) and then made a table with my results. Should I
simply work off the query tables? I didn't want to
initially because I did not want to be updating those
query tables when running the VBA code, however is that
better Access practice?

Thank you for your help,
Melody

>-----Original Message-----
>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
>>
>
>
>.
>

Relevant Pages

  • Re: Aging Function
    ... Why then do you need to produce aging reports on outstanding debits? ... >>and credits) is already entered and there will be no ... >>> for each account. ... >>>>DebitID, and Amount. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Aging Function
    ... When you say these are "old accounts", do you mean that all the data (debits ... I think to do this matching between the two tables (debits and credits) you ... > for each account. ... >>DebitID, and Amount. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Lord Help Me Project
    ... The fields in the main form only serve as account identification information, ... different amount types. ... subform are changed, but I'll post those issues on the forms community. ... If you think of your checkbook, it's the "transaction ...
    (microsoft.public.access.tablesdbdesign)
  • RE: Assigning Duplicate Values
    ... and numbers transaction amounts, and the colors are account numbers, I simply ... As an alternative I started looking at doing a count by dollar amount, ... Posting Year Posting Month Month Day Year GL Ref# Amount GL Account ...
    (microsoft.public.access.queries)
  • RE: Managing period accounts
    ... transaction documents and additional administrative fields. ... will only be the account number and the amount (where one is –ve and the ... The account number is the debtors account number. ...
    (microsoft.public.fox.programmer.exchange)