How do you get rid of a lookup field

[cedooley]

I'm trying to fix a database that someone else created. Although I hav
quite a bit of Access experience, I'm stuck!

Basically, one of the tables has a field set up as a Lookup Column.
want to change it to just a plain old text with the Lookup's associate
values (as opposed to the stored id number) as the actual value. I'v
tried creating a new field and running an update query to set the ne
values, but no matter what I do I get the stored number instead of th
real value.

There's already a LOT of data in this table, otherwise I would jus
retype the values. Help

 

[John W. Vinson]

I'm trying to fix a database that someone else created. Although I have
quite a bit of Access experience, I'm stuck!

Basically, one of the tables has a field set up as a Lookup Column. I
want to change it to just a plain old text with the Lookup's associated
values (as opposed to the stored id number) as the actual value. I've
tried creating a new field and running an update query to set the new
values, but no matter what I do I get the stored number instead of the
real value.

There's already a LOT of data in this table, otherwise I would just
retype the values. Help!

It'll take a few steps to do this.

1. MAKE A BACKUP of your entire database. Check the backup, make sure it
works! You might need it.
2. You *did* check the backup... right?
3. Open your table in design view. Add a new Text field the same size as the
Text field in the lookup table (it will become the text value, but you'll need
to use a different name for the moment). Select the offending lookup field
and, on the Lookup tab on the field properties, change it from "Combo Box" to
"Textbox". You'll now have a blank text field and a Number field.
4. Close the table and create a new Query. Add this table *and* the lookup
table for this field. Be sure to include the new blank text field, both ID
fields, and the text field from the lookup table; the two tables should be
automatically joined on the ID - if they're not, join them.
5. Change the query to an Update query using the Query menu item or the
query-type tool on the toolbar.
6. On the Update To line under the new blank text field type

[lookuptable].[lookupfield]

using the name of your lookup table and the text field in that table.
7. Run the query by clicking the ! icon on the toolbar. Accept any warning
prompts (you *did* check your backup... right?)
8. Open the table and see if the new field is properly updated.
9. Open the Relationships window; click the "Show All" icon. Select the join
line between your table and the lookup table and press the Delete key.
10. Delete the lookup table.
11. Open the main table, delete the lookup field (no longer needed), and
rename the newly created field to the old name.
12. Test all your forms and reports.

 

[Dale Fye]

Can I ask why you want to do this?

I concur that you really should not use the "lookup" feature of the table
design, because it tends to confuse you and others into thinking that the
value in the field is actually the text value. But you can still use the
numeric field, just don't make a lookup. Then, when you want to view the
text value, create a query and link the two tables on the numeric field.

Dale

 

[cedooley]

Dale - I don't think the lookup column was even designed correctl
(although I could be wrong because I never use them) and it's causin
problems for the user. Otherwise if it were working properly, yeah, I'
just leave it.