How do I get rid of Duplicate Entries With Unique IDs in MS Access 2003?

Question

I'm working on an MS Access Database with tons of duplicate entries. The problem is that there is a table of students, and sometimes instead of just updating a certain student's information, someone would just add the student in again with a different ID. I want to get rid of all the duplicates (which is a pain since there's barely any way to differentiate them), which would be fine by just deleting the duplicates, except that other tables may rely on the duplicate. How can I change all the tables that rely on a certain ID to rely on the ID that I choose to keep?

Here's what it looks like:

 Student ID | L. Name |F. Name

 ANDY-01    | Andy    |  Andy

 ANDY-02    | Andy    |  Andy

Then in the courses table I'd have courses that ANDY-01 would have taken, and courses ANDY-02 would have taken. I want to merge all entries in all the tables that would have ANDY-01 and ANDY-02 as ANDY-01. How would I go about this?

(Don't worry about how I'm differentiating between ANDY-01 and ANDY-02)

Answer 1

You just have to make some update SQL:

update another_table set student_id=:ID2 where student_id=:ID1

Answer 2

+1 for Riho's answer. To update multiple tables you could create a procedure like the one below, and manually update the ID values and execute the procedure for each student.
If you have a table or query that maps the old and new IDs you could write another procedure to read the table and call this 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