Relationship or Join

[Guest]

Why would someone use a relationship instead of a join (left or right)?

I'm trying to grasp relationships better.

Thanks!!!

 

[Guest]

A relationship makes a database level 'join'. It's basically the same thing
except that relationship information is physically written to the disk. Your
still have to join them in the query though.

Difference is, if you make the relationship first, it will enforce it at the
table level so that no matter what query is made, or how the data is entered,
it will keep that rule.

Also, because it creates the join information every time the data is
entered, your queries will run incredibly faster. If you don't, Access (or
any db for that matter) has to do the join 'on the fly' in memory.

Hope that helps..

 

[John Vinson]

Why would someone use a relationship instead of a join (left or right)?

I'm trying to grasp relationships better.

Thanks!!!

Just to add to Tom's excellent points:

The main benefit of a Relationship is that it protects your data. For
instance, if you have the classic Orders - OrderDetails tables,
related one to many with relational integrity enforced, Access will
simply throw an error if you attempt to put a record into OrderDetails
without a corresponding Order already in existance. If you don't have
such a relationship, you're not protected from this particular type of
"garbage" in your database.

John W. Vinson[MVP]

 

[Guest]

is it advisable to have left or right join when we have not an suitable
unique field to create reletionship between the table on normalisation ?

 

[Arvin Meyer [MVP]]

Left and Right Joins allow you to see all the data from 1 table and matching
records from the other. Equi-Joins only allow matching data.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Jamie Collins]

is it advisable to have left or right join when we have not an suitable
unique field to create reletionship between the table on normalisation ?

Sounds like you may instead want a 'fuzzy match'. Post some details.

Only when you've got "with relational integrity enforced" of course
a.k.a. a FOREIGN KEY. If this is assumed, the two elements in the OP's
question can't really be compared because an FK is SQL DDL and a JOIN
is SQL DML. Using the more relaxed interpretation of 'Access
Relationship', the answer could be, "Defining an Access Relationship
saves you some keystrokes/mouse clicks when you add those tables into
the Query Designer Builder Thing" <g>.

Jamie.

--