update query question 2

[blaine011]

I have a database which I wish to normalize.

One of the attributes is province. I created a provnum attribute which will store the reference numbers for the province.

I created a separate province lookup table, with reference numbers and province name.

I want to create a query that will go through the main customer table, and for each province name, it will check the province lookup table, get the reference number for each province it finds, and then put that number in the provnum attribute field in the main customer table, So that I can remove the province attribute from the main customer table.

How do I do this?

 

[BobAtHome]

In the sql statement below, replace the table and field names with your table and field names. Create a new query, click on "View" and select "SQL View". Cut and paste the modified sql statement into the query "SQL View" box. Run the query.

UPDATE CustomerTable INNER JOIN ProvinceLookUp ON CustomerTable.ProvinceName = ProvinceLookUp.ProvName SET CustomerTable.ProvinceRefNum = ProvinceLookUp.ProvRef;

 

[dhookom]

In addition to BobAtHome's response, you may need to temporarily set the primary key of ProvinceLookup to the ProvName field.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.