Dupes in one column in table - is this possible??

[Guest]

I have a table which is created daily from external sources. It has fields
Ckey, Mkey, Description, Date and numerous description fleids which are
different for each record.

Mkey is a unique reference for each record genrated from an autonumber.

My problem is duplicates in the Ckey field - i want to eliminate them, using
only the record that has the lowest Mkey number!

for example i may have
Ckey Mkey (desc fields....)
1 50
2 51
2 52
2 53
3 54

and i want to eliminate the records with mkey 52, 53 from the table. This is
somethiing i may need to do regulary. I;m thinking about using the find
dupes query then a delete query, but i dont want to delete all the dupes - i
want to keep the one that has the lowest mkey, so that eventually each ckey
occurence is unique!
Any ideas? i'm pretty new to access, and in setting up the table have
currently defines mkey as my primary key.

I need the resultant query/amended table to return all the original fields...
Please help!

 

[Guest]

You can create a query that will return the records you want to display

SELECT TableName.Ckey, Min(TableName.Mkey) AS MinMkey
FROM TableName
GROUP BY TableName.Ckey
=======================================
So you don't have to delete the records, the query will always return the
right records, without the need to run a delete query every time

 

[sebt]

Hi

If you want, you can delete the unwanted rows by LEFT JOINing the table
to the query Ofer posted (which selects the rows you do want). Save
the query as e.g. QryRowsYouWant

Delete Tablename.* FROM
TableName LEFT JOIN QryRowsYouWant ON ON
Tablename.Ckey=QryRowsYouWant.Ckey AND
TableName.mkey=QryRowsYouWant.mkey
WHERE QryRowsYouWant.CKey IS NULL

Or you can select all the columns from the original table by joining
Ofer's query to the table:

SELECT tbl.* FROM
TableName AS tbl
INNER JOIN
QryRowsYouWant
ON
Tbl.Ckey=QryRowsYouWant.Ckey AND Tbl.mkey=QryRowsYouWant.mkey

cheers


Seb

 

[Guest]

great look ideal -

i've put in the sql for a new delete query as below but am getting a syntax
error - cant for the life of me see where? - this is one of my fist forays
into SQL so i know it'll be obviuos....

DELETE tbl_matched.* FROM
tbl_matched LEFT JOIN PB_DupeFinder ON ON
tbl_matched.Ckey = PB_DupeFinder.Ckey
AND
tbl_matched.Mkey = PB_DupeFinder.Mkey
WHERE PB_DupeFinder.Ckey IS NULL
;

 

[Douglas J Steele]

Assuming that's a copy-and-past of the actual query, you've got the word ON
twice in a row.

 

[sebt]

So he has - only because it was there twice in the pseudo-SQL I posted!
My mistake...

Seb