Change existing TEXT field to LOOKUP

[frankd]

I have a populated TEXT field that I would like to change to LOOKUP on
another table for all new records. I receive an error msg stating that all
records will be deleted if I save the table. Is there a work around?
In advance, thanks, frank

 

[Tim Ferguson]

I have a populated TEXT field that I would like to change to LOOKUP on
another table for all new records.

LookUp fields are A Really Really Bad Idea -- but perhaps you mean that
you'd like to make a proper Foreign Key and relationship.

First some assumptions: your original table looks like this:

Eric, 32, Red
Simba, 4, Blue
Fred, 17, Ginger

and the other table looks like this:

1 Red
2 Blue
3 Green

First, back up everything twice before you start.

Make sure you data are "clean": that every colour exists in the other
table. Note that record 'Fred' has 'Ginger', which is not legal colour
in the look up table. Either add a new row in the look up table or change
the original record. You can create a query to help with this.

Next, add a new field to the original table to hold the new FK -- call it
ColourID or something, and make it exactly the same data type and size as
the ID field in the Colours table.

Use an update query to join the two tables on the colour text and fill in
the appropriate value of Colours.ID into OriginalTable.ColourID.

When that is complete and tested, you can delete the old
OriginalTable.Colour field, and create the relationship between the new
field and the Colours.ID field.

Did I mention to back up everything twice before you start?

Hope that helps


Tim F

 

[frankd]

Tim, why are LookUp fields "A Really Really Bad Idea?" The field I am
working with is WorkCity and when I layed out the database I thought it would
be ok to enter each city individually. Now I find many cities are repeating
themselves. You are correct in that I have cities in the WorkCity field NOT
currently in my related City table. I'll work on it and again using the
Lookup Wizard. Thanks for your help. I am interested in your opinion of
Lookup fields. Frank

 

[John Vinson]

Tim, why are LookUp fields "A Really Really Bad Idea?" The field I am
working with is WorkCity and when I layed out the database I thought it would
be ok to enter each city individually. Now I find many cities are repeating
themselves. You are correct in that I have cities in the WorkCity field NOT
currently in my related City table. I'll work on it and again using the
Lookup Wizard. Thanks for your help. I am interested in your opinion of
Lookup fields. Frank

Take a look at http://www.mvps.org/access/lookupfields.htm for a
critique.

The problem isn't using Lookups - the problem is using them IN TABLES.
Tables are *not* designed as data entry or editing user interfaces;
they're used for data storage, and data storage *only*. To edit data,
use Forms - and on a form, you would indeed use a Combo Box (a
"lookup" if you will) to enter the WorkCity.

John W. Vinson[MVP]

 

[Tim Ferguson]

You are correct in that I have cities in the WorkCity field NOT
currently in my related City table. I'll work on it and again using the
Lookup Wizard.

Nonono :- like John V says, it's the LookUp Wizard (i.e. Lookup Fields)
that is the menace. It gives the user/ developer a completely false idea of
what is actually going on in the database, often does the implementation
really badly anyway; and it's so quick and easy to set up a relationship by
hand anyway that the disadvantages are just not worth it.

Best wishes


Tim F

 

[frankd]

John, I suspect I didnt make myself clear. My intent is to use a Form Combo
Box for possible cities from CITY TABLE to populate the WORKCITY FIELD in
CONTACT TABLE. I have many addresses using WORKCITY already in CONTACT. The
Lookup wizard within CONTACT design view prompts me to name the Lookup field
(WorkCityLU) and to save CONTACT table. When I acknowlege that I want to
save CONTACT table Access then notifies me that all of my records will be
deleted. Is there not a way to change the status of WORKCITY from Text to
Numerical (Lookup) in CONTACT design view without losing previously entered
records? Thank you very much.

 

[John Vinson]

John, I suspect I didnt make myself clear. My intent is to use a Form Combo
Box for possible cities from CITY TABLE to populate the WORKCITY FIELD in
CONTACT TABLE. I have many addresses using WORKCITY already in CONTACT. The
Lookup wizard within CONTACT design view prompts me to name the Lookup field
(WorkCityLU) and to save CONTACT table. When I acknowlege that I want to
save CONTACT table Access then notifies me that all of my records will be
deleted. Is there not a way to change the status of WORKCITY from Text to
Numerical (Lookup) in CONTACT design view without losing previously entered
records? Thank you very much.

You'll need a one-time Update query.

Add a new WorkCityID field, Long Integer, to your table. (Turn off ALL
OF THE LOOKUP FIELDS).

Create a Query joining your current Text WORKCITY to the city name
field in [CITY TABLE]. You may need to (at least temporarily) put a
unique index on the city name field (not a permanent solution, I live
in Parma, Idaho but there is also a Parma, Ohio and of course Parma,
Italy).

Update the newly added WorkCityID field to the ID field from your city
table.

Then delete the text WORKCITY field and use a combo box on your form
to display the city name while storing the city ID.

John W. Vinson[MVP]