How to use several joins in one query?

[MIKQ]

Hi, I'm trying to do a query but somehow can't manage that stupid
access syntax.
While trying I created a query which reproducible crashes access!

What am I doing wrong?:

SELECT p.Problem, p.Problem_lang, l.Loesung, l.Loesung_lang,
h.Hersteller
FROM ((Problem AS p inner join Prob_Loes AS pl on p.ID_Problem =
pl.ID_Problem)
inner join Loesung AS l on pl.ID_Loesung = l.ID_Loesung)
left join Hersteller AS h on p.ID_Problem = h.ID_Problem;

 

[OfficeDev18 via AccessMonster.com]

Your SQL looks OK. Do you perhaps already have a relationship (in the
relationships window) between tables Problem and Hersteller?

Sam

 

[KARL DEWEY]

What am I doing wrong?
Why do you think you are doing something wrong? I built what I thought was
your table structure and the query runs ok.
Is this a total crash as in having to restart the program?

Post some sample data from your tables.

 

[MIKQ]

Oh dear, yes I do.
Isn't that the way you create a RDBMS?
For a n:n relation between two table (Problem and Loesung) I created an
intersection table (Prob_Loes)
The PK in Prob_Loes consists of the two PK of Problem and Loesung.
And I dragged the PK from Loesung (Field ID_Loesung) to the FK in
Prob_Loes (Field: ID_Loesung).
I did the same with Problem (Field ID_Problem).
To bad I can't post a screenshot here.


The Table "Hersteller" is connected in the same way. (Intersection
Table Prob_Hers).


So connecting these in Access AND doing a query connecting theses
tables is wrong?

Greetings
MIKQ

 

[MIKQ]

Is this a total crash as in having to restart the program?



YES

MIKQ

 

[OfficeDev18 via AccessMonster.com]

As Karl says, I don't think you're doing anything wrong per se, but the new
query might not like the new relationship you're giving it, because it
contradicts the established relationship.

What you might want to do is to copy the 4 tables, Problem, Prob_Loes,
Loesung, and Hersteller, and make sure there is no relationdhip in the
Relationships window for the new tables. If there is, delete it. Don't delete
the relationships for the original tables, though. Now, re-do the SQL
statement using the new tables and see if the query runs.

Hope this helps,

Sam

Is this a total crash as in having to restart the program?
YES

MIKQ


Why do you think you are doing something wrong? I built what I thought was

[quoted text clipped - 17 lines]

 

[MIKQ]

I deleted all relations in Access but that query still crashed access.


SELECT p.Problem, p.Problem_lang, l.Loesung, l.Loesung_lang,
h.Hersteller
FROM ((Problem AS p INNER JOIN Prob_Loes AS pl ON
p.ID_Problem=pl.ID_Problem) INNER JOIN Loesung AS l ON
pl.ID_Loesung=l.ID_Loesung) LEFT JOIN Hersteller AS h ON
h.ID_Problem=p.ID_Problem;



Then I changed my select to:

SELECT p.Problem, p.Problem_lang, l.Loesung, l.Loesung_lang,
h.Hersteller
FROM ((Problem AS p INNER JOIN Prob_Loes AS pl ON
p.ID_Problem=pl.ID_Problem) INNER JOIN Loesung AS l ON
pl.ID_Loesung=l.ID_Loesung) INNER JOIN Hersteller AS h ON
h.ID_Problem=p.ID_Problem;


That one works but returns no rows.
A "RIGHT JOIN" won't be recognized, the "LEFT JOIN" still let Access
2002 crash.


Do I have to have plenty of data in all tables.
I 'm using an intentionally a "LFET JOIN" because I expect that
some tables won't contain matching data.

Please help!

 

[OfficeDev18 via AccessMonster.com]

Try breaking it down and making subqueries in Access' query designer; you can
test each subquery seperately to make sure it's working. You may have to
tinker with it to get the correct levels of subqueries. Try the last join
first, as in:

SELECT p.FieldList, h.FieldList FROM Problem AS p LEFT JOIN Hersteller AS h
ON h.ID_Problem = p.ID_Problem;

To clarify things, you may want to add a WHERE clause, such as WHERE h.
ID_Problem Is Null (or Is Not Null, as the case may be.)

Of course, substitute the correct field names as needed. Now test the query
and see if it gives you the correct results. If it does, save the query -
you'll need to name it, such as qryProblemSub1.

Now make a new query using qryProblemSub1 as one 'table' , and do another
level, as in:

SELECT qryProblemSub1.* FROM qryProblemSub1 AS p INNER JOIN Prob_Loes AS pl
ON
p.ID_Problem=pl.ID_Problem;

Test the query, save it and name. Then do the last (highest) level to get the
fields you need.

If Access still crashes, I suspect there's something wrong with one or more
of the tables.

Hope this helps,

Sam

 

[MIKQ]

Dear Gary & OfficeDev18,


I figured that the table "Hersteller" is the Prob too.


As tables "Problem" and "Hersteller" are n:n connected there is
a intersection
table called "Prob_Hers". I followed OfficeDev18 suggestion and
broke my
statement into small pieces.

I tried to do a LEFT OUTER JOIN in a query that would show me all rows
from "Problem" (ignoring if there is a "Hersteller" connected
to it) but only the rows from "Hersteller" that are connected to a
"Problem".

The main idea is to show all rows from "Problem" and if there is
additional info in other tables as "Hersteller" ("Version"
etc.) this should be shown too. If there is no data in "Herstller"
("Version" etc.) everything from Problem should still show up!

To begin with I did this:

SELECT p.Problem, p.Problem_lang, h.Hersteller
FROM (Problem As p LEFT JOIN Prob_Hers AS ph ON
p.ID_Problem=ph.ID_Problem) INNER JOIN Hersteller AS h ON
h.ID_Hersteller=ph.ID_Hersteller;

And Access tells me "Verknüpfungsausdruck nicht unterstützt." In
English that should be something like: The connecting
expression/operator is unsupported/wrong.

Am I doing a stupid mistake, or even some serious design error? My main
idea can't be so hard, can it?

 

[MIKQ]

I googled an found the error message in English:
It says: Error 3296 "Join expression not supported (4)"

Hope that makes it easier to help me....

MIKQ

 

[MIKQ]

you just cannot nest inner joins
within an outer join....

save the inner join part as a separate
query, then left join to that query....

that way Access won't get confused...

Could you please explain? Is it possible to do this in a single query
or do I have to buffer the result somerwhere? If yes: How?