Duplicate Payments using SQL

Question

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.

Answer 1

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;