Duplicate record selection

[Guest]

I'm developing an application to identify duplicate payments from a table
which contains a record for each payment transaction (both the debit and the
credit).

Key fields in table: 'invoice number', 'vendor', 'invoice date', 'amount',
'indicator' among others.

I used standard 'find dup' query to select duplicate 'amount', 'vendor', and
'inoice date'. Now I need to narrow the dataset further to exclude some
records that offset (debit and credit) each other. I have created a
calculated field ("invoice amount") to reflect product of 'amount' and
'indicator', showed this field in query results. If for each matched set
with identical 'amount', 'vendor' and 'invoice date', When there are only two
records and one is negative (in the 'amount' field) and the other is positive
(in the 'amount' field'), I want these excluded from my results. If there
are 3 records and one of them offsets another, I want to exclude the two that
offset but have the 3rd remain in the dataset. If there are 5 otherwise
matching records and two of them offset, I want them excluded but the other 3
included. I hope you get the pattern.

I do not understand how to write code. So please respond with an elementary
answer.

 

[Guest]

There is no elementary answer because its a somewhat complex problem.
What happens if the records don't match e.g. What if there is a debit for
$50 and two credits for $20 and $30, is this regarded as a match? What if
there are debits for $25 and $25 and credits for $20 and $30, is this a
match? If so, it means a much more complex matching algorithm is needed.
How many records are in this table?
There may be a way to do this in a single SQL query but it would be a pretty
complex query.

Dorian