I am focusing on an MS Access Database with a lot of duplicate records. However , there's a table of scholars, and often rather than just upgrading a particular student's information, someone would certainly add a student in again having a different ID. I wish to eliminate all of the replicates (the industry discomfort since there's barely in whatever way to distinguish them), which may be fine just by removing the replicates, with the exception that other tables may depend around the duplicate. How do i change all of the tables that depend on the certain ID to depend around the ID which i decide to keep?
This is what it appears like:
Student ID L. Title F. Title ANDY-01 Andy Andy ANDY-02 Andy Andy
Then within the courses table I'd have courses that ANDY-01 might have taken, and courses ANDY-02 might have taken. I wish to merge all records in most the tables that will have ANDY-01 and ANDY-02 as ANDY-01. Wouldso would I build a storage shed?
(Don't be concerned about how exactly I am distinguishing between ANDY-01 and ANDY-02)
You just need to have update SQL:
update another_table set student_id=:ID2 where student_id=:ID1
+1 for Riho's answer. To update multiple tables you can produce a procedure such as the one below, and by hand update the ID values and execute the process for every student.
For those who have a table or query that maps that old and new IDs you can write another procedure to see the table and refer to this as technique of 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