Aging report problem.

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

Re: Aging report problem.

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" wrote in message
news:9qaUi.15423$uE4.13993@trnddc07...
> 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
>

Re: Aging report problem.

On Oct 25, 7:21 pm, "Ron" wrote:
> 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

Re: Aging report problem.

wrote in message
news:1194551533.345052.92390@s15g2000prm.googlegroups.com...
> On Oct 25, 7:21 pm, "Ron" wrote:
>> 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

Re: Aging report problem.

On Nov 8, 6:56 pm, "Ron" wrote:
> wrote in message
>
> news:1194551533.345052.92390@s15g2000prm.googlegroups.com...
>
>
>
> > On Oct 25, 7:21 pm, "Ron" wrote:
> >> 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 "transferred" to other invoices needing
payment.

Enough mulling for me... Hope this helps!