[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 664
  • Last Modified:

Master Table and LookUp Table relationship

Is it necesary to create a relationtionship between a table and all the lookup table it is using, or not

bets regards
2 Solutions
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
titorober23Author Commented:
any benefit if i create them
Jeffrey CoachmanCommented:
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now