Delete Query

[Cheshire101]

I need to be able to edit a table of data using specific criteria and I am unsure where to begin. I have created several delete queries but not like this.



Three fields: ID, Course, and EffDate

The data comes from a different system and can not be edited prior to the import into the database so I need to make updates.

A Delete Query that deletes records if the the ID and Course are duplicated and saves the record with the same information and the lastest EffDate.
Example:
ID Course EffDate
123 B0129 9/2/10
123 B0129 10/15/10
123 B0129 1/2/11

In this case the query would delete the first two records and save the third.
Thank you in advance for any help.

[TheShabz]

Any way you would be able to have an intermediate step? Create a temporary table that is essentially a duplicate of the info from the other system with an extra field that would be a yes/no field where you would run an update query to flag the record for each ID with the max date. Once you have that, you can run a delete query that deletes everything that's not yes/true/-1.

[Cheshire101]

How do I tag each record with the Yes/No? I a having problems identifying the records using a query or SQL code.
Below is a longer list of example data I have highlighted the rows that should be tagged (deleted).
IDCourseEffDate123B01291/1/2009123B01295/1/2010123B012911/1/2010123B01306/1/2008123B01313/1/2010123B01324/1/2011321B01235/23/2010321B01236/1/2004321B01295/10/2007321B01296/15/2011321B01317/1/2010456B01236/1/2008456B01293/1/2010456B01314/1/2011456B01317/1/2010

[Cheshire101]

OK - figured it out. I was making it way more complicated than it was.

Just run query using the Max function. For anyone who might need this

SELECT ID, Course, Max(EffDate) AS MaxDate
FROM tblTableName
Group By ID, Course