Access 2010: Syntax error (missing operator) in query expression

Question

I want to have three tables in my sql query but I get an error message.

I have this sql code:

 SELECT warehouse.expiry_date, pharmacy.expiry_date, drugs.active_substance, 
 drugs.strength, drugs.strength_type, drugs.dosage_form, drugs.minimum_quantity, 
 SUM(warehouse.in_quant)+SUM(pharmacy.in_quant)-SUM(warehouse.out_quant)-
 SUM(pharmacy.out_quant) AS Total_available_stock

 FROM drugs as a 
  INNER JOIN warehouse as b
    ON a.ID = b.drug_id 
  INNER JOIN pharmacy as c 
    ON b.drug_id = c.drug_id
 GROUP BY warehouse.expiry_date, pharmacy.expiry_date, drugs.active_substance, 
 drugs.strength, drugs.strength_type, drugs.dosage_form, drugs.minimum_quantity;

And i get the error:

 Syntax error (missing operator) in query expression in 'a.ID = b.drug_id 
 INNER JOIN pharmacy as c
     on b.drug_id = c.drug_i'.

Any help please?

Guffa · Accepted Answer · 2012-06-01 14:53:09Z

49

In Access you need parentheses when you have more than one join:

FROM (drugs as a 
  INNER JOIN warehouse as b
    ON a.ID = b.drug_id) 
  INNER JOIN pharmacy as c 
    ON b.drug_id = c.drug_id

answered Jun 1, 2012 at 14:53

Guffa

703k111 gold badges760 silver badges1k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Joseph U. · Accepted Answer · 2017-07-20 17:06:19Z

6

Just wanted to add that it gets even more messy when you have more than 2 Inner Joins. Then you need to add an extra parentheses at the beginning and to the end of each of the Inner Joins (except the last one)

Example:

 FROM ((drugs as a 
  INNER JOIN warehouse as b
    ON a.ID = b.drug_id) 
  INNER JOIN pharmacy as c1 
    ON b.drug_id1 = c1.drug_id)
  INNER JOIN pharmacy as c2 
    ON b.drug_id2 = c2.drug_id

answered Jul 20, 2017 at 17:06

Joseph U.

4,62711 gold badges43 silver badges49 bronze badges

Collectives™ on Stack Overflow

Access 2010: Syntax error (missing operator) in query expression

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related