Master Table and LookUp Table relationship

Posted on 2009-02-19
Last Modified: 2012-05-06
Is it necesary to create a relationtionship between a table and all the lookup table it is using, or not

bets regards
Question by:titorober23
    LVL 84

    Assisted Solution

    by:Scott McDaniel (Microsoft Access MVP - EE MVE )
    You aren't required to create ANY relationships. You can handle all data integrity on your own, if you so desire.

    Access will, however, create relationships behind the scenes if you join those two tables in a query, for example. These are simply for Access to use, and you cannot get at those relationships.

    Author Comment

    any benefit if i create them
    LVL 74

    Accepted Solution

    LOL, what some might consider a benefit, others will call a detriment.

    "Potential" benefits:
    1. If the "Enable AutoJoin" option in Access is turned on, it will automatically join the lookup table to the main table if you use both tables in a query.
    2. You can enforce referential integrity, *However*, as LSM stated, your database should really handle this.
    Both of these "benefits" can work against you as well.

    I do it sometimes, just so I can see how things "flow" in my database.
    The times when I did not, it never made any difference.

    Please remember that the only purpose for a Lookup table is to "Look up" values.
    IMHO, a "Lookup" table really will only have one or two fields.
    The ID Field and The Name Field, or just the Name Field.

    Now, you could argue that an Employee table is a lookup table, because you can use it as the source for a combobox to "Lookup" an employee.
    But that probably goes beyond the scope of this question.
    (Just my 2c)

    Let's see if LSM has any thoughts on this...


    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

    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

    15 Experts available now in Live!

    Get 1:1 Help Now