deleting duplicate records

[chegra26]

I have two records in access where I need to delete duplicate fields from one table
Table 1 has 1645 records
Table 2 has 889 records

I need to delete the records in Table 1 that have the same information as table two.
The field is called Student ID so I need to delete the student ID in table 1 that contains the same student id as in table 2.

I need help with the delete query.

Thanks

 

[mole999]

for the future you need to make sure your key does not allow duplicates, i'm not sure how to deliberately find the duplicate lines

 

[chegra26]

Its from two different tables. I'm trying to compare the two tables and delete out the duplicates in one of the tables.

 

[Micron]

If you are sure student id is a unique value in each table, AND no id is in both tables but belonging to different students, you should be able to join both tables on student id and set the query type to be a delete query. In query design, choose the table and id field from the drop downs that you want to delete from. In the 'delete where' row, put something like tblA.ID = tblB.ID. I doubt you have set any 'cascading deletes' in table relationships for either of these tables, but if you have, you could end up deleting from other tables. I'd check the relationships window before doing this.

I think that would do the trick. NOTE: MAKE COPIES OF THESE TWO TABLES BEFORE ATTEMPTING THIS TYPE OF EXERCISE IN CASE IT GOES WRONG.
I doubt you have set any 'cascading deletes' in table relationships for either of these tables, but if you have, you could end up deleting from other tables. I'd check the relationships window before doing this. Maybe it would be better if you copied the entire database with the tables instead of just the tables.

 

[alansidman]

Look at this video. I think this may be what you are looking for

Macromedia Flash (SWF) Movie Created by Camtasia Studio 2