2
\$\begingroup\$

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.

\$\endgroup\$
1
\$\begingroup\$

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;
\$\endgroup\$
  • 1
    \$\begingroup\$ That did it, and its way faster/can process many more records without timing out! \$\endgroup\$ – Caleb Ford Nov 13 '17 at 17:01
  • 1
    \$\begingroup\$ 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. \$\endgroup\$ – Caleb Ford Nov 13 '17 at 17:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.