Aging Function

[Melody]

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

 

[Graham Mandeno]

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.

 

[Ted Allen]

Hi Melody,

I want to check back once more for a little more
clarification. I agree with the earlier post that it
sounds like the database could likely use some structural
changes, but you mentioned that information will not be
added to the tables in the future so I want to check to
see what your end purpose is. Are you just trying to
resolve the outstanding debits for existing data (one
time deal) and then you are done with this database for
good, or will you be modifying the database to be able to
handle future credits and debits as well?

-Ted Allen

 

[Ted Allen]

Hi Melody,

If this is a one-time task that you are trying to do, and
you won't have future credits/debits to deal with, I have
posted a quick and dirty method that you can use to run
an update query using the DSum() and IIF() functions in
response to your post in the Queries group.

If this is the case (that it is a one-time task), I think
the query will be easier to deal with than learning the
VBA syntax (sorry if I mislead you by misunderstanding
your original post). But, if it's not just a one-time
task, post back and we should be able to help you put the
VBA together.

-Ted Allen

 

[Melody]

Ted, you're right, this is just a one-time task. Can you
send me the link to the 'quick and dirty method' you
referred to? I am also going play around with Access,
using the info posted by Graham (Thank you Graham!) -- you
will probably see a response from me soon.

Thanks so much for all of your tips!

Melody

 

[Ted Allen]

Hi Melody,

I'm not really sure how to link to other discussions.
The message was posted below your post in the queries
group, but following is the text

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

 

[Melody]

Ted, I think I understand what you're saying. These are
all old accounts though, so I'm thinking I can leave out
the dsum command. However, when I am using the iif
function to see if the credit amount is greater than the
debits amount and calculating the outstanding debit
amount, is this also updating the credit account to the
lesser amount and carrying that value over to the next
record? If not, I'm not sure I can use a query anymore,
because I need to update the outstanding credit line too
and apply it to the following record line item.

As for Graham's method, I'm confused as to what this 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;

Is this needed if all my accounts are old? 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? If you can help me out once again, I'd
appreciate it.

Thanks,
Melody

-----Original Message-----
Hi Melody,

I'm not really sure how to link to other discussions.
The message was posted below your post in the queries
group, but following is the text

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

-----Original Message-----
Ted, you're right, this is just a one-time task. Can you
send me the link to the 'quick and dirty method' you
referred to? I am also going play around with Access,
using the info posted by Graham (Thank you Graham!) -- you
will probably see a response from me soon.

Thanks so much for all of your tips!

Melody

.

.

 

[Melody]

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

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

.

 

[Ted Allen]

Hi Melody,

The DSum() function will definitely be required for your
update, because it is used to sum all of the older debits
so that you can compare the sum of the older debits to
the total credit amount, and use that to calculate your
adjusted debit amount for that particular line.

If you are able to successfully calculate the outstanding
debit amounts, it will be pretty easy to calculate the
revised credit amount by subtracting the difference of
the sum of the original debits for an account and the sum
of the revised debits for an account.

Again though, this will not work without some changes if
a given account will have multiple debit listings for a
given age. To check this, you could try setting the
account # and the age fields in combination as the
primary key for the table. You will get an error message
if duplicate age entries exist for an account.

Regarding your question on Graham's post, if you cannot
do what you want to do via update queries, and you decide
to write a module, you will need to create recordsets to
work with in code. You would need two recordsets, one
with the credit information and one with the detailed
debit information, or you could possibly use one
recordset if you were to add the total credit amount to
each line of your debit recordset as a reference (similar
to what I suggested in the query update). The text that
you asked about from Graham's post was the SQL text that
would generate the recordsets (the list of records that
you would then step through in code to calculate the
revised debit amounts). There are other ways of
generating your recordsets as well, such as specifying an
existing table or query name.

If you want to try the update query method, I think it
would be a good idea to start by trying to get the DSum()
function to work in a regular select query. The DSum()
function statement is really the key to being able to do
what you want to do, because it allows you, for each
record, to know what the sum of the older debits is, and
therefore how much (if any) credit should be applied to
the debit of the current record.

-Ted Allen

-----Original Message-----
Ted, I think I understand what you're saying. These are
all old accounts though, so I'm thinking I can leave out
the dsum command. However, when I am using the iif
function to see if the credit amount is greater than the
debits amount and calculating the outstanding debit
amount, is this also updating the credit account to the
lesser amount and carrying that value over to the next
record? If not, I'm not sure I can use a query anymore,
because I need to update the outstanding credit line too
and apply it to the following record line item.

As for Graham's method, I'm confused as to what this 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;

Is this needed if all my accounts are old? 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? If you can help me out once again, I'd
appreciate it.

Thanks,
Melody

-----Original Message-----
Hi Melody,

I'm not really sure how to link to other discussions.
The message was posted below your post in the queries
group, but following is the text

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

-----Original Message-----
Ted, you're right, this is just a one-time task. Can you
send me the link to the 'quick and dirty method' you
referred to? I am also going play around with Access,
using the info posted by Graham (Thank you Graham!) -- you
will probably see a response from me soon.

Thanks so much for all of your tips!

Melody

-----Original Message-----
Hi Melody,

If this is a one-time task that you are trying to do, and
you won't have future credits/debits to deal with, I have
posted a quick and dirty method that you can use to run
an update query using the DSum() and IIF() functions in
response to your post in the Queries group.

If this is the case (that it is a one-time task), I think
the query will be easier to deal with than learning the
VBA syntax (sorry if I mislead you by misunderstanding
your original post). But, if it's not just a one- time
task, post back and we should be able to help you put the
VBA together.

-Ted Allen
-----Original Message-----
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

.

.

.

.

.

 

[Melody]

Ted, I finally gotcha. I think the query method will work
for the results I need. I did as you recommended and
started off by creating a field which calculates the dsum
for each record in the debit table, although for some
reason, all OlderDebits values are null.

This is the SQL code which I can't get to work:
SELECT [Debits by Trx Date Copy].[Active or Inactive
Account], [Debits by Trx Date Copy].[Trx Date], [Debits by
Trx Date Copy].Age, [Debits by Trx Date Copy].Debit, DSum
([Debit],"Debits By Trx Date Copy","[Active or Inactive
Account]"=" & [Active or Inactive Account]" And [Age]>" &
[Age]") AS OlderDebits, [Total Credits By Account
Copy].TotalCredit
FROM [Debits by Trx Date Copy] LEFT JOIN [Total Credits By
Account Copy] ON [Debits by Trx Date Copy].[Active or
Inactive Account] = [Total Credits By Account Copy].
[Active or Inactive Account];

After I get the dsum calculation to work within Query 1,
I plan to run a 2nd query from query 1, since when i try
to refer to OlderDebits within query 1, it prompts me for
an OlderDebits parameter.

Can you please help me figure out what I'm doing wrong in
the above code?

Thanks,
Melody

-----Original Message-----
Hi Melody,

The DSum() function will definitely be required for your
update, because it is used to sum all of the older debits
so that you can compare the sum of the older debits to
the total credit amount, and use that to calculate your
adjusted debit amount for that particular line.

If you are able to successfully calculate the outstanding
debit amounts, it will be pretty easy to calculate the
revised credit amount by subtracting the difference of
the sum of the original debits for an account and the sum
of the revised debits for an account.

Again though, this will not work without some changes if
a given account will have multiple debit listings for a
given age. To check this, you could try setting the
account # and the age fields in combination as the
primary key for the table. You will get an error message
if duplicate age entries exist for an account.

Regarding your question on Graham's post, if you cannot
do what you want to do via update queries, and you decide
to write a module, you will need to create recordsets to
work with in code. You would need two recordsets, one
with the credit information and one with the detailed
debit information, or you could possibly use one
recordset if you were to add the total credit amount to
each line of your debit recordset as a reference (similar
to what I suggested in the query update). The text that
you asked about from Graham's post was the SQL text that
would generate the recordsets (the list of records that
you would then step through in code to calculate the
revised debit amounts). There are other ways of
generating your recordsets as well, such as specifying an
existing table or query name.

If you want to try the update query method, I think it
would be a good idea to start by trying to get the DSum()
function to work in a regular select query. The DSum()
function statement is really the key to being able to do
what you want to do, because it allows you, for each
record, to know what the sum of the older debits is, and
therefore how much (if any) credit should be applied to
the debit of the current record.

-Ted Allen

-----Original Message-----
Ted, I think I understand what you're saying. These are
all old accounts though, so I'm thinking I can leave out
the dsum command. However, when I am using the iif
function to see if the credit amount is greater than the
debits amount and calculating the outstanding debit
amount, is this also updating the credit account to the
lesser amount and carrying that value over to the next
record? If not, I'm not sure I can use a query anymore,
because I need to update the outstanding credit line too
and apply it to the following record line item.

As for Graham's method, I'm confused as to what this 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;

Is this needed if all my accounts are old? 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? If you can help me out once again, I'd
appreciate it.

Thanks,
Melody

-----Original Message-----
Hi Melody,

I'm not really sure how to link to other discussions.
The message was posted below your post in the queries
group, but following is the text

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


-----Original Message-----
Ted, you're right, this is just a one-time task. Can
you
send me the link to the 'quick and dirty method' you
referred to? I am also going play around with Access,
using the info posted by Graham (Thank you Graham!) --
you
will probably see a response from me soon.

Thanks so much for all of your tips!

Melody

-----Original Message-----
Hi Melody,

If this is a one-time task that you are trying to do,
and
you won't have future credits/debits to deal with, I
have
posted a quick and dirty method that you can use to run
an update query using the DSum() and IIF() functions in
response to your post in the Queries group.

If this is the case (that it is a one-time task), I
think
the query will be easier to deal with than learning the
VBA syntax (sorry if I mislead you by misunderstanding
your original post). But, if it's not just a one- time
task, post back and we should be able to help you put
the
VBA together.

-Ted Allen
-----Original Message-----
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

.

.

.

.

.

.

 

[Melody]

Ted,

BTW, as followup to my last post(pasted below), I believe
the error has something to do with setting the accounts
equal to each other in the dsum function, because when I
used only the dates as criteria I was able to generate
values but not when using the account criteria. The
accounts [Active or Inactive Account] are text strings. I
played around with the & and " characters but it did not
help. If you happen to know why this is not working,
please let me know.

Thanks,
Melody


Ted, I finally gotcha. I think the query method will work
for the results I need. I did as you recommended and
started off by creating a field which calculates the dsum
for each record in the debit table, although for some
reason, all OlderDebits values are null.

This is the SQL code which I can't get to work:
SELECT [Debits by Trx Date Copy].[Active or Inactive
Account], [Debits by Trx Date Copy].[Trx Date], [Debits by
Trx Date Copy].Age, [Debits by Trx Date Copy].Debit, DSum
([Debit],"Debits By Trx Date Copy","[Active or Inactive
Account]"=" & [Active or Inactive Account]" And [Age]>" &
[Age]") AS OlderDebits, [Total Credits By Account
Copy].TotalCredit
FROM [Debits by Trx Date Copy] LEFT JOIN [Total Credits By
Account Copy] ON [Debits by Trx Date Copy].[Active or
Inactive Account] = [Total Credits By Account Copy].
[Active or Inactive Account];

After I get the dsum calculation to work within Query 1,
I plan to run a 2nd query from query 1, since when i try
to refer to OlderDebits within query 1, it prompts me for
an OlderDebits parameter.

Can you please help me figure out what I'm doing wrong in
the above code?

Thanks,
Melody

 

[Graham Mandeno]

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

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

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

.

 

[Ted Allen]

Hi Melody,

I believe that the problem is just with your DSum
function, so I will focus on that.

Following is the function in your code:
DSum([Debit],"Debits By Trx Date Copy","[Active or
Inactive Account]"=" & [Active or Inactive Account]" And
[Age]>" & [Age]")

I'll offer the following potential corrections:

The first reference to [Debit] must be enclosed in
quotations.

I'm not sure, but you may need to enclose the table name
in brackets since it includes spaces (I don't think so,
but if all of the other recommendations don't fix the
function you could try it.

There is an extra quotation mark before the = sign

If the Acct No is an alpha, you need to enclose it in
single quotes by placing a single quote in the string
before and after the field.

The reference to the query records Account number must be
preceeded with the table name, because your query
contains two fields with the same name (one for each
table)

There is an & missing before the quote before the word AND

You have an extra quotation mark after the last Age field
reference.

Incorporating these notes, try the following to see if it
will work:

DSum("[Debit]","Debits By Trx Date Copy","[Active or
Inactive Account] = '" & [Debits by Trx Date Copy].
[Active or Inactive Account] & "' And [Age] > " & [Age])

If this does not work, I would try placing brackets
around the table name as follows:

DSum("[Debit]","[Debits By Trx Date Copy]","[Active or
Inactive Account] = '" & [Debits by Trx Date Copy].
[Active or Inactive Account] & "' And [Age] > " & [Age])

Regarding your concern about the problem with the
accounts being set equal to each other, I think it was
likely a syntax error in the criteria, rather than a
problem with using the accounts as criteria. Most likely
it was a result of the missing single quotes. It is easy
to make mistakes in the syntax for these criteria. The
way you have to think of it is that anything not in
quotes will get replaced with the field values, and then
everything will get pieced together. Once pieced
together, the string should result in a valid WHERE
condition, without the word WHERE. So, if you look at
just the criteria part of the expression:

"[Active or Inactive Account] = " & [Debits by Trx Date
Copy].[Active or Inactive Account] & " And [Age] > " &
[Age]

It will substitute the values for the fields not in
quotes. So, if you assume that [Debits by Trx Date Copy].
[Active or Inactive Account] for the current record in
the query = "A1234" and the Age = "59", the result of the
concatenation will be:

"[Active or Inactive Account] = 'A1234' And [Age] > 59"

So, the function would sum the "[Debit]" field (your
first function criteria), in the "Debits by Trx Date
Copy" table (second criteria), WHERE [Active or Inactive
Account] = 'A1234' And [Age] > 59

When you see the result of the criteria concatenation, I
think it is easier to see that the field names inside the
quotes in your criteria are referring to the table field
names, while the ones not in quotes are placeholders
referring to the value of that field for the current
record in the query. The function uses the resulting
expression once the query fields (not in quotes) have
been replaced with their values.

Note that the single quotes are used to designate string
values. Single quotes are used because double quotes
would mess up the string concatenation expression. #
signs are used to similarly denote date values (such as
#3/9/04#).

One other thing, I don't remember ever seeing whether you
had checked to see if you will have duplicate age values
for a given acct. If so, you may need to revise the
formula somewhat.

Hopefully this will help. I remember how much it helped
me when I finally got the hang of the domain functions.
They really open up a lot of opportunities.

Post back and let me know if you are able to get it
working.

-Ted Allen

-----Original Message-----
Ted,

BTW, as followup to my last post(pasted below), I believe
the error has something to do with setting the accounts
equal to each other in the dsum function, because when I
used only the dates as criteria I was able to generate
values but not when using the account criteria. The
accounts [Active or Inactive Account] are text strings. I
played around with the & and " characters but it did not
help. If you happen to know why this is not working,
please let me know.

Thanks,
Melody


Ted, I finally gotcha. I think the query method will work
for the results I need. I did as you recommended and
started off by creating a field which calculates the dsum
for each record in the debit table, although for some
reason, all OlderDebits values are null.

This is the SQL code which I can't get to work:
SELECT [Debits by Trx Date Copy].[Active or Inactive
Account], [Debits by Trx Date Copy].[Trx Date], [Debits by
Trx Date Copy].Age, [Debits by Trx Date Copy].Debit, DSum
([Debit],"Debits By Trx Date Copy","[Active or Inactive
Account]"=" & [Active or Inactive Account]" And [Age]>" &
[Age]") AS OlderDebits, [Total Credits By Account
Copy].TotalCredit
FROM [Debits by Trx Date Copy] LEFT JOIN [Total Credits By
Account Copy] ON [Debits by Trx Date Copy].[Active or
Inactive Account] = [Total Credits By Account Copy].
[Active or Inactive Account];

After I get the dsum calculation to work within Query 1,
I plan to run a 2nd query from query 1, since when i try
to refer to OlderDebits within query 1, it prompts me for
an OlderDebits parameter.

Can you please help me figure out what I'm doing wrong in
the above code?

Thanks,
Melody

.

 

[Melody]

Ted,

I took your advice and the query worked! =) (That syntax
sure does get tricky!) Anyway, thank you so much all your
help. I did notice two discrepancies when matching my
control totals however.. it had to deal with the fact that
two of my accounts had total credits which exceeded the
total debits for those accounts, so there were still some
credits that needed to be applied to debit line items that
didn't exist. Do you know if there's a way to state in my
iff statement to: input 0 if [CreditAmt]- [OlderDebits]>
[DebitAmt] unless it's the last line, then input
[CreditAmt] - [DebitAmt]? Actually I'm not concerned with
this because I can always just point this out manually but
I was just wondering if that was possible.

Anyway, thanks again for helping me out! I am still going
to try to use Graham's VBA method, I have to learn
somewhere I guess, so expect to still see me online!

Melody

-----Original Message-----
Hi Melody,

I believe that the problem is just with your DSum
function, so I will focus on that.

Following is the function in your code:
DSum([Debit],"Debits By Trx Date Copy","[Active or
Inactive Account]"=" & [Active or Inactive Account]" And
[Age]>" & [Age]")

I'll offer the following potential corrections:

The first reference to [Debit] must be enclosed in
quotations.

I'm not sure, but you may need to enclose the table name
in brackets since it includes spaces (I don't think so,
but if all of the other recommendations don't fix the
function you could try it.

There is an extra quotation mark before the = sign

If the Acct No is an alpha, you need to enclose it in
single quotes by placing a single quote in the string
before and after the field.

The reference to the query records Account number must be
preceeded with the table name, because your query
contains two fields with the same name (one for each
table)

There is an & missing before the quote before the word AND

You have an extra quotation mark after the last Age field
reference.

Incorporating these notes, try the following to see if it
will work:

DSum("[Debit]","Debits By Trx Date Copy","[Active or
Inactive Account] = '" & [Debits by Trx Date Copy].
[Active or Inactive Account] & "' And [Age] > " & [Age])

If this does not work, I would try placing brackets
around the table name as follows:

DSum("[Debit]","[Debits By Trx Date Copy]","[Active or
Inactive Account] = '" & [Debits by Trx Date Copy].
[Active or Inactive Account] & "' And [Age] > " & [Age])

Regarding your concern about the problem with the
accounts being set equal to each other, I think it was
likely a syntax error in the criteria, rather than a
problem with using the accounts as criteria. Most likely
it was a result of the missing single quotes. It is easy
to make mistakes in the syntax for these criteria. The
way you have to think of it is that anything not in
quotes will get replaced with the field values, and then
everything will get pieced together. Once pieced
together, the string should result in a valid WHERE
condition, without the word WHERE. So, if you look at
just the criteria part of the expression:

"[Active or Inactive Account] = " & [Debits by Trx Date
Copy].[Active or Inactive Account] & " And [Age] > " &
[Age]

It will substitute the values for the fields not in
quotes. So, if you assume that [Debits by Trx Date Copy].
[Active or Inactive Account] for the current record in
the query = "A1234" and the Age = "59", the result of the
concatenation will be:

"[Active or Inactive Account] = 'A1234' And [Age] > 59"

So, the function would sum the "[Debit]" field (your
first function criteria), in the "Debits by Trx Date
Copy" table (second criteria), WHERE [Active or Inactive
Account] = 'A1234' And [Age] > 59

When you see the result of the criteria concatenation, I
think it is easier to see that the field names inside the
quotes in your criteria are referring to the table field
names, while the ones not in quotes are placeholders
referring to the value of that field for the current
record in the query. The function uses the resulting
expression once the query fields (not in quotes) have
been replaced with their values.

Note that the single quotes are used to designate string
values. Single quotes are used because double quotes
would mess up the string concatenation expression. #
signs are used to similarly denote date values (such as
#3/9/04#).

One other thing, I don't remember ever seeing whether you
had checked to see if you will have duplicate age values
for a given acct. If so, you may need to revise the
formula somewhat.

Hopefully this will help. I remember how much it helped
me when I finally got the hang of the domain functions.
They really open up a lot of opportunities.

Post back and let me know if you are able to get it
working.

-Ted Allen

-----Original Message-----
Ted,

BTW, as followup to my last post(pasted below), I believe
the error has something to do with setting the accounts
equal to each other in the dsum function, because when I
used only the dates as criteria I was able to generate
values but not when using the account criteria. The
accounts [Active or Inactive Account] are text strings. I
played around with the & and " characters but it did not
help. If you happen to know why this is not working,
please let me know.

Thanks,
Melody


Ted, I finally gotcha. I think the query method will work
for the results I need. I did as you recommended and
started off by creating a field which calculates the dsum
for each record in the debit table, although for some
reason, all OlderDebits values are null.

This is the SQL code which I can't get to work:
SELECT [Debits by Trx Date Copy].[Active or Inactive
Account], [Debits by Trx Date Copy].[Trx Date], [Debits by
Trx Date Copy].Age, [Debits by Trx Date Copy].Debit, DSum
([Debit],"Debits By Trx Date Copy","[Active or Inactive
Account]"=" & [Active or Inactive Account]" And [Age]>" &
[Age]") AS OlderDebits, [Total Credits By Account
Copy].TotalCredit
FROM [Debits by Trx Date Copy] LEFT JOIN [Total Credits By
Account Copy] ON [Debits by Trx Date Copy].[Active or
Inactive Account] = [Total Credits By Account Copy].
[Active or Inactive Account];

After I get the dsum calculation to work within Query 1,
I plan to run a 2nd query from query 1, since when i try
to refer to OlderDebits within query 1, it prompts me for
an OlderDebits parameter.

Can you please help me figure out what I'm doing wrong in
the above code?

Thanks,
Melody

.

.

 

[Melody]

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

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


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



.

.

 

[Ted Allen]

Hi Melody,

Glad you got it to work. Regarding your question about
the if condition, you could use an iif() statement such
as the following

iif([Age] = DMax("[Age]",]","Debits By Trx Date
Copy","[Active or Inactive Account] = '" & [Debits by Trx
Date Copy].[Active or Inactive Account]),Formula for when
Age = Max, Formula for when Age <> Max)

to specify a different formula for when the age is the
last (Max) record.

I agree that it would be a good idea to learn how to do
this in code if you plan to work with databases much in
the future. Once you learn how to create recordsets and
work with them in code you can do just about anything
that you want to do with the data.

-Ted Allen

-----Original Message-----
Ted,

I took your advice and the query worked! =) (That syntax
sure does get tricky!) Anyway, thank you so much all your
help. I did notice two discrepancies when matching my
control totals however.. it had to deal with the fact that
two of my accounts had total credits which exceeded the
total debits for those accounts, so there were still some
credits that needed to be applied to debit line items that
didn't exist. Do you know if there's a way to state in my
iff statement to: input 0 if [CreditAmt]- [OlderDebits]>
[DebitAmt] unless it's the last line, then input
[CreditAmt] - [DebitAmt]? Actually I'm not concerned with
this because I can always just point this out manually but
I was just wondering if that was possible.

Anyway, thanks again for helping me out! I am still going
to try to use Graham's VBA method, I have to learn
somewhere I guess, so expect to still see me online!

Melody

-----Original Message-----
Hi Melody,

I believe that the problem is just with your DSum
function, so I will focus on that.

Following is the function in your code:
DSum([Debit],"Debits By Trx Date Copy","[Active or
Inactive Account]"=" & [Active or Inactive Account]" And
[Age]>" & [Age]")

I'll offer the following potential corrections:

The first reference to [Debit] must be enclosed in
quotations.

I'm not sure, but you may need to enclose the table name
in brackets since it includes spaces (I don't think so,
but if all of the other recommendations don't fix the
function you could try it.

There is an extra quotation mark before the = sign

If the Acct No is an alpha, you need to enclose it in
single quotes by placing a single quote in the string
before and after the field.

The reference to the query records Account number must be
preceeded with the table name, because your query
contains two fields with the same name (one for each
table)

There is an & missing before the quote before the word AND

You have an extra quotation mark after the last Age field
reference.

Incorporating these notes, try the following to see if it
will work:

DSum("[Debit]","Debits By Trx Date Copy","[Active or
Inactive Account] = '" & [Debits by Trx Date Copy].
[Active or Inactive Account] & "' And [Age] > " & [Age])

If this does not work, I would try placing brackets
around the table name as follows:

DSum("[Debit]","[Debits By Trx Date Copy]","[Active or
Inactive Account] = '" & [Debits by Trx Date Copy].
[Active or Inactive Account] & "' And [Age] > " & [Age])

Regarding your concern about the problem with the
accounts being set equal to each other, I think it was
likely a syntax error in the criteria, rather than a
problem with using the accounts as criteria. Most likely
it was a result of the missing single quotes. It is easy
to make mistakes in the syntax for these criteria. The
way you have to think of it is that anything not in
quotes will get replaced with the field values, and then
everything will get pieced together. Once pieced
together, the string should result in a valid WHERE
condition, without the word WHERE. So, if you look at
just the criteria part of the expression:

"[Active or Inactive Account] = " & [Debits by Trx Date
Copy].[Active or Inactive Account] & " And [Age] > " &
[Age]

It will substitute the values for the fields not in
quotes. So, if you assume that [Debits by Trx Date Copy].
[Active or Inactive Account] for the current record in
the query = "A1234" and the Age = "59", the result of the
concatenation will be:

"[Active or Inactive Account] = 'A1234' And [Age] > 59"

So, the function would sum the "[Debit]" field (your
first function criteria), in the "Debits by Trx Date
Copy" table (second criteria), WHERE [Active or Inactive
Account] = 'A1234' And [Age] > 59

When you see the result of the criteria concatenation, I
think it is easier to see that the field names inside the
quotes in your criteria are referring to the table field
names, while the ones not in quotes are placeholders
referring to the value of that field for the current
record in the query. The function uses the resulting
expression once the query fields (not in quotes) have
been replaced with their values.

Note that the single quotes are used to designate string
values. Single quotes are used because double quotes
would mess up the string concatenation expression. #
signs are used to similarly denote date values (such as
#3/9/04#).

One other thing, I don't remember ever seeing whether you
had checked to see if you will have duplicate age values
for a given acct. If so, you may need to revise the
formula somewhat.

Hopefully this will help. I remember how much it helped
me when I finally got the hang of the domain functions.
They really open up a lot of opportunities.

Post back and let me know if you are able to get it
working.

-Ted Allen

-----Original Message-----
Ted,

BTW, as followup to my last post(pasted below), I believe
the error has something to do with setting the accounts
equal to each other in the dsum function, because when I
used only the dates as criteria I was able to generate
values but not when using the account criteria. The
accounts [Active or Inactive Account] are text

strings.
I

played around with the & and " characters but it did not
help. If you happen to know why this is not working,
please let me know.

Thanks,
Melody


Ted, I finally gotcha. I think the query method will work
for the results I need. I did as you recommended and
started off by creating a field which calculates the dsum
for each record in the debit table, although for some
reason, all OlderDebits values are null.

This is the SQL code which I can't get to work:
SELECT [Debits by Trx Date Copy].[Active or Inactive
Account], [Debits by Trx Date Copy].[Trx Date],

[Debits
by

Trx Date Copy].Age, [Debits by Trx Date Copy].Debit, DSum
([Debit],"Debits By Trx Date Copy","[Active or Inactive
Account]"=" & [Active or Inactive Account]" And [Age]

"

&
[Age]") AS OlderDebits, [Total Credits By Account
Copy].TotalCredit
FROM [Debits by Trx Date Copy] LEFT JOIN [Total

Credits
By

Account Copy] ON [Debits by Trx Date Copy].[Active or
Inactive Account] = [Total Credits By Account Copy].
[Active or Inactive Account];

After I get the dsum calculation to work within Query 1,
I plan to run a 2nd query from query 1, since when i try
to refer to OlderDebits within query 1, it prompts me for
an OlderDebits parameter.

Can you please help me figure out what I'm doing wrong in
the above code?

Thanks,
Melody

.

.

.

 

[Graham Mandeno]

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

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

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


message
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



.

.