Can somebody please help me?

[Melody]

I sent this earlier, sorry if you have read this twice. I
am trying to use Access to create an A/R aging report for
old accounts but I am stuck. So far, I have debits and
credits listed by account number and transaction date. I
have created a column called 'Age' using datediff which
calculates the age of the transaction, and I used the IIf
function to create a field called 'Bucket', which sets the
field as '0-30', '31-60', etc for each record.

At this point, I would like to subtract the total credit
amount per account number from each line of debit
transactions, so as to only age the
debit transactions left outstanding after the all the
credits have been applied, using the First-In, First Out
method.

Logically I'd like to sort the records by account number
and then by age (descending order), and use the following
logic:

reference and join the following query tables:
debit transactions by account
credit totals by account

for each account number,
if debit <= total credit,
set debit = 0
set total credit = credit - debit
if total credit > 0, loop
else stop

then i would take my remaining records where debit <> 0
and use a pivot table to classify by account number and
Age bucket.

I assume I will need to write SQL code for this. Can this
even be performed in Access? Since I have no SQL
experience, do you have any tips on how to write this? Any
help would be appreciated.

Thanks,
Melody

 

[Ted Allen]

Hi Melody,

It may or may not be possible to do what you want to do
with a series of update queries, but it sounds like this
would be much better to do in a simple Visual Basic
module.

Without knowing more about your workflow, it is hard to
give specific recommendations or code though. I would
recommend posting in the Forms Programming or Modules
groups, and also add a little more info about the
workflow (such as when is the code run, is it run
immediately each time a credit is entered, or is it
batched every so often). It sounds like it is probably
batched.

It shouldn't be too much of a problem to do what you want
to do, even if you have no previous programming
experience. The code will be fairly simple, and there
are a great many in the groups that are happy to help
with it. You can make it easier for them by letting them
know how/when you want to run the code, and how the info
is stored in the tables and how the tables relate. You
don't have to list all of the fields in your tables (or
queries), just the key fields that will be used by the
code.

I would also suggest that you specify that you would 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).

HTH, I'll look for future posts.

-Ted Allen

 

[Melody]

Ted,

Thank you - your response does help. I will post my
question on the programming and module groups with more
specific relevant information. With your Visual Basic
idea, I think I am finally starting to see the light...

Melody

 

[Ted Allen]

Hi Melody,

After reading your post in modules I think that it sounds
like this may be a one-time deal that you are trying to
do to resolve some old accounts. If that is the case, it
may be better to use an update query as you originally
suggested (I may have misunderstood your problem
initially).

I think that what you could do would be to use IIF() and
DSum() functions to do the update that you want.

But, just to be on the safe side I would work on a copy
of the data. You may also want to create a new field for
the updated outstanding debit amount so that you can
check afterward to see that the Sum of the original Debit
Amount minus the Sum of the original Credit amount equals
the sum of the calculated Outstanding debit amounts.

Note that this method would not be the way you would want
to handle this for a working database of ongoing debits
and credits. But, if this is just a one-time task, this
method will give you a quick and dirty semi-spreadsheet
approach.

Following are the steps that should give you what you
want:

1. Add a field to your debit table named OutstandingDebit
2. Create a query linking the debit and credit tables by
Account #. The only purpose of this will be to allow
your query to reference the total credit for each account
in the formula's to calculate the outstanding credit for
that line.
3. Add the OutstandingDebit field to the query.
4. Change the query type to Update.
5. In the "Update To" field of the query, use the
following functions to build your formula (I don't know
your actual table and field names so I have assumed them,
you will have to adjust them to fit). You may even want
to experiment with these individually to see how they
work prior to putting them together in the update query.

DSum formula to find the sum of all debits older than the
current account:

OlderDebits: DSum
("[DebitAmt]","DebitTableName","[DebitAcctID] = " &
[DebitAcctID] & " AND [DebitAge] > " & [DebitAge])

Note that in the DSum() function, the third expression
appears to refer to field names redundantly. But, the
field references inside the quotes are referring to the
fields in the table, while the field names outside the
quotes are referring to the value of those fields in the
current record of the query. So the function is saying
to sum the DebitAmt field in the DebitTableName table for
all records that meet the criteria in the third
expression (where DebitAcctID equals the DebitAcctID of
the current record and the DebitAge is greater than the
DebitAge of the Current Record. This value is labeled as
the OlderDebits field in your query.

iif function to see if the credit amount is greater than
the older debits amount. If so, calculate the
outstanding debit amount. This is the formula that you
would place in the Update To line under the Outstanding
Debits field.

iif([CreditAmt]>[OlderDebits],iif([CreditAmt]-
[OlderDebits]>[DebitAmt],0,[DebitAmt]-([CreditAmt]-
[OlderDebits])),[DebitAmt])

This should work provided I did not make any syntax
errors and provided that the debits table does not have
multiple listings for the same debit age.

I think that you could also use a subquery in place of
the DSum() statement, but I'm also not that good with
SQL, so I often just use the Domain Aggregate functions
for one-time queries such as this where ongoing
performance isn't really an issue (I would be more likely
to make syntax errors if I tried to post the full SQL
statement here without being able to try it out). Again,
since I'm not completely sure I understand your situation
I'm not sure if this will work for you, but I thought
that I would offer it just in case it would help.

Post back if you want to try this method and you have
problems.

-Ted Allen