converting regular field to lookup field - help!

[missy]

How would I replace a regular field with a lookup field?
I have a notion about how to add a lookup field when you
enter it as a new field, but I'm leery about making a
lookup field on the same line as the original one. (I'm
scared we'll lose previously entered data.)

Is it possible to do this without having to start a new
field and then transferring all the old data?

Thanks in advance.

 

[John Vinson]

How would I replace a regular field with a lookup field?

If you're sensible, you WOULDN'T DO SO.

Lookup Fields are an abomination; they cause FAR more confusion and
inefficiency than benefit.

If you want to create a lookup *table* - for use with a Combo Box on
Forms, not in your table datasheet - there are some steps you can
take; but I'd really recommend against attempting to do it in your
Table.

 

[missy]

Well, I never made any claim of being sensible.

We're already using lookup fields for some of our info and
they've worked quite well for us so far. Unfortunately,
these were all set up before any data was entered and now
I show up and decide it'd be nice to not have to
type "Australia" or "British Virgin Islands" a hundred
times over.

The reason I'd prefer to link it to an existing table is
because I'm worried about omissions if I make a separate
table just for this particular field.

 

[John Vinson]

The reason I'd prefer to link it to an existing table is
because I'm worried about omissions if I make a separate
table just for this particular field.

You can create your "lookup" table, tblCountries say, as a single
field table with no Autonumber ID: country names should be unique
anyway so simply make the country name the Primary Key.

You can fill the table using an Append query. Create a Query based on
your table, select only the country field, and set the query's Unique
Values property to Yes. Change it to an Append query and it will
populate your new table with every country in your table (including
the misspellings! Edit it!)

You can then create a Combo Box based on tblCountries, or, if you
REALLY insist, change the Lookup property of the Country field to use
a combo box based on tblCountries.