Access 2010 SQL - Show Duplicate Records in Order for eventual Deletion (pure SQL Solution pls)

Question

I have the following table named 'flt'

You can see the duplicates are identifed by 3 columns only (flight, fltno, stad)... I don't care about what is in col1 and col2.. But I should be able to show it in the query.

So.. you can see ids 8, 3 and 10 are duplicates.

I want to write a pure SQL query... that can do the following:

1) the duplicate count column.. which basically counts how many records are there that matches the flight, fltno, stad of the currently selected row.

2) the "duplicate rank" column which orders the duplicates.. 1 means first record, 2 means this is the 2nd record and 3 means this is the 3rd record. You can see ba 104 has 2 records in total... and it is ranked 1 and 2.

3) from the resulting (possibly editable) query.. I should be able to filter out (using where) all the duplicate ranks that are > 1... then able to delete those records. So.. id 8, 3 and 10 are > 1.. and I should be able to delete them with in this query... by clicking on the row and delete key.

If the condition 3 is not entirely achievable.. please give me the best way possible. Thanks.

Answer 1

This SQL will get you the results as per your question, however it won't work as part of a DELETE query, I suggest SELECTING from this query into a temporary table and then running a DELETE query from that : )

SELECT A.id, A.flight, A.fltno, A.stad, A.col1, A.col2, B.concount AS [duplicate count], (SELECT Count(C.id) FROM tblfit As C WHERE C.flight&C.fltno&C.stad=A.concat AND C.id <= A.id) AS [duplicate rank]
FROM (SELECT tblfit.*, [flight] & [fltno] & [stad] AS concat
FROM tblfit) AS A,
(SELECT [flight] & [fltno] & [stad] AS concat, Count([concat]) AS concount
FROM tblfit
GROUP BY [flight] & [fltno] & [stad]) AS B
WHERE A.concat = B.concat;