Deleting duplicate records from an append query when the key is Autonumber

[AndrewG]

I have a table with the following fields - Appeal #(Auto Number), Student ID, Class. I want to append records from an identical table and then delete the duplicate records (or not append the duplicate records in the first place).

The trouble is the only single unique key is the Auto Number field.
The same student appears in the table many times and the same class appears many times, but their combination is unique.

This makes it impossible to remove the duplicate queries by copying the structure of the table and then appending values.

Is there a way to do this or have I just designed the table badly?

 

[dcx693]

Sounds to me like the table should be set up with a compound primary key using the combination of student ID and class. I don't think you need that autonumber field if the table is set up that way.

I think then you should have no problem running your append query.

 

[neileg]

If you try to append records that would create a duplicate PK, Access will refuse to do this. Isn't this exactly what you want to achieve?

However, I don't understand how you can have two 'identical' tables that have an autonumber as PK and that are synchronised on the autonumber. I would have thought that you are going to get duplicate appeal numbers that are not duplicated records.

I'm inclined to agree with dcx693 that the combination of student and class would make a better key. If you treat both tables the same way, your append will reject the key duplicates just as I suggested above.

Sorry if I've missed the plot!