Multiple INNER JOIN SQL ACCESS

Syntax Error (missing Operator) in query expression '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'.

SELECT tbl_employee.emp_ID,
tbl_employee.emp_name,
tbl_gross.BasicSalary,
tbl_gross.totalOT,
tbl_netpay.totalGross,
tbl_tax.totalLate,
tbl_tax.allowance,
tbl_tax.SSS,
tbl_tax.PhilHealth,
tbl_tax.GSIS,
tbl_tax.HDMF,
tbl_netpay.totalDeduc,
tbl_netpay.emp_ti,
tbl_netpay.emp_wt,
tbl_netpay.emp_np
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;

I always get the error above.

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 ClassRepresentativ2nd
  FROM
       ((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 ClassRepresentativ2nd
  FROM person AS cr
  INNER 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 ClassRepresentativ2nd
  FROM class, person AS cr, person AS cr2
  WHERE class.C_P_ClassRep=cr.P_Nr AND class.C_P_ClassRep2nd=cr2.P_Nr
  ;