Table LookUp Field disconnect

Posted on 2011-10-11
Last Modified: 2012-05-12
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.
Question by:Sasha42
    LVL 61

    Accepted Solution


    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.
    LVL 47

    Expert Comment

    by:Dale Fye (Access MVP)

    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.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now