Inner Joins with multiple tables

[jaybee3]

Hi All,

I'm trying to wrap my head around the syntax for Access SQL. I'm pretty sure this would normally work, I've previously only dabbled with Oracle. I read something about parentheses with Access joins but the placement of them evades me. Any help would be much appreciated.

SELECT cu.CustName, v.Date,v.Rate,v1.Date,v1.Rate</SPAN></SPAN>
FROM Value v </SPAN></SPAN>
INNER JOIN Value v1 </SPAN></SPAN>
ON v1.ID = v.ID</SPAN></SPAN>
  INNER JOIN Class c </SPAN></SPAN>
  ON c.ID = v.ID </SPAN></SPAN>
    INNER JOIN Subject s </SPAN></SPAN>
    ON s.Sub = c.Sub </SPAN></SPAN>
      INNER JOIN Customers cu </SPAN></SPAN>
      ON cu.CustNumber = s.CustNumber</SPAN></SPAN>
WHERE v.date = #02/04/2013# and v1.date = #28/03/2013# and cu.custnumber = 19;</SPAN></SPAN>

 

[Joe4]

Parentheses may be helpful in nesting Inner Joins (if nothing else, for readability), but I do not think they are necessary. I usually don't use them in my Inner Joins, and they seem to work fine.

What happens when you try?
Are you getting errors or unexpected results?

 

[jaybee3]

Cheers for the quick response.

It won't run, I'm getting the following error popping up

I'm getting "Syntax error (missing operator) in query expression 'v1.ID= v.ID ....


Solved the problem: For every inner join i needed a parenthesis before the initial table and one after each join:

...
FROM ((((Value v </SPAN></SPAN>
INNER JOIN Value v1 </SPAN></SPAN>
ON v1.ID = v.ID</SPAN></SPAN> )
INNER JOIN Class c </SPAN></SPAN>
  ON c.ID = v.ID )</SPAN></SPAN>
    INNER JOIN Subject s </SPAN></SPAN>
    ON s.Sub = c.Sub )</SPAN></SPAN>
      INNER JOIN Customers cu )</SPAN></SPAN>
      ON cu.CustNumber = s.CustNumber
...

</SPAN></SPAN>

 

[Joe4]

Odd. I have never had to do that before. But if you got it to work out, that's all the matters!