How to get rid of duplicate records with unique identifiers in MS Access 2003?
I am working on an MS Access database with many duplicate records. The problem is that there is a table of students, and sometimes, instead of just updating certain information about a student, someone will just add the student again with a different ID. I want to get rid of all duplicates (which is a pain since it is almost impossible to tell them apart), which would be nice just by removing the duplicates, except other tables might rely on the duplicate. How can I change all tables that rely on a specific ID to rely on the ID I choose?
This is how it looks:
Student ID | L. Name | F. Name ANDY-01 | Andy | Andy ANDY-02 | Andy | Andy
Then in the course table I would have courses that ANDY-01 would take and courses ANDY-02 would take. I want to concatenate all records in all tables that need to have ANDY-01 and ANDY-02 as ANDY-01. How can i do this?
(Don't worry about how I differentiate between ANDY-01 and ANDY-02)
a source to share
+1 for Riho's answer. To update multiple tables, you can create a procedure like the one below and manually update the ID values and follow the procedure for each student.
If you have a table or query that displays old and new IDs, you can write another procedure to read the table and call that procedure for each student.
Public Sub UpdateStudent()
Dim oldID As String
Dim newID As String
oldID = "ID1"
newID = "ID2"
DoCmd.Execute "update another_table set student_id='" & newID & "' where student_id=" & oldID
DoCmd.Execute "update yet_another_table set student_id='" & newID & "' where student_id=" & oldID
End Sub
a source to share