Programatically deleting records for a specific criteria (field value)

[VBAguy22]

Hello
I have a table that archives transactions for customers. It only stores the latest transaction.
The problem is, the person before me didn't delete the old transactions but just kept adding them.
The table is called "Trans". How do I programatically delete ALL the previous tranasctions for a customer (by "CustomerNumber" field...each customer has a unique number) and add the latest transaction record?

 

[BNPMike]

Here is a correlated subquey from Steve101. It identifies the most recent record for each client. Alter this so it picks all the records that aren't the latest record and use that as the basis for your delete query.

SELECT *
FROM tblClient C
WHERE C.PaymentDate = (SELECT MAX(PaymentDate)
FROM tblClient C2
WHERE C2.ClientId = C.ClientId);

 

[VBAguy22]

I need to earse all the records for that one employee though

 

[BNPMike]

What I was suggesting is you identify all the records that aren't the latest and delete them. If you turn my SQL into a delete, isn't that OK?