Microsoft Access - How can I convert a text field into a look up field without losing my current data?

Asked By Sheri Havens on 13-Jun-12 10:16 AM
I have a CSV file that I am importing into Access. Everything is fine, up until I want to make some of the fields look up fields. When I do, I loose the data. I have over 16,500 records and going back and re-doing is not an option.

I've made sure that my look up field is set to the same size as the entry field. I still get the prompt that my fieldsize property has been altered to a smaller size and data may be lost. Then Access encountered errors and the contents of 16,645 records has been deleted.

In this particular database, I will want to make look up links to about 18 of the fields.
wally eye replied to Sheri Havens on 13-Jun-12 10:41 AM
You can create new fields as your lookups, write a query to move the values from the text fields to the lookup fields, delete the original field and rename the lookup to the same name.

You should be take care to ensure that all the lookup fields actually get populated from the query, though, some of the current data might not validate against your new lookup list.
Sheri Havens replied to wally eye on 13-Jun-12 11:49 AM
What I actually found that worked was to have my look up fields already defined, and then make sure that the links and relationships were deleted. Then imported my data. I didn't lose the data, and all my look up fields worked great.

I also had to make sure that my look up fields were defined as "List" not "Combo".