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: https://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, May 07 '15 at 19:42
I think that's also necessary, however that alone didn't fix it :( — hypetech, May 07 '15 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](http://stackoverflow.com/q/20929332/77335) Beware Access can be finicky with outer joins, so adding parentheses may give you a *different* error. — HansUp, May 07 '15 at 19:49

Sagar Joon · Accepted Answer · 2015-05-07T20:14:25.257

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

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, May 07 '15 at 20:06
And if I try to just view, it asks for a parameter value for m.EMeter — hypetech, May 07 '15 at 20:07

Syntax Error in SQL Query With Switch and Join Statements

1 Answers1