INNER JOINS in Access

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rick - 31 Jan 2006 12:27 GMT

Compared to SQL2000, Access has its own, original way to build the table
relationships in an SQL Statement.

I'm trying to build an application with Visual Basic 6 that shows some data
using DAO 3.6.

My problem is how can I find a methodology (not intuition) to build the
INNER JOIN statements. Why? Because the user is the one to pick the fields to
be shown on a grid and these fields may come from different tables with
different relationships.

Methodologically speaking, here there are several tables from which the user
might pick the fields, their relations are shown. How would you, experts in
Access 97(or XP), build the INNER JOIN section for the query(a SELECT
statement). Please, don't worry about what is in the SELECT part(fields), nor
the GROUPING section, nor the ORDER BY section. If you please, could you give
us a step by step method to build these JOINS? THis is no homework, I'm
struggling with so many cases that every time I pick a diffrent field I get
an Error in VB 6(DAO Object).

How can I use the MDB files to let the user pick whatever he wants (this is
the requirement for my project). Note: He is not supposed to use the query
builder, since he is not expected to have ACCESS installed, except for the
VB6 license.

Table1<-->Table2 Relationship:Table1ID
Table1<-->Table3 Relationship:Table1ID
Table3<-->Table2 Relationship:Table1ID AND Table3ID
Table4<-->Table2 Relationship:Table4ID
Table5<-->Table2 Relationship:Table5ID

Regards,

Signature

Rick

Reply to this Message

John Spencer - 31 Jan 2006 13:06 GMT

Rick,

You seem to be talking about JET database engine not Access.

I don't know if this will help but any INNER JOIN clause that works in MS
SQL should work in JET with the exception that you must use parentheses to
control the joins

FROM
(table1 INNER JOIN Table2 ON Table1.x = Table2.x)
INNER JOIN Table3 ON Table1.y=Table3.y

FROM
((table1 INNER JOIN Table2 ON Table1.x = Table2.x)
INNER JOIN Table3 ON Table1.y=Table3.y)
INNER JOIN table2 On Table3.z = Table2.z

FROM
(((table1 INNER JOIN Table2 ON Table1.x = Table2.x)
INNER JOIN Table3 ON Table1.y=Table3.y)
INNER JOIN table2 On Table3.z = Table2.z)
INNER JOIN Table4 ON Table1.x = table4.x

So each time you add a join and a table you need to add a open parentheses
at the beginning and a close parentheses before you add the next join. That
should work. Try it and let us know.

> Compared to SQL2000, Access has its own, original way to build the table
> relationships in an SQL Statement.
[quoted text clipped - 36 lines]
>
> Regards,

Reply to this Message

Rick - 31 Jan 2006 13:48 GMT

For instance, here you can see two other examples,
The first one is built on ACCESS Query builder and the second is the one the
one would normally write for SQL2000. Can you see the differences? How can I
deduct a rule for this?

--MS JET DATABASE Way
SELECT Table1.*, Table2.*, Table3.*, Table4.*
FROM Table1
INNER JOIN Table2 INNER JOIN (
Table3 INNER JOIN Table4 ON
Table3.Table3ID = Table4.Table3ID
) ON Table2.Table2ID = Table4.Table2ID
) ON Table1.Table1ID = Table4.Table1ID;

--MS SQL2000 way
SELECT Table1.*, Table2.*, Table3.*, Table4.*
FROM Table1
INNER JOIN Table4 ON Table1.Table1ID = Table4.Table1ID
INNER JOIN Table2 ON Table2.Table2ID = Table4.Table2ID
INNER JOIN Table3 ON Table3.Table3ID = Table4.Table3ID

Signature

Rick

Reply to this Message

John Spencer - 31 Jan 2006 16:26 GMT

Does the following query work with Access JET? Does it give you the same
results as the one you posted?

SELECT Table1.*, Table2.*, Table3.*, Table4.*
FROM ( (Table1 INNER JOIN Table4 ON Table1.Table1ID = Table4.Table1ID)
INNER JOIN Table2 ON Table2.Table2ID = Table4.Table2ID)
INNER JOIN Table3 ON Table3.Table3ID = Table4.Table3ID

Just because the Query Builder (the grid) builds the SQL in a certain way
does not mean that you have to follow the same path. If you need to do
OUTER JOINS (Left Joins and Right Joins) you could run into problems,
although the same technique would generally work.

> For instance, here you can see two other examples,
> The first one is built on ACCESS Query builder and the second is the one
[quoted text clipped - 18 lines]
> INNER JOIN Table2 ON Table2.Table2ID = Table4.Table2ID
> INNER JOIN Table3 ON Table3.Table3ID = Table4.Table3ID

Reply to this Message