Table Relationships Join Types? 1

[gallas]

We are currently designing additions to our data model with some new tables, etc, etc.

Something that we've previously ignored is that in the MS Access Tables Relationship window it is possible to set join types of table to table. What is the significance of this? It seems to have no relevance at all at the table level. Naturally we define the join types at the query level to return the data we want.

There have been other threads which have come close to answering this but nothing concrete. Anyone any comments? [Smile2]

"If a job's worth doing, it's worth doing twice!"

 

[BNPMike]

Relationships declared to Access will cause it to default these in the QBE window for example. You also need to declare them if you want to declare referential integrity.

As you realise, you don't need a relationship in order to retrieve data from multiple tables.

 

[pbrodsky]

My understanding, and someone correct me if I am wrong, is that the relationships window does 2 things:

1. it sets up default relationships for the query builder if you load 2 tables that have a defined relationship

2. it allow you to define enforced relationships, with integrity enforced by the database engine.

It doesn't affect relationships defined in queries.

 

[PHV]

And it makes the Form wizard smarter.

 

[gallas]

Thanks all.

The only real meaning to defining table relationship Inner and Outer join types is to set defaults for queries, the form wizard, etc. So when you create queries or use the form wizard, etc, they will inherit the join types set at the table relationship level (as well as the relationship types and ref integrity).

Who gets the star?

"If a job's worth doing, it's worth doing twice!"

 

[BNPMike]

How come you knew the answer all along?

 

[gallas]

Just a bit nervous when messing with our main db back end! [Bigsmile]

"If a job's worth doing, it's worth doing twice!"