deleting records in access

[Sparky]

hi there,

i have 2 tables which i am now trying to merge together and it wont let me delete the duplicate records. I have a unique number on each record in one table and the same unique number exists in the second table (not autonumber though). I check to see which ones are duplicated and then if certain conditions are met, i need to delete from the second table, but access says that i cant delete as it is read only - why? This is using a delete record query.

None of the files are read only and i can delete the records in the normal table view, but there are about 10,000.

If you have any ideas, please let me know,
Cheers,
Sparky

[Ecniv]

Its because of the join.

I've had problems deleting, and one way around was the link fields both have to be the primary key.

Otherwise you need to (put all the duplicated IDs into another table and) loop through and delete each occurance of the duplication in teh second table. Or flag them in a field then delete using that field.

Regards

Vince

[PilgrimPete]

DELETE FROM Table2
WHERE UniqueID IN 
   (SELECT UniqueID
    FROM Table1
    WHERE YourSpecialConditionsAreMet)

This obviously won't work as it is - you'll need to sort out your WHERE clause, but it should give you a start. It probably won't be very quick for 10 000 records..!

Also UNION queries remove duplicates, but you won't easily be able to meet your other conditions...