Table LookUp Field disconnect

I inherited a database.  In one of the tables there is a field that has a LookUp on the LookUp tab- a combo box pulling from another table.  I want to seperate that data into two fields.  If I delete the LookUp combobox I get the value for the field.  I need the value of the LookUp combobox too. Thanks.
Who is Participating?
mbizupConnect With a Mentor Commented:

Storing both the ID and the lookup Text in your table is redundant, and should not be done.

Rather than storing both peices of data in your table, you should store the ID field (as you are already), and use combo boxes or queries to display the text on the fly, as you need it.
Dale FyeCommented:

no points please.

to add to mbizup's comment, I'd go so far as to say that the "lookup" feature that MS added to the tableview in Access is one of the causes of confusion in most newbies, and which causes confusion for some of us that are more experienced, when working on someone elses database.  Because you don't see the actual value that is stored in the field (when using lookups), users get the impression that what is stored is the text, and when they write queries they try to use the text values rather than the actual numeric values.

Rather than using this feature, I would recommend that you delete the lookup from the table definition and create a query to view your data.  With a query you can join the two table on the appropriate field, and choose to display which ever fields you want, from either table.  This is the way relational database were meant to be viewed.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.