Consolidating duplicate child records

  • Thread starter Thread starter sdrawkcab
  • Start date Start date
S

sdrawkcab

Hello,

I have two tables with a relationship as such:

tblAuto

ItemID(PK), ModelID, VIN
2411, 311, LLLLLLLL
2412, 363, MMMMMMMM
2413, 488, NNNNNNNN
2414, 311, OOOOOOOO
2415, 926, PPPPPPPP

tblModel
ModelID(PK), Model
311, Mustang GT
363, Sebring JXI
488, F-150 Lariat
926, Mustang GT

I would like to write a query to analyze tblAuto, find all instances of
tblAuto.ModelID which point to duplicate records in tblModel.Model
where tblModel.ModelID = tblAuto.ModelID, and change all the duplicates
to one value. I already have the query to delete the orphans in
tblModel after that.

So, in the above example, I would like to change tblAuto.ModelID = 926
to 311 based on the fact that these are both Mustang GT.

Thanks for any help,

Mike Brown
 
I would add a column to tblModel for the Correct value and set that value to
the desired value (is it always the lowest number)
Then use that to update tblAuto
Then delete the duplicates out of tblModel.

First query:
Update tblModel
Set FieldCorrect = DMin("ItemID","tblModel","Model=""" & tblModel.Model
&"""")

Second query
UPDATE tblAuto INNER JOIN tblModel
ON tblAuto.ModelId= tblModel.ModelID
SET tblAuto.ModelID =[tblModel].[FieldCorrect]

Third query
DELETE DistinctRow TblModel.*
FROM TblModel
WHERE TblModel.ModelID <> tblModel.FieldCorrect

You could also just use a temporary table that is a copy of tblModel with
the additional field. Then use it for the update and you could use it for
deleting the extraneous models in the tblModel
 
Back
Top