Question about the two types of Join Property boxes in queries, please help!

[Kelvin Beaton]

I've notice that in some queries I have different options when I look at the
properties.

One join type give me three options. Both values the same, All of table A
and all the records from table B where the joins equals, etc.

There there are times when I get a box that gives me the option of cascading
updates, deletes, etc, with and additional join type properties button that
gives me the options in the partagraph above.

What am I doing different to get these two types of Join Property boxes?

I have primary keys in both tables.
I'm using Access 2003 for the front end and MS SQL for the backend.

Any input would be appreciated

Kelvin

 

[Van T. Dinh]

Are you using ADP (Access Project) format or MDB (Access Database) format?

It sounds like ADP (which I don't use in my production databases) and my
guess is that you the first dialog you described is in the Query / View
Design interface while the second one is is the Table Design stage since we
usually set the R.I. + Cascade Update and Cascade Delete in the Table
Design.

 

[Bill Mosca, MS Access MVP]

Kelvin

The first box you mentioned is the one in the relationsips window. This is
for establishing relationships between tables such as one-to-many. This
prevents the entry of a record in the "many" table if there is no parent
record in the "one" table. You can't enter an order for a customer if the
customer is not in the customer table.

The relationships set up a permanent structure until you open the window and
change them.

The second box you mentioned is strictly for joins in a query. As you saw,
the 3 different join types retrieve different records. These joins are
temporary and hold only for that query.

 

[Guest]

Kelvin:

I think you are confusing queries with relationships. Tables in a query can
be joined in 3 ways, which the options in the 'Join Properties' dialogue
represent:

1. An INNER JOIN. This is the default when you create a query and returns
only those rows where there are matching values for the join fields in both
tables.

2. A LEFT OUTER JOIN. This returns all rows from the table on the left
side of the join (subject to any criteria which restrict this), and only
matching rows from the table on the right side (note that you can't have any
criteria on the right side table as this would in effect make it an INNERE
JOIN). Any fields from the table on the right side for rows where there is
no match with a row on the left side will be Null.

3. A RIGHT OUTER JOIN. As you'd expect this is simply the reverse of the
above, returning all rows from the table on the right side of the join, and
only matching rows from the table on the left side.

In the relationships window on the other hand if you right click on the join
line between two tables and then open the 'Edit Relationship' dialogue, this
includes a 'Join Type' button which opens the same dialogue as above. What
this does is set the default join type when you join the tables in a query to
whatever you select here. The options in the main 'Edit Relationship'
dialogue are threefold:

1. The 'Enforce Referential Integrity' checkbox creates a 'constraint'
which means that a row can only be inserted into the referencing table (many
side) where a matching row already exists in the referenced table (one side).
Say you have a table States and a table Cities, you can't insert a row in
Cities for San Francisco with a value CA in its State foreign key column
until a row for California with a key value CA has been inserted in States.
Conversely you can't delete a row from States if a row exists in Cities with
a matching State value. Enforcing referential integrity where its
appropriate is very important.

2. Cascade Deletes. This means that if you delete a row from a referenced
table (one side) all matching rows in any referencing tables will
automatically be deleted. USE THIS CAREFULLY! You would probably not want
to use it in a States--<Cities relationship for instance, but might well do
so in a Customers--<Contacts relationship, as if you delete a company from
your customers table there is no point retaining the records of contacts in
that company.

3. Cascade Updates. This means that if you change the value of a primary
key column in a row in a referenced table the values in any foreign key
columns in matching rows in referencing tables will automatically change. If
California for some reason changed from CA to CF then the State value in
Cities for San Francisco, Los Angeles, San Diego etc would also change. if
the primary key is an autonumber column StateID and the foreign key a
corresponding long integer number column, there is no point enforcing cascade
updates as the value of an autonumber can't be changed.

Relationships in the front end merely set the defaults for queries. All
constraints should be applied in the back end. I don't use SQL SERVER
myself, but in standard SQL this is done with the DDL 'ALTER TABLE ADD
CONSTRAINT' command on a existing tables, or as a 'CONSTRAINT <constraint
name> REFERENCES <referenced table (referenced column)>' specification when a
referencing table is created with DDL.

Ken Sheridan
Stafford, England