Re: Aging Function
From: Melody (anonymous_at_discussions.microsoft.com)
Date: 03/11/04
- Next message: Allen Browne: "Re: Detecting if a form is open"
- Previous message: Melody: "Aging Function"
- In reply to: Graham Mandeno: "Re: Aging Function"
- Next in thread: Graham Mandeno: "Re: Aging Function"
- Reply: Graham Mandeno: "Re: Aging Function"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 10 Mar 2004 16:36:18 -0800
Graham,
I now understand your logic and you are correct, that is
what I want to do. I will work on the VBA code you
suggested and let you know if I get stuck.
Thanks for your advice,
Melody
>-----Original Message-----
>Hi Melody
>
>When you say these are "old accounts", do you mean that
all the data (debits
>and credits) is already entered and there will be no
further activity on the
>accounts? If this is the case, then I'm a bit confused
about the purpose of
>your aging report. I had assumed that it was to produce
customer statements
>to show outstanding debts.
>
>In any case, do you not wish to apply payments to
outstanding debits in the
>order that those debts were incurred? For example:
> Jan 2003 Invoice 1 $100
> Feb 2003 Invoice 2 $200
> Mar 2003 Payment 1 $250
>This payment should be applied to pay off Invoice 1 in
full, and Invoice 2
>in part, leaving an outstanding balance of $50
>
>I think to do this matching between the two tables
(debits and credits) you
>really need the third table that specifies how much is
being paid off which
>debit.
>
>In the example above, there would be two records
generated in the
>intermediate table:
> Invoice 1 / Payment 1 / $100
> Invoice 2 / Payment 1 / $150
>
>Perhaps you could give us some more information on the
structure of your
>existing tables and *exactly* what you are wanting to
achieve.
>--
>Good Luck!
>
>Graham Mandeno [Access MVP]
>New Zealand - Home of Lord of the Rings
>
>
>"Melody" <anonymous@discussions.microsoft.com> wrote in
message
>news:994901c405e7$5f6bc8a0$a401280a@phx.gbl...
>> 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
>> >>
>> >
>> >
>> >.
>> >
>
>
>.
>
- Next message: Allen Browne: "Re: Detecting if a form is open"
- Previous message: Melody: "Aging Function"
- In reply to: Graham Mandeno: "Re: Aging Function"
- Next in thread: Graham Mandeno: "Re: Aging Function"
- Reply: Graham Mandeno: "Re: Aging Function"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
