Join Type in Relationships (1 Viewer)

[Shep]

When might one configure a Join Type in Relationships as something other than option 1 ?

(1: Only include rows where the joined fields from both tables are equal)

I understand the use of join types in queries, but cannot understand why or when one might set this in the Relationships window.

Thanks in advance.

 

[Travis]

You would use the other options when you want all the data to show from one table even when there are no records in the second table.

Example:

You have an Employee Table

You have an Employee Family Table (Lists Names and Birthdays of Spouse and Kids)

When you run a query on Employees and want the list to include Spouse and Kids information, you don't want to leave out the Single Employee's.

You would then be able to get a list of employees regaurdless if you have Family information or not.

This technique is useful for storing information that does not always occur for every record.

 

[Shep]

All right, thanks very much.

That explains the 'why' - it's no more than I expected, I suppose.

The 'when', I fail to understand...since you can simply set the join type in a query.

No great worry, though. I was just curious. Thanks.

 

[Pat Hartman]

In the relationships window, you would be setting the DEFAULT join type. That would only be used by Access when you build a query in QBE view. When relationships are defined, Access automatically draws join lines between tables that participate in defined relationships. This default simply tells Access what type of join to assume. You can override the assumption in the query by double clicking on the join line and choosing a different option.

 

[Shep]

Ahh...quite clear now. Thanks Pat!