Store Transaction Logs

[mcgettge]

I need to delete duplicate transactions from a 3.5 million row table. The transactions appear with a different transaction number, but have the same store #, account #, transaction $ amount, transaction type, date, and are within 60 seconds of the original. I don't know where to go. I know that in Excel I could write multiple IF statements, but this data is well beyond the limits of Excel.

 

[PHV]

Provided [transaction number] is the PrimaryKey, you may try something like this (SQL code):
DELETE FROM tblTransactions
WHERE [transaction number] Not In (SELECT Min([transaction number]) FROM tblTransactions GROUP BY [store #], [account #], [transaction $ amount], [transaction type], Format([DateTime field],'yyyymmddhhnn'))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886