Syntax Error in SQL Query With Switch and Join Statements

Question

This is my SQL Query

SELECT m.Bldg, 
SUM(e.TotCost * SWITCH(e.Utility='E', 1, true, 0)) AS ECost, 
SUM(g.TotCost * SWITCH(e.Utility='G', 1, true, 0)) AS GCost, 
SUM(h.TotCost * SWITCH(e.Utility='H', 1, true, 0)) AS HCost, 
SUM(c.TotCost * SWITCH(e.Utility='C', 1, true, 0)) AS CCost, 
SUM(w.TotCost * SWITCH(e.Utility='W', 1, true, 0)) AS WCost, 
SUM(s.TotCost * SWITCH(e.Utility='S', 1, true, 0)) AS SCost
FROM tblBldgMeters m
LEFT JOIN qryMtrHistory e ON m.EMeter=e.Meter
LEFT JOIN qryMtrHistory g ON m.GMeter=g.Meter
LEFT JOIN qryMtrHistory h ON m.HMeter=h.Meter
LEFT JOIN qryMtrHistory c ON m.CMeter=C.Meter
LEFT JOIN qryMtrHistory w ON m.WMeter=W.Meter
LEFT JOIN qryMtrHistory s ON m.SMeter=S.Meter
GROUP BY m.Bldg

I am getting this error message: enter image description here

When I get out of the message, this part is highlighted "UM(e." from the very first line of the query. I think the error is that I need more parentheses around the LEFT JOINS, but I'm not sure how to format them. If anyone can help, I would appreciate it. I'm assuming Access is throwing me an error about me trying something that is legal in SQL but not in access, but I'm not sure how to fix it. Thank you!

According to this: msdn.microsoft.com/en-us/library/bb208894%28v=office.12%29.aspx Access doesn't have explicit "OUTER" keyword. "LEFT JOIN" is implicitly an outer join. Could you try to drop "OUTER" from your query? — Alex Napitupulu
– Alex Napitupulu, Commented May 7, 2015 at 19:42
I think that's also necessary, however that alone didn't fix it :( — hypetech
– hypetech, Commented May 7, 2015 at 19:44
LEFT OUTER JOIN vs. LEFT JOIN is not a problem; Access accepts either. You need parentheses in the FROM clause. See Multiple INNER JOIN SQL ACCESS Beware Access can be finicky with outer joins, so adding parentheses may give you a different error. — HansUp
– HansUp, Commented May 7, 2015 at 19:49

Sagar Joon · Accepted Answer · 2015-05-07 20:14:25Z

3

Try this(EDITED), using parenthesis :

 SELECT m.Bldg, 
 SUM(e.TotCost * SWITCH(e.Utility='E', 1, true, 0)) AS ECost, 
 SUM(g.TotCost * SWITCH(e.Utility='G', 1, true, 0)) AS GCost, 
 SUM(h.TotCost * SWITCH(e.Utility='H', 1, true, 0)) AS HCost, 
 SUM(c.TotCost * SWITCH(e.Utility='C', 1, true, 0)) AS CCost, 
 SUM(w.TotCost * SWITCH(e.Utility='W', 1, true, 0)) AS WCost, 
 SUM(s.TotCost * SWITCH(e.Utility='S', 1, true, 0)) AS SCost
 FROM 
 ((((
 (tblBldgMeters m LEFT JOIN qryMtrHistory e ON (m.EMeter=e.Meter) )
   LEFT JOIN qryMtrHistory g ON (m.GMeter=g.Meter)
  )
    LEFT  JOIN qryMtrHistory h ON (m.HMeter=h.Meter)
  )
    LEFT JOIN qryMtrHistory c ON (m.CMeter=C.Meter)
  )
    LEFT JOIN qryMtrHistory w ON (m.WMeter=W.Meter)
  )
    LEFT JOIN qryMtrHistory s ON (m.SMeter=S.Meter)

  GROUP BY m.Bldg ;

Cause of the problem was :

The Simple Query Wizard adds an extra set of parentheses in the join expression of the SQL statement that it creates for the query.

The error occurs if your query meets both of the following conditions: If your query is based on two or more tables. OR If two of the tables contain a relationship or join based on a three-field primary key.

Take a look at this : https://support.microsoft.com/en-us/kb/207868

edited May 7, 2015 at 20:14

answered May 7, 2015 at 19:48

Sagar Joon

1,41715 silver badges23 bronze badges

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

2 Comments

hypetech Over a year ago

When I try running that, it processes for a second like it's going to work, but then gives the error "Microsoft Access can't represent the join expression m.EMeter=e.Meter in Design view

hypetech Over a year ago

And if I try to just view, it asks for a parameter value for m.EMeter

Collectives™ on Stack Overflow

Syntax Error in SQL Query With Switch and Join Statements

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related