Update child table based on Parent Duplicates

[gen2fish]

Good Day!

I am working on an audition tracking database that was implemented before I stared working here. It was originally designed poorly with no normalization and each audition got a new record regardless if that individual has taken one before. I have since split it into five tables.

For my Student Table I have;

LName
FName
MInit
ID

By migrating the data I have about 2000 duplicate students.

My Audition Table is

ID
studID
AuditionDate
Remarks
scores...

I would like to find a way to run a query to find duplicates (easy) and to change the duplicates in the Auditon table. For example;

I would query tblStudents and get;

Doe, John P 654
Doe, John P 7822
Doe, John P 1382

how to I update tblAudtions so that 7822 and 1382 are now 654?

 

[MStef]

As I can understand you, you want to make one record "Doe John 654" from 3 records. Make a new table "tblStudentNew" with Index1 on the field LName and
FName (1 index on 2 fields), with UNIQUE properties = YES. Make an "Append query" on the table Student (input), and tblSdtudentNew (output). Run this quer. In the query put the SORT (asc) on the field ID, and on the fields LName and FName. Run this query. Before take a copy of your table.

 

[MStef]

Look at "DemoIndex1A2002-2003.mdb" (attachment, zip).
Look at tables, query. Run query adn look at "tblStudentNew".
But it is SUSPICIOUSLY if you have got 2 students with the
same LName and FName.

 

[gen2fish]

Thank you for your reply. However my goal was not to delete the tblStudents, it was to change the related table so that the three instances of one name would be consolidated. What I ended up doing is writing a script that went through each record in the student table and compared it with each record in that same table. If the LName and the first four letters of the FName were the same, I would update the Auditions table to point to one name.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rsa As DAO.Recordset
Dim str As String
Dim firstLName As String
Dim firstFName As String
Dim firstID As String
Dim leftone As String
Dim lefttwo As String
 
str = "SELECT * FROM tblStudents"
Set db = CurrentDb
Set rs = db.OpenRecordset(str, dbOpenDynaset)
Set rsa = db.OpenRecordset(str, dbOpenDynaset)
 
DoCmd.SetWarnings (False)
Do Until rsa.EOF
firstLName = rsa![LName]
firstFName = rsa![FName]
firstID = rsa![ID]
rs.MoveFirst
Do Until rs.EOF
  If firstLName = rs![LName] Then
    If Left(firstFName, 4) = Left(rs![FName], 4) Then
 
    DoCmd.RunSQL ("UPDATE tblAuditions SET StudID = " & firstID & " WHERE StudID = " & rs![ID])
 
    End If
  End If
 
  rs.MoveNext
 
Loop
rsa.MoveNext
Loop
rs.Close
rsa.Close
DoCmd.SetWarnings (True)

 

[MStef]

But, there are no LName and FName in the related table (tbl Audition).
I think your tables not normalized well.

 

[gen2fish]

I use an autonumber in my student table with a number in my auditions table because people can have the same last name and the same first name. Not likely, but possible.