Not saying this is an elegant solution, but I finally got something working today that does this. (Which I needed, because I'm using linked Sharepoint Lists, and data integrity rules don't get enforced well on Sharepoint.)
WARNING: I haven't tested this yet for removing multiple duplicates, e.g. two different records that each have duplicates. I suspect it would completely delete one of those records, which is bad! It works fine when there's only one record that has duplicates.
Also note this explanation is for the benefit of weaker users of Access, like myself. I'm sure the pros will have many ways to improve this!
The basic idea is:
1) Create 1st query to find duplicates in my table
2) Create 2nd query to find the highest ID out of all those duplicates (i.e. 2nd query acts on the 1st query's results)
3) Create 3rd query to find all the rows from the 1st query that do NOT appear in the 2nd query
4) Create a query to delete, from the original table, all the results of the 3rd query.
So here is roughly how I did that:
1)
1st Query, to find duplicates in my table.
I used Query Wizard > Find Duplicates and went through the steps to create a query to find duplicates in my table (tbl) based on four fields (StudentNumber, CourseID, SemesterID, and YearID). By right-clicking on the query, you can switch to the SQL view, which looks something like this:
SELECT [tbl].*
FROM [tbl]
WHERE ((([tbl].StudentNumber)
In
(SELECT [StudentNumber]
FROM [tbl] As Tmp
GROUP BY [StudentNumber],[CourseID],[SemesterID],[YearID]
HAVING Count(*)>1
And [CourseID] = [tbl].[CourseID]
And [SemesterID] = [tbl].[SemesterID]
And [YearID] = [tbl].[YearID])));
2)
2nd Query, to find the highest ID out of all the duplicates in the list. NOTE: This is where I think bad things could happen if you have two or more records that have duplicates, because one of them will have a higher ID than the others, which means you'd be deleting all but one duplicate of that record, but every single instance of every other record that has a duplicate.
I used Query Wizard > Select Query, and then I moved the ID to the first field and, in the properties window, I set Top Values to 1 to only return the record with the biggest ID. (Fellow newbies, in the Query Wizard, when it prompts you to choose a table, look for the option to choose a query instead.)
SELECT TOP 1 [qry1].[ID], [qry1].(all the other fields were included here), ...
FROM [qry1];
3)
3rd Query, to find all the queries that appear in the 1st query but not the 2nd query.
I used Query Wizard > Find Unmatched, and went through the steps to check which records in query 1 do not appear in query 2 to get something like this:
SELECT [qry1].*
FROM [qry1] LEFT JOIN [qry2] ON [qry1].[ID] = [qry2].[ID]
WHERE ((([qry2].ID) Is Null));
4)
4th Query, to delete, from the original table, all the records in the 3rd query.
This is where things got painful for me for a while, because Access doesn't like to delete things when there's an inner join. The following work-around seemed to do what I needed it to. I used Query Design to create a blank query, then I right-clicked and switched to the SQL view, and I pasted in SQL of this form:
DELETE [tbl].*
FROM [tbl]
WHERE [tbl].[ID] IN (SELECT [qry3].[ID] FROM [qry3] );
NOTE: You can replace DELETE with SELECT in that code to check, non-destructively, whether your query is going to delete the correct records.
