Delete multiple records from table.

[exRP12Nuke]

Hello,

I have an append query that runs each time my VBA code is ran. This append query takes values from one table and stores it in another table for a historical record. However, if the values that it pulls have not changed from the last time the program ran, it creates a duplicate entry within the history table.

I found a small bit of code in the FAQ discussion area here, and it is a good start, but it does not return the values that I need it to.

DELETE [LastCOHrs] FROM [tblCOHist] WHERE [ID] Not In (SELECT Max([ID]) FROM [tblCOHist] GROUP BY [LastCOHrs]);

It does delete ALL duplicate records from the LastCOHrs column in the table. The fields that are in the tblCOHist table are SN, CompCOde, ModCode, and LastCOHrs. It is possible to have multiple entries for SN, CompCode, and ModCode, but I do not want multiple entries for the LastCOHrs field.

I have attached a sample spreadsheet of what the history table looks like, any help would be greatly appreciated.

SN	CompCode2	 ModCode	LastCOHrs	
A	        1		       10	
A	        2		        5	
A	        3		        7	
A	        4	 	        8	
A	        5	      L	       45	
A	        5	      R	       52	
A	        1	 	       10 	Duplicate entry - this one needs to be deleted
A	        1 	               500	This one is ok

Thanks!