Name Mapping Query (1 Viewer)

[tmyers]

I am trying to make a query to normalize manufacturer names as they are imported. I made a table [tblNameMapping] and it has an ID autonumber for its PK then two fields [OldName] & [NewName].

[OldName] is how the manufacturer appears on the vendors quote and [NewName] is how we want it. I am then trying to run an update query against my table [tblProduct] and everywhere the field [Manufacturer] matches the [OldName], update [Manufacturer] to the [NewName].

I think I am close, but I have initially written:

UPDATE tblNameMapping, tblProduct
SET tblProduct.Manufacturer=tblNameMapping.NewName
JOIN tblProduct.Manufacturer
ON tblNameMapping.OldName

Not quite right however. Am I close?

 

[theDBguy]

Hi. It would be more like.

UPDATE tblProduct
INNER JOIN tblNameMapping
ON tblProduct,Manufacturer=tblNameMapping.OldName
SET tblProduct.Manufacturer=tblNameMapping.NewName

Sent from phone...

 

[jdraw]

For consideration:
If you have an authoritative table of Manufacturers with your ID and the associated names(OldName,NewName), then you would have the ManufacturerID on the Product.

How do you vet Manufacturer names? We often had Names - Operating, Legal, Aliases - all associated to a unique CompanyID.

 

[tmyers]

For consideration:
If you have an authoritative table of Manufacturers with your ID and the associated names(OldName,NewName), then you would have the ManufacturerID on the Product.

How do you vet Manufacturer names? We often had Names - Operating, Legal, Aliases - all associated to a unique CompanyID.

Mostly it is just for formatting purposes. Most of the names are abbreviated, in all caps or have words that are not needed.

An example would be:
The vendors quote shows the manufacturer as CHLO but it is short for Chloride. To save users time of typing to correct it, I made the above to change it to Chloride during the import process. Like wise we have one that is ABL-Lithonia Lighting Company. That is cut down to simply be Lithonia. The last example would be LUMARK is simply changed to Lumark.

All I am using it for is to format the text properly so it is uniform easily read. I figured this was a nice easy way of doing it.

 

[arnelgp]

keep same Text info on two tables can be a pain.
you need to update both tables to be in synch.

what is advised is to only have a ManufacturerID (FK and the autonumber of tblNameMapping).
so you only "update" tblNameMapping with whatever new name you prefer.

 

[The_Doc_Man]

Arnel beat me to it. You should only have the Manufacturer's name in one place - the Manufacturer table. Every place that name appears outside of the table, you should instead have a foreign key of the ManufacturerID. If you are normalizing tables, THAT is how they should be normalized.

Then you would never have to do those updates in tables other than the Manufacturer table.