Re: Aging Function
From: Graham Mandeno (Graham.Mandeno_at_nomail.please)
Date: 03/09/04
- Next message: Alex Ivanov: "Re: XP not talking to VB"
- Previous message: Elwin: "trapping keystrokes"
- In reply to: Melody: "Re: Aging Function"
- Next in thread: Melody: "Re: Aging Function"
- Reply: Melody: "Re: Aging Function"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 10 Mar 2004 10:51:59 +1300
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: Alex Ivanov: "Re: XP not talking to VB"
- Previous message: Elwin: "trapping keystrokes"
- In reply to: Melody: "Re: Aging Function"
- Next in thread: Melody: "Re: Aging Function"
- Reply: Melody: "Re: Aging Function"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
