Duplicate issues across multiple columns

[inspirz]

Hi,

Ok here's the scenario:
I have a database:

Column A: is unique TRANSACTION NUMBER
Coumn B: is VALUE ($)
Column C: is a the Payment ID (it can be a DEALER PAYMENT, or a CHARGE-BACK
or SYSTEM REVERSAL, et cetera)
Column D: Dealer ID Number

Ok here's the problem and I hope I explain this clearly:
Our company is paying duplicate dealer payments and/or chargebacks and I
need to distinguish which are the real dupes versus legitimate dupes.

so for example - in the scenario below - TRANSACTION NUMBER 12345 had 1
duplicate payment because scenario A1 and A2 cancel each other out ($40 -$40)
therefore you have 2 payments of $40 when a dealer can only be paid 1 time.
How can I create a query to show me only legitimate dupes?

Column A1: 12345
Column B1: $40
Column C1: " Dealer Commission Payment"
Column D1: 5555

Column A2: 12345
Column B2: (-$40)
Column C2: "Dealer Commission Chargeback"
Column D2: 5555

Column A3: 12345
Column B3: $40
Column C3: "Dealer Commission Payment"
Column D3: 5555

Column A4: 12345
Column B4: $40
Column C4: "Dealer Commission Payment"
Column D4: 5555

 

[Fred]

I noticed that nobody answered

I think that you have some several structure/data definition problems which
need to be worked out That (combine with, I think, you being a little new to
Access) makes it too much for one post but here goes.

First I mention a few terminology issues not to be a pain but because I
think they will be helpful.

(shorten my explanatory names as needed)

What you described is a table, not a database. In Access terminology , a
database is a container that holds tables, forms, queries and lots of other
stuff.

Anr your "columns" are fields. Your shoudl rename them using more
descriptive names. I'll call your "Transaction Number"
"AutoSaleTransactionNumber". And, note that while it may be "unique" in
some other respect, it is NOT unique with respect to your described
table.....it repeats.

Next, I think that you are saying (in your example) that the first
chargeback cancelled the first payment, that the second payment was then
fine, and that the third payment (fourth record) constituted / created a
"real duplication".

Your table is a CommissionTransaction table, which is what you need.
However, your definition of "Transaction" is SOMETHING ELSE . . .. it is the
auto sale (or whatever it was) that these payments relate to. Ill call that
your AutoSaleTransaction.

Your CommissionTransaction needs to have a Primary Key (PK) unique
identifier. Add an Autonumber PK "CommissionTransactionID" to that table.


Next, while you are thinking that your second record (the charge back)
cancelled the first record (the first payment) and not the 3rd or 4th
records (payments) that "pairing" is not documented anywhere, and the
computer can't read your mind.

I think that the above will provide you a better foundation. Built upon
that could be a more elegant solution, but here's a simple /low tech one.

Add a "CancelledOut" field ....whe you issue a chargeback, put a "Y" in this
field for both the payment and chargeback record of that "pair". Next is
a "query of a query" which simpler that one fancy query. Make a query that
returns records where "CancelledOut" is null. Use that as a data source for
a "find duplicates" query where you look for duplicates on the combinaiton of
two fields:

AutoSaleTransactionNumber and
Column B1 / "Value"

Hope that helps a little