Multiple joins with multiple criteria

[Guest]

Lets start with the query:

1. SELECT *
2. FROM ( ( ( Plaatsingen
3. RIGHT JOIN ( Afdelingen
4. LEFT JOIN [Stagesoorten per instelling]
5. ON [Stagesoorten per instelling].[Afdeling ID]
= Afdelingen.[Afdeling ID]
6. )
7. ON ( Plaatsingen.[Stagesoort ID] = [Stagesoorten per
instelling].[Stagesoort ID] ) AND
8. ( Plaatsingen.[Afdeling ID] = [Stagesoorten per
instelling].[Afdeling ID] ) AND
9. ( Plaatsingen.[Periode ID] = [Stagesoorten per
instelling].[Periode ID] ) )
10. LEFT JOIN Instellingen ON Instellingen.[Instelling ID] =
Afdelingen.[Instelling ID] )
11. LEFT JOIN Contactpersonen ON ( Contactpersonen.[Afdeling ID] =
Afdelingen.[Afdeling ID] ) AND
12. ( Contactpersonen.[Stagesoort ID] =
[Stagesoorten per instelling].[Stagesoort ID] ) )
13. ORDER BY Afdelingen.[Afdeling ID];




The problem is the second criteria in the join on table "contactpersonen"
(line 12). When I remove that line (and delete AND, fix the numer of
brackets, etc.), everything works great. But when I put line 12 back in the
query, it gives me the error:
Join-expression not supported

In the join on table "plaatsingen" (line 7 - 9), the join can work with
multiple criteria, even though the structure of the joins is the same.

I think, it's a incorrect-number-of-brackets-problem, but since I've tried
all kind of bracket-combinations it still doesn't work. Somebody ideas?!?!?
Thanks!

 

[John Viescas]

You can define a JOIN only between two tables - so any criteria in the ON
clause must all use the same two tables. Your line 12 adds a third table:

LEFT JOIN Contactpersonen
ON ( Contactpersonen.[Afdeling ID] =
Afdelingen.[Afdeling ID] )
AND ( Contactpersonen.[Stagesoort ID] =
[Stagesoorten per instelling].[Stagesoort ID] ) )

The ON clause makes reference to tables Contactpersonen, Afdelingen, and
[Stagesoorten per instelling].

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

 

[Guest]

Ah, now that's something I didn't know. Thanks! I tested some changes and
they work ... You just saved me a very big headache...

You can define a JOIN only between two tables - so any criteria in the ON
clause must all use the same two tables. Your line 12 adds a third table:

LEFT JOIN Contactpersonen
ON ( Contactpersonen.[Afdeling ID] =
Afdelingen.[Afdeling ID] )
AND ( Contactpersonen.[Stagesoort ID] =
[Stagesoorten per instelling].[Stagesoort ID] ) )

The ON clause makes reference to tables Contactpersonen, Afdelingen, and
[Stagesoorten per instelling].

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

Lets start with the query:

1. SELECT *
2. FROM ( ( ( Plaatsingen
3. RIGHT JOIN ( Afdelingen
4. LEFT JOIN [Stagesoorten per instelling]
5. ON [Stagesoorten per instelling].[Afdeling ID]
= Afdelingen.[Afdeling ID]
6. )
7. ON ( Plaatsingen.[Stagesoort ID] = [Stagesoorten per
instelling].[Stagesoort ID] ) AND
8. ( Plaatsingen.[Afdeling ID] = [Stagesoorten per
instelling].[Afdeling ID] ) AND
9. ( Plaatsingen.[Periode ID] = [Stagesoorten per
instelling].[Periode ID] ) )
10. LEFT JOIN Instellingen ON Instellingen.[Instelling ID] =
Afdelingen.[Instelling ID] )
11. LEFT JOIN Contactpersonen ON ( Contactpersonen.[Afdeling ID]
=
Afdelingen.[Afdeling ID] ) AND
12. ( Contactpersonen.[Stagesoort ID]
=
[Stagesoorten per instelling].[Stagesoort ID] ) )
13. ORDER BY Afdelingen.[Afdeling ID];




The problem is the second criteria in the join on table "contactpersonen"
(line 12). When I remove that line (and delete AND, fix the numer of
brackets, etc.), everything works great. But when I put line 12 back in
the
query, it gives me the error:
Join-expression not supported

In the join on table "plaatsingen" (line 7 - 9), the join can work with
multiple criteria, even though the structure of the joins is the same.

I think, it's a incorrect-number-of-brackets-problem, but since I've tried
all kind of bracket-combinations it still doesn't work. Somebody
ideas?!?!?
Thanks!