Hogpen,

Next time it would be helpful to present the data like this, then we could easily duplicate the data and write the queries:

CREATE TABLE Accounting

(ProdDate DateTime,

AcctDate DateTime,

MtrNo int,

MtrSfx char(1),

TrnxID int,

RecCode char (2)

)

insert into Accounting

select '2/1/2011','8/1/2011','365','A','8301','RR' union all

select '2/1/2011','2/1/2013','365','A','8301','RR' union all

select '2/1/2011','2/1/2011','365','A','8301','OR' union all

select '3/1/2011','9/1/2011','365','A','8302','RR' union all

select '3/1/2011','3/1/2013','365','A','8302','RR' union all

select '3/1/2011','3/1/2011','365','A','8302','OR'

Here is the delete statement that I've come up with:

DELETE Accounting

FROM Accounting a,

(SELECT MtrNo, MtrSfx, TrnxID, RecCode, MAX(AcctDate) AcctDate

FROM Accounting

WHERE RecCode = 'RR'

GROUP BY MtrNo, MtrSfx, TrnxID, RecCode

HAVING COUNT(TrnxID) > 1) b

where a.MtrNo = b.MtrNo and a.MtrSfx = b.MtrSfx

and a.TrnxID = b.TrnxID and a.RecCode = b.RecCode

and a.AcctDate = b.AcctDate

You need to group the data by all the primary key fields and then join that data on the original table and select. I find it is always helpful to write my code as a delete and just before I delete it, run it as a select to make sure I have the right code:

select a.*

FROM Accounting a,

(SELECT MtrNo, MtrSfx, TrnxID, RecCode, MAX(AcctDate) AcctDate

FROM Accounting

WHERE RecCode = 'RR'

GROUP BY MtrNo, MtrSfx, TrnxID, RecCode

HAVING COUNT(TrnxID) > 1) b

where a.MtrNo = b.MtrNo and a.MtrSfx = b.MtrSfx

and a.TrnxID = b.TrnxID and a.RecCode = b.RecCode

and a.AcctDate = b.AcctDate