Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Can anyone explain what the benefit of the different join types are when joining fields/tables in the relationships view in MS Access (I'm using 2010 but I guess this applies to earlier versions)? I can perfectly well understand why I might want a left outer join or an inner join when performing a query but I don't grasp what this does when I'm editing the relationships between tables themselves (i.e. not in a query). Clearly, 'enforce referential integrity' means I can't accidentally create orphan records (and query joins won't do this) but the applicability of join types outside queries mystifies me.

share|improve this question
FWIW, I almost never define anything in the relationships window except real RI, i.e., two tables related with RI enforced. Drawing lines without RI seems to me just a massive waste of time. If the tables are related, there needs to be RI defined. Anything else is just pretty pictures that may or may not make certain kinds of operations easier in the QBE, but serve no real purpose otherwise. In other words, I'd suggest you simply ignore this aspect of the relationships window entirely. – David W. Fenton Jul 31 '10 at 19:52

On the relationship view, please think of it as this is how the database was designed to operate. When you create queries, those joins will automatically appear based on the relationship view. The database will work without any relationships in the relationship view but for every query and report you create; you will have to create the appropriate joins.

share|improve this answer

Depending on the design of the database where a join really only applies under certain condition. Anything you define in the relationship view for the most part will be used always. If you need a join for only a single query then may want to only create that join as part of the query.

share|improve this answer
Thank you both, that makes very good sense - I'm most grateful. – Patrick Jul 31 '10 at 9:31

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .