# Duplicate Payments using SQL

Working on identifying duplicate vendor payments using SQL in MS Access. The business logic uses four column; Vendor #, Invoice #, Invoice Date and Invoice Amount. There are 5 core reviews, if all are the Exact matches (EEEE) or if at least one field is different (DEEE,EDEE,EEDE,EEED). I have it working for all 5, however its slow and I would like to improve on the code itself. I put an example of EEEE and DEEE below. Thoughts on a better way to write it?

Table SCHEMA:

• ID - AutoNumber

• VendorCol - Short Text

• VendorNameCol - Short Text
• InvoiceNoCol - Short Text
• InvAmountCol - Number
• InvoiceDateCol - Date/Time

# EEEE:

SELECT *
FROM [Payment History] AS PH1
WHERE Exists
(Select *
From [Payment History] AS PH2
Where PH2.[ID] <>PH1.[ID]
AND PH2.[VendorCol] = PH1.[VendorCol]
AND PH2.[InvoiceNoCol] = PH1.[InvoiceNoCol]
AND PH2.[InvoiceDateCol] = PH1.[InvoiceDateCol]
AND PH2.[InvAmountCol] = PH1.[InvAmountCol])
ORDER BY [InvAmountCol] DESC , [InvoiceDateCol] DESC;


Example of desired result: EEEE Example

# DEEE: Different vendor # but all else equal

SELECT *
FROM [Payment History] AS PH1
WHERE Exists
(Select *
From [Payment History] AS PH2
Where PH2.[ID] <>PH1.[ID]
AND PH2.[VendorCol] <> PH1.[VendorCol]
AND PH2.[InvoiceNoCol] = PH1.[InvoiceNoCol]
AND PH2.[InvoiceDateCol] = PH1.[InvoiceDateCol]
AND PH2.[InvAmountCol] = PH1.[InvAmountCol])
ORDER BY [InvAmountCol] DESC , [InvoiceDateCol] DESC;


Example of desired result: DEEE Example

I can post the other ones, but figured this would get most there.

You are needlessly complicating this a little, checking a single table for multiple records can be done the same way as checking multiple tables for the same record. You want to use a join.

You also use PH1 and PH2 as aliases, but what do they mean. Your aliases should be meaningful. I replaced PH1 with base, as it's the base table of our search, and PH2 with duplicate, as it contains the duplicate records.

Last but by no means least, you should avoid using SELECT * in SQL in general. If the schema of the table changes then the query will happy continue to run, even though the data may now be incorrect or irrelevant. SELECT * also tends to run slower than typing out the column names, although I am not 100% sure this is also the case in Access SQL.

Here is your first query after I finished tinkering:

SELECT
base.[VendorNameCol] AS [Vendor Name],
base.[InvoiceNoCol] AS [Invoice #],
base.[InvoiceDateCol] AS [Invoice Date]
base.[InvAmountCol] AS [Invoice Amount]
FROM [Payment History] AS base
INNER JOIN [Payment History] AS duplicate
ON base.[ID] <> duplicate.[ID]
AND base.[VendorCol] = duplicate.[VendorCol]
AND base.[InvoiceNoCol] = duplicate.[InvoiceNoCol]
AND base.[InvoiceDateCol] = duplicate.[InvoiceDateCol]
AND base.[InvAmountCol] = duplicate.[InvAmountCol])
ORDER BY
[InvAmountCol] DESC,
[InvoiceDateCol] DESC;

• That did it, and its way faster/can process many more records without timing out! – Caleb Ford Nov 13 '17 at 17:01
• That did it, and its way faster/can process many more records without timing out! Thank you very much, I learned a lot from comment and re-write of the code. - Is what I meant to send before I accidentally hit enter. – Caleb Ford Nov 13 '17 at 17:23