Normalizing with Update

[Nicodemas]

Hello, I have run into an issue and need someone to explain a process to me in laymen's terms so I can understand it.

I have two [hypothetical] tables in MS Access. They are define thus:

Table: Products
 
ID      Name    Customer
-------+-------+-------
1       Pants   Bob
2       Shirt   Henry
3       Shoes    Dad
4       Socks   Dad
5       Apple   Henry
6       Banana  Henry
7       Orange  Henry
 
Table: Customers
 
ID      Name 
-------+-------
1       Bob
2       Henry
3       Dad
4       Jacob
 

Please help me understand the SQL statement I could write in order to update 'Products' by replacing the data in the Customer column with the data in the ID column of 'Customers' which corresponds.

[FishVal]

Something like the following:

UPDATE Products INNER JOIN Customers ON Products.Customer = Customers.Name SET Products.CustomerID = Customers.ID;
 

where Products.CustomerID is a new field with a relevant datatype (I guess Products.Customer and Customers.ID datatypes don't match)

[NeoPa]

First add a new field into your Products table called CustomerID. This would be of the same type as the [ID] field already in the Customers table, except that if Customers.ID is an AutoNumber field, CustomerID would have to be set as Long Integer.

That done, you would use SQL very similar to (identical with really) that which Fish has supplied already :

UPDATE Products INNER JOIN Customers
    ON Products.Customer=Customers.Name
SET    Products.CustomerID=Customers.ID

[Nicodemas]

Delicious, thank you!

[NeoPa]

You're welcome :)

Anyone looking to normalise existing data is worth supporting as far as I'm concerned.