Re: INNER JOINS in Access
- From: "John Spencer" <spencer@xxxxxxxxx>
- Date: Tue, 31 Jan 2006 08:06:31 -0500
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.
"Rick" <Rick@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E4A0B402-E4A9-4DA6-9CF8-347BC7A6DFB6@xxxxxxxxxxxxxxxx
> 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,
>
> --
> Rick
.
- References:
- INNER JOINS in Access
- From: Rick
- INNER JOINS in Access
- Prev by Date: Re: fees
- Next by Date: Re: Combining tables from two copies of DB
- Previous by thread: INNER JOINS in Access
- Next by thread: Re: INNER JOINS in Access
- Index(es):
Relevant Pages
|
