Re: Aging Function
From: Graham Mandeno (Graham.Mandeno_at_nomail.please)
Date: 03/11/04
- Next message: Julian Cropley: "Data Entry Property"
- Previous message: Steve: "Data Entry Property"
- In reply to: Melody: "Re: Aging Function"
- Next in thread: Ted Allen: "Aging Function"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 12 Mar 2004 12:17:09 +1300
Hi Melody
OK - I'll keep an eye on this thread, so post back here if you need more
help.
I'm still a bit confused though... If these are "old" accounts, then I
assume they are all settled and there will be no further activity on them.
Why then do you need to produce aging reports on outstanding debits?
--
Good Luck!
Graham Mandeno [Access MVP]
New Zealand - Home of Lord of the Rings
"Melody" <anonymous@discussions.microsoft.com> wrote in message
news:a77b01c40700$de490ba0$a301280a@phx.gbl...
> 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: Julian Cropley: "Data Entry Property"
- Previous message: Steve: "Data Entry Property"
- In reply to: Melody: "Re: Aging Function"
- Next in thread: Ted Allen: "Aging Function"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
