Hello,
To my understanding the relationship type (one to many, one to one, many to
many) should always be the same between the table and the query.
My question is what about the the join type ( right join and left join)
should it be the same in table and query?
This stuff really confused me when I first used the Access user
interface and the following [long post] is what I wish someone had
told *me* at the time.
The 'Relationships' database tool in the Access interface may be used
to create two distinct kinds of object and in different layers of your
application (I'm assuming the 'traditional' Access+Jet .mdb
application here):
1) a 'Relationship' object in the *Access* layer;
2) a foreign key (FK) object between base tables in the *Jet* layer.
To create a FK via the Relationships designer you must check the
'Enforce Referential Integrity' box in the 'Edit Relationship' dialog
(opened by double-clicking the arrow between two tables in the
diagram).
Jet FKs behave pretty much like FKs in any other SQL DBMS, so do some
googling if you are unfamiliar with the concept (note there are Jet-
specific issues e.g. the semantics of the NULL value in FKs are non-
standard in Jet). A FK can only be created between base tables.
[BTW when I say 'SQL' I mean the SQL language and put SQL keywords
(e.g. INNER JOIN) in upper case. I mention this because some people
use 'SQL' to mean 'SQL DBMS' and some people even use 'SQL' to mean
'Microsoft SQL Server; also, so you don't think I'm shouting "INNER
JOIN!" at you <g>.]
An Access Relationship is a different animal entirely and it's *only*
use is in the Access user interface's Query designer. Whereas an FK is
limited to Jet base tables, an Access Relationship can be created
between logical tables e.g. base tables, viewed tables, linked tables,
etc.
Say you have two tables, TableA and TableB, and you create an Access
Relationship between them choosing for the join type, say, the first
option, "Only include rows where the joined fields from both tables
are equal." which translates to INNER JOIN in the SQL language. The
idea is that if you subsequently use the Access user interface to
create a Query object, you can simply drop TableA and TableB into the
Query designer ('Add Tables') and the SQL JOIN clause will
automatically be written for you, in this case using the INNER JOIN
syntax. If you alter the join type via Relationships window and create
a new Access Query using the same tables then the auto-generated SQL
JOIN will be of the new JOIN type.
The Access Query designer is to help users by writing their SQL code
for them. Arguably, if you can write SQL and enjoy doing so then the
Access Query designer is best avoided. For example, the Access
Relationship join types are limited to INNER JOIN, LEFT OUTER JOIN and
RIGHT OUTER JOIN where the join predicate is equality, whereas using
SQL many more joins are possible (these seem to be collectively
referred to in the Access community as 'non-equi-joins'). Also, it is
possible to use the SQL View of an Access Query object to write SQL
DDL (data definition language e.g. CREATE TABLE, ALTER TABLE ADD
CONSTRAINT, CREATE PROCEDURE, etc) and SQL DCL (data control language
e.g. CREATE USER, GRANT, REVOKE, BEGIN TRNASACTION, ROLLABCK
TRNASACTION, etc), whereas the designer can only auto-generate a
subset of SQL DML (data modification language e.g. SELECT, INSERT,
UPDATE, DELETE, etc). The auto-generated SQL code does not always
resemble the code a human would write e.g. the former tends to contain
superfluous brackets and parentheses and other constructs that makes
the code harder to read IMO.
Note that strictly speaking there is no such *object* as an 'Access
Query' because what actually happens is that the Access Query
*designer* is used to create an object in the Jet layer e.g. an Access
Query comprising a SELECT query will create a VIEW (logical viewed
table) or a PROCEDURE (logical stored proc) in the Jet layer depending
on the SQL code. Conversely, when viewing the structure of a VIEW or a
PROCEDURE in Access, the user interface does not discriminate between
them and lumps them together as 'Query'.
When I started using Access I was at a distinct *disadvantage* because
I already knew how to write SQL
I went on a course to (supposedly) learn how to use Access and, as I
mentioned, came away thoroughly confused because the Access interface
does a 'good' job (as did my trainer!) of isolating me, the user, from
SQL. Several months later I was doing an installation of SQL Server at
a client's site and running a SQL DDL script to create tables with
constraints and I sudden had the epiphany: the trainer had been
explaining FKs without once using the term 'foreign key'!
To recap: an Access Relationship pertains to the Access interface and
is all about auto-generating simple SQL code via the Query designer.
An Access Relationship With Referential Integrity Enforced pertains to
FKs in the Jet layer and is all about enforcing data integrity via
table constraints.
What can be confusing IMO is that folk in the Access community seem to
use the term 'relationship' (lowercase) to mean 'An Access
Relationship With Referential Integrity Enforced' or FOREIGN KEY i.e.
there is an assumption that the 'Enforce Referential Integrity' will
be checked, even though this isn't implied or even checked by default
(IIRC) in the user interface. I think the reason for this is that
experienced Access users have conclude that an Access Relationship
without referential integrity (i.e. no FK) is not really worth
bothering with, so they assume that everyone thinks the same way. I
guess it can be hard for newbies to grasp this.
[Some folk even use the term 'relation' to mean FK. Someone with any
kind of knowledge of the Relational Model (which includes many SQL
coders, naturally) is likely to find this confusing because for them
'relation' in RM most closely resembles 'table' in SQL. The source of
the misnomer, I think, is the DAO library, in which the collection
class for Access Relationships is named 'Relations', presumably a
contraction of 'Relationships'.]
Jamie.
--
