Is this multiple join possible?

[zkarj]

Firstly, I was surprised that Access does not seem to allow mutliple joins without nesting. I've used other SQL engines which allow a discrete set of joins to be specified. Anyway, I think this makes it harder, but it should still be doable.

I have the following tables and relationships that I am wanting to exploit.

AP relates 1-to-many to AI.
AI relates 1-to-many to AU.
AU relates to AY 1-to-<none or one>.

I want to find entries in AI that have multiple distinct AU entries. Specifically I want to show each pair of conflicting AU entries (using the same AI), plus I want to include info for each of the AU entries from the related AY. The AP is needed for the description of that entity, but does not otherwise feature.

Here's the SQL I came up with, which is syntactically correct but "unsupported".

SELECT AP.Application, AI.Server, AI.Instance, AI.Status, AU1.Project, AU1.Role, AY1.Activity, AU2.Project, AU2.Role, AY2.Activity
FROM Application AS AP, [Application Instances] AS AI, [Application Use] AS AU1, Activity AS AY1, [Application Use] AS AU2, Activity as AY2,

AP INNER JOIN
   (AI INNER JOIN
      ((AU1 LEFT JOIN AY1 ON AY1.Project = AU1.Project AND AY1.Role = AU1.Role)   INNER JOIN
         (AU2 LEFT JOIN AY2 ON AY2.Project = AU2.Project AND AY2.Role = AU2.Role)
         ON AU2.ID = AU1.ID)
      ON AU1.ID = AI.[Application Instance ID])
  ON AP.Application = AI.Application

WHERE AU1.Project <> AU2.Project OR AU1.Role <> AU2.Role;

The "AS" clauses rename the files to the letter scheme I used to explain above, but with a "1" or "2" suffix for each side of the pairing.

Can anybody suggest an alternate way of doing this, or if I have something wrong in the statement?

 

[zkarj]

OK, after much playing, this works:

SELECT AI.Application, AI.Server, AI.Instance, AI.Status, AU1.Project, AU1.Role, AY1.Activity, AU2.Project, AU2.Role, AY2.Activity, AU1.[Application Instance ID]
FROM ((([Application Use] AS AU1 INNER JOIN [Application Use] AS AU2 ON AU1.[Application Instance ID]=AU2.[Application Instance ID]) INNER JOIN [Application Instances] AS AI ON AU1.[Application Instance ID]=AI.ID) LEFT JOIN Activity AS AY1 ON (AU1.Project=AY1.Project) AND (AY1.Role=AU1.Role)) LEFT JOIN Activity AS AY2 ON (AU2.Project=AY2.Project) AND (AY2.Role=AU2.Role)
WHERE (((AU1.Project & AU1.Role)>(AU2.Project & AU2.Role)));

Don't quite know why. Both statements make sense to me. One works, one doesn't.