Matching records in a table

[Paul Fenton]

I have a table of client job records which lists every job for each
client and the balance due on that job. There can be 1 or more record
for each client and I use this table to generate some reports on
receivables.

Some clients have records which net to zero because refunds/rebates
are entered as a negative balance due. (Don't ask why the client
wants to do it this way!) I want to eliminate those records for the
client which net to zero.

Example

Client BalDue DateDue
Jim $100.00 1/1/04
Jim $100.00 1/31/04
Jim $200.00 2/2/04
Jim ($100.00) 1/5/04
Bob etc.

In that example, I would want to delete one of the positive $100
records and the negative $100.00 record. If Jim had more than 1
record for a positive $100.00, I want to delete the oldest one.

I need some help as to how to go through the table and match things
up.


Paul Fenton
(e-mail address removed)

 

[Nikos Yannacopoulos]

Paul,

For a change, my reply is a business process one much more than an Access
one. In my humble opinion, deleting client line items is a bad idea, in that
you lose history, and when a dispute arises with a client on their open
balance, you will have a hard time reconciling. Therefore, I would suggest a
different approach:
Add another field (yes/no) to your job table, and call it, say, Closed,
default = no. The idea is that all line items are open until paid or
balanced off with a credit entry, as in your example.
Use a client form with a job subform for entry / maintenance (possibly
showing open items only), and when you have a credit enrty click both that
one and the debit it balances off Closed > Yes.
To calculate your open balance, sum amounts where Closed = No
On a receivables report, you can select no to show line items with Closed =
Yes.
This may seem a bit more manual process than what you had in mind, but it's
a standard credit control practice... I suppose threre must be a reason.

You could make it a bit more elaborate by using one more field to store
payment date or credit item reference for each item, if you want. You could
even go for partial item settlement, but this gets more complicated and goes
deeper into credit control; don't know if you'd bother.

HTH,
Nikos

 

[Paul Fenton]

Nikos, thank you for the reply. I guess I should have said that the
table I was referring to is a temporary table that I create from a
query of several underlying tables. No original data gets deleted,
but I need to block those matching records in the temp table from
appearing on the reports.

The idea of a yes/no field is interesting and I will explore that.
There is a point in the process where they're updating the job records
where I could set such a flag and then adjust my query to ignore the
flagged records.

Thanks again...

Paul Fenton