Change a field to a lookup field without losing info 1

[bgibsonIYD]

3 Tables: Names2, State, Country

Names2 has a State and Country Field that need to be lookup fields to lookup the state abbreviation and the country abbreviations

State: StateID,Abbreviation,STATE
Country: CountryID,Abbreviation,COUNTRY

The Names2 table already has 4800+ records. How do I change the State and Country fields in Names2 without losing all the data that already exists for those 4800+ records?

Beth
**Learning Access**

 

[PamelaG]

Hi Beth!

This is a pretty common scenario, so you will want to add this to your bag of tricks!

1. Create a query that shows Names2 and States.

2. Drag a relationship line from the State field in Names2 to the same field in State (this allows the records to match up by the state name).

3. Make sure the query is an outer join to the Names2 table so you don't lose any records in case the state name is missing or wrong.

4. Add the following to the QBE grid:

tblNames2!Names2Key
tblNames2!all other data except state
tblStates!Statekey

5. Use the resulting recordset to create a new table called Names2Corrected

6. Rename Names2 to Names2Old

7. Create a new Names table that has the proper design including the lookup.

8. Append the records in Names2Corrected to the new Names table.

HTH!

Pamela

http://www.accessinsight.com

 

[bgibsonIYD]

I knew there was a way that you could do it. I will definantly add that to my bag of tricks as I have to do that with several different fields, the state field was just one of them.

Beth
**Learning Access**