Multiple INNER JOIN SQL ACCESS

Access requires parentheses in the FROM clause for queries which include more than one join. Try it this way ...

FROM    ((tbl_employee    INNER JOIN tbl_netpay    ON tbl_employee.emp_id = tbl_netpay.emp_id)    INNER JOIN tbl_gross    ON tbl_employee.emp_id = tbl_gross.emp_ID)    INNER JOIN tbl_tax    ON tbl_employee.emp_id = tbl_tax.emp_ID;

If possible, use the Access query designer to set up your joins. The designer will add parentheses as required to keep the db engine happy.

Thanks HansUp for your answer, it is very helpful and it works!

I found three patterns working in Access, yours is the best, because it works in all cases.

• INNER JOIN, your variant. I will call it "closed set pattern".It is possible to join more than two tables to the same table with good performance only with this pattern.

  SELECT C_Name, cr.P_FirstName+" "+cr.P_SurName AS ClassRepresentativ, cr2.P_FirstName+" "+cr2.P_SurName AS ClassRepresentativ2ndFROM     ((class       INNER JOIN person AS cr        ON class.C_P_ClassRep=cr.P_Nr     )     INNER JOIN person AS cr2     ON class.C_P_ClassRep2nd=cr2.P_Nr  )

  ;

• INNER JOIN "chained-set pattern"

  SELECT C_Name, cr.P_FirstName+" "+cr.P_SurName AS ClassRepresentativ, cr2.P_FirstName+" "+cr2.P_SurName AS ClassRepresentativ2ndFROM person AS crINNER JOIN ( class    INNER JOIN ( person AS cr2   ) ON class.C_P_ClassRep2nd=cr2.P_Nr) ON class.C_P_ClassRep=cr.P_Nr;

• CROSS JOIN with WHERE

  SELECT C_Name, cr.P_FirstName+" "+cr.P_SurName AS ClassRepresentativ, cr2.P_FirstName+" "+cr2.P_SurName AS ClassRepresentativ2ndFROM class, person AS cr, person AS cr2WHERE class.C_P_ClassRep=cr.P_Nr AND class.C_P_ClassRep2nd=cr2.P_Nr;