I am often required to design queries involving two or more tables and
linking them together. However, in linking them together, i am often
forced to define the relationship (join properties) between these
tables and I often get it wrong. As a result, i get duplicate records
all thetime. Can anyone explain to me how does one-to-many and many-
to-many relationship really works in Access queries and how are they
being retrieved. And how can i easily identify and fix this probem.
Plese give some examples.
I went on an Access course some <coughs> years ago and this really
confused the &%@£ out of me, too; about six month later, I'm at a
client's site configuring some tables in SQL Server and I suddenly had
this Eureka moment and realized what the teacher was trying to
convey...
I think the problem is there are two concepts that get merged into
one. Consider your SQL DLL (your schema i.e. tables) and your SQL DML
(how you use those tables to manage data e.g. returning a resultset of
data).
In SQL DDL, you can create a FOREIGN KEY so that one table REFERENCES
another.
In SQL DML. you can create a JOIN between two tables. IIRC the three
JOIN types supported in the query builder tool thing are INNER JOIN,
LEFT JOIN and RIGHT JOIN.
[
FWIW something I realized quickly but took a while to confirm is that
Table1 LEFT JOIN Table2 ON <join condition>
is logically equivalent to
Table2 RIGHT JOIN Table1 ON <join condition>
]
It is usually the case that the columns used in REFERENCES in the SQL
DDL are the ones that will be used to created table joins in the SQL
DML (the query builder will assume this by default) but it doesn't
have to be this way.
My suggestion would be to research the SQL keywords (uppcase) I've
mentioned above and learn how to write simple queries including JOINs
using SQL e.g. try
http://www.sqlcourse.com/.
Bear in mind that the query builder tool is writing SQL and to get the
most out of it you will do well to understand the basic format of a
query (though you may find that once you know how you will prefer to
write your SQL queries yourself <g>). Also, Access uses the term
'Relationships' for FOREIGN KEYs although the Access definition is
looser by design.
Some hints:
"Here is how a SELECT works in SQL ... at least in theory..."
http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/799592725f2d4685
"Here is how OUTER JOINs work in SQL-92..."
http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/e5771ad2197c3117
Jamie.
--
