converting look-up fields

[KimC]

I have a database with a lookup field I created with the Wizard. Now I want
to get rid of the lookup key value that is stored and instead store the
actual value. Is that possible?

 

[Rick Brandt]

I have a database with a lookup field I created with the Wizard. Now
I want to get rid of the lookup key value that is stored and instead
store the actual value. Is that possible?

In design view of the table, select that field then go to the bottom of the
grid and you should see two tabs "General" and "Lookup". Go to the Lookup
tab and change the Display Control to "Text Box".

 

[KimC]

Is there any way to preserve the value associated with the foreign key, or do
I have to enter all the data again into the new Text Box?

 

[Jeff Boyce]

Pardon my intrusion...

I may be misunderstanding, but it sounds like you want to remove the key
value and only store the "text" value. This would be backwards from a
well-normalized design, in which you would ONLY store the ID value and use a
query to join to the lookup table that holds the text value.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Arvin Meyer [MVP]]

Kim,

The tables in a database are to store data and to maintain the data's
integrity. They do not exist to display or directly enter data directly.
Anything that was done to make display or data entry into the tables easier
is patently wrong. Please see:

http://www.mvps.org/access/tencommandments.htm

and look at the first and second commandments. The second commandment has a
link to the evils of lookup fields:

http://www.mvps.org/access/lookupfields.htm

Rest assured that your data is safe and sound when using proper keys instead
of text. A query will allow you to link the actual value.

Relational databases are (or should be) mathematically efficient. Storing a
4 byte key is much more efficient that storing 20 to 30 bytes of data.

As an added benefit, by storing the key you only need to change the data in
1 place instead of repeatedly. Think about this: Suppose you had names in
your database for a man named Smith and a man named Smyth. Now you need to
add a new one and mistakenly enter "Smyth" instead of "Smith". How would you
fix it? No query would work because of the properly named Smythes. You'd
have to check each place the name occurred and fix it. With a properly
designed relational table, you'd fix the 1 wrong entry.

 

[KimC]

Jeff,
You are correct in understanding what I want to do. I understand relational
tables and normalized design and all that. The reason I wanted to store the
text and not a key is so that I could have the dropdown list generated by a
query of the table displaying all the previously entered values. That way, a
user could enter a value not in the list and it would appear on the list the
next time. We are still in the design and test phase, so there is not a ton
of data to deal with right now. Yes, it does sound backward. Maybe I am
going about creating "self-generating" drop down lists in the wrong way? Can
I use the same technique with the linked table as the data source like it is
now? Will the data table update if I add a value not in the original table?
Maybe what I want to do is easier than I think.

 

[Jeff Boyce]

Kim

I've used "self-generating drop-downs", as you term them, for looking up a
city, given a zip code. I do this on an Address form, and use the zipcode
field of the Address table to look up cities using that zipcode (I have at
least one zipcode shared by two cities).

You might look into using the NotInList event (and LimitToList property) to
help you add new values.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[KimC]

Thanks, Jeff, I hadn't thought of that. I'll give it a try.