Aging report problem.

[Ron]

Hi All,

I've got a client/transaction type of database where tblClient is linked to
tblTransactions via ClientID. The client table contains all clients from
day one. The transaction table contains all transactions from day one
(includes any purchases, labor charges, payments, adjustments) The aging
report I developed works fine, except...

For any client that owes money, it lists ALL the transactions for that
client. Or, it lists all the transactions for a client where the dates of
entry are between 2 dates I have the operator input. But I really want it
more open--I want it to list only transactions that haven't been
paid/adjusted yet regardless of some arbitrary date the operator can input.
So, I want a report go through the transactions and find the first
transaction where the balance is not equal zero and start there on the aging
detail.

Example would be where Jane Smith has had 22 transactions but the 1st 18
equal 0 and just happen to have happened prior to March 2007, so she's got
just 4 that she still owes money on, and that's all I want to show on the
aging report--transactions since March 2007. Then John James has had 6
transactions and all are still owed and they date back to June of 2006--all
should appear on the aging. And Bill Adams has 11 transactions, the 1st 9
of which equals 0 and the last 2, from Sept 2007 are still owed and those
should appear, the first 9 shouldn't. Follow?

How can I pull out only the "unresolved " transactions?

TIA,
ron

[Bob Darlington]

Ron,
Are you basing your report on a query?
Have you applied a criteria like "Balance <0" to that query?
Perhaps you could post the SQL.

--
Bob Darlington
Brisbane
"Ron" <ro************ ********@verizo n.netwrote in message
news:9qaUi.1542 3$uE4.13993@trn ddc07...

Hi All,

I've got a client/transaction type of database where tblClient is linked
to tblTransactions via ClientID. The client table contains all clients
from day one. The transaction table contains all transactions from day
one (includes any purchases, labor charges, payments, adjustments) The
aging report I developed works fine, except...

For any client that owes money, it lists ALL the transactions for that
client. Or, it lists all the transactions for a client where the dates of
entry are between 2 dates I have the operator input. But I really want it
more open--I want it to list only transactions that haven't been
paid/adjusted yet regardless of some arbitrary date the operator can
input. So, I want a report go through the transactions and find the first
transaction where the balance is not equal zero and start there on the
aging detail.

Example would be where Jane Smith has had 22 transactions but the 1st 18
equal 0 and just happen to have happened prior to March 2007, so she's got
just 4 that she still owes money on, and that's all I want to show on the
aging report--transactions since March 2007. Then John James has had 6
transactions and all are still owed and they date back to June of
2006--all should appear on the aging. And Bill Adams has 11 transactions,
the 1st 9 of which equals 0 and the last 2, from Sept 2007 are still owed
and those should appear, the first 9 shouldn't. Follow?

How can I pull out only the "unresolved " transactions?

TIA,
ron

[sergia.dupoux]

On Oct 25, 7:21 pm, "Ron" <ronSPAMBLOCKIN Gwest...@verizo n.netwrote:

Hi All,

I've got a client/transaction type of database where tblClient is linked to
tblTransactions via ClientID. The client table contains all clients from
day one. The transaction table contains all transactions from day one
(includes any purchases, labor charges, payments, adjustments) The aging
report I developed works fine, except...

For any client that owes money, it lists ALL the transactions for that
client. Or, it lists all the transactions for a client where the dates of
entry are between 2 dates I have the operator input. But I really want it
more open--I want it to list only transactions that haven't been
paid/adjusted yet regardless of some arbitrary date the operator can input.
So, I want a report go through the transactions and find the first
transaction where the balance is not equal zero and start there on the aging
detail.

Example would be where Jane Smith has had 22 transactions but the 1st 18
equal 0 and just happen to have happened prior to March 2007, so she's got
just 4 that she still owes money on, and that's all I want to show on the
aging report--transactions since March 2007. Then John James has had 6
transactions and all are still owed and they date back to June of 2006--all
should appear on the aging. And Bill Adams has 11 transactions, the 1st 9
of which equals 0 and the last 2, from Sept 2007 are still owed and those
should appear, the first 9 shouldn't. Follow?

How can I pull out only the "unresolved " transactions?

TIA,
ron

did you ever get an answer to this? did it work? I have a similar
problem

[Ron]

<se***********@ gmail.comwrote in message
news:11******** *************@s 15g2000prm.goog legroups.com...

On Oct 25, 7:21 pm, "Ron" <ronSPAMBLOCKIN Gwest...@verizo n.netwrote:

>Hi All,

I've got a client/transaction type of database where tblClient is linked
to
tblTransaction s via ClientID. The client table contains all clients from
day one. The transaction table contains all transactions from day one
(includes any purchases, labor charges, payments, adjustments) The aging
report I developed works fine, except...

For any client that owes money, it lists ALL the transactions for that
client. Or, it lists all the transactions for a client where the dates
of
entry are between 2 dates I have the operator input. But I really want
it
more open--I want it to list only transactions that haven't been
paid/adjusted yet regardless of some arbitrary date the operator can
input.
So, I want a report go through the transactions and find the first
transaction where the balance is not equal zero and start there on the
aging
detail.

Example would be where Jane Smith has had 22 transactions but the 1st 18
equal 0 and just happen to have happened prior to March 2007, so she's
got
just 4 that she still owes money on, and that's all I want to show on the
aging report--transactions since March 2007. Then John James has had 6
transactions and all are still owed and they date back to June of
2006--all
should appear on the aging. And Bill Adams has 11 transactions, the 1st
9
of which equals 0 and the last 2, from Sept 2007 are still owed and those
should appear, the first 9 shouldn't. Follow?

How can I pull out only the "unresolved " transactions?

TIA,
ron

did you ever get an answer to this? did it work? I have a similar
problem

I received one reply:

Ron,
Are you basing your report on a query?
Have you applied a criteria like "Balance <0" to that query?
Perhaps you could post the SQL.

--
Bob Darlington
Brisbane
But... haven't had a chance to even look at it really. I don't have a field
for "balance" as it's a running sum, and not saved anywhere. I still don't
know how I'd find the first unpaid charge and start the display from there.
I just went on to more pressing matters and figured I'd revisit when I have
time. Untill then, I'm mulling it over--that's sometimes when I do my best
work. ::grin::

Good luck to you in finding a solution though. I'll post mine, when/if I
come up with it...please do the same.

Thanks,
ron

[Smartin]

On Nov 8, 6:56 pm, "Ron" <ronSPAMBLOCKIN Gwest...@verizo n.netwrote:

<sergia.dup...@ gmail.comwrote in message

news:11******** *************@s 15g2000prm.goog legroups.com...

On Oct 25, 7:21 pm, "Ron" <ronSPAMBLOCKIN Gwest...@verizo n.netwrote:

Hi All,

I've got a client/transaction type of database where tblClient is linked
to
tblTransactions via ClientID. The client table contains all clients from
day one. The transaction table contains all transactions from day one
(includes any purchases, labor charges, payments, adjustments) The aging
report I developed works fine, except...

For any client that owes money, it lists ALL the transactions for that
client. Or, it lists all the transactions for a client where the dates
of
entry are between 2 dates I have the operator input. But I really want
it
more open--I want it to list only transactions that haven't been
paid/adjusted yet regardless of some arbitrary date the operator can
input.
So, I want a report go through the transactions and find the first
transaction where the balance is not equal zero and start there on the
aging
detail.

Example would be where Jane Smith has had 22 transactions but the 1st 18
equal 0 and just happen to have happened prior to March 2007, so she's
got
just 4 that she still owes money on, and that's all I want to show on the
aging report--transactions since March 2007. Then John James has had 6
transactions and all are still owed and they date back to June of
2006--all
should appear on the aging. And Bill Adams has 11 transactions, the 1st
9
of which equals 0 and the last 2, from Sept 2007 are still owed and those
should appear, the first 9 shouldn't. Follow?

How can I pull out only the "unresolved " transactions?

TIA,
ron

did you ever get an answer to this? did it work? I have a similar
problem

I received one reply:

Ron,
Are you basing your report on a query?
Have you applied a criteria like "Balance <0" to that query?
Perhaps you could post the SQL.

--
Bob Darlington
Brisbane

But... haven't had a chance to even look at it really. I don't have a field
for "balance" as it's a running sum, and not saved anywhere. I still don't
know how I'd find the first unpaid charge and start the display from there.
I just went on to more pressing matters and figured I'd revisit when I have
time. Untill then, I'm mulling it over--that's sometimes when I do my best
work. ::grin::

Good luck to you in finding a solution though. I'll post mine, when/if I
come up with it...please do the same.

Thanks,
ron

Just some thoughts to add to your mulling...

In real life, do charges group into what might be called an invoice?
If so, it would seem logical to represent an invoice in the database
with a table that unites clients and transactions. Then your
transactions can be summed up at the invoice level to reveal invoices
with a non-zero balance.

Alternatively, I'm thinking there probably is some way to sequentially
analyze client transactions and, upon finding a running balance of
zero, mark said transactions as "resolved". Then you can exclude
resolved transaction in further analysis. VBA might be the path of
least resistance here since you can pluck records one at a time and
keep tabs on the sums. The more I think about it though, trying to
mark individual transactions as resolved might get you into trouble
if, say, a dyslexic client pays $98 on an $89 balance. How will you
propagate the credit forward to future transactions?

Returning to the invoice concept, credit balances can be adjusted on
individual invoices and "transferre d" to other invoices needing
payment.

Enough mulling for me... Hope this helps!