×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Change a field to a lookup field without losing info

Change a field to a lookup field without losing info

Change a field to a lookup field without losing info

(OP)
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**

RE: Change a field to a lookup field without losing info

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

RE: Change a field to a lookup field without losing info

(OP)
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**

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close