title: syntax error (missing operator) in query expression in MS Access
body: <p>I'm getting the error:</p>
  
  <pre><code>syntax error (missing operator) in query expression '((dbo_tblBidder.bidder_sale_id = dbo_tblSale.sale_id) AND (dbo_tblSale.sale_id IN (319))) LEFT JOIN
                    dbo_tblItem ON ((dbo_tblBidder.bidder_sale_id = dbo_tblItem.item_sale_id) AND (dbo_tblBidder.bidder_number = dbo_tblItem.item_bidder_number)'.
  </code></pre>
  
  <p>I've had this issue before and know that it doesn't like my parenthesis setup, but nothing I do seems to make MS Access happy.</p>
  
  <pre><code>SELECT     dbo_tblMailList.mail_FirstName, dbo_tblMailList.mail_LastName, dbo_tblMailList.mail_Address1, dbo_tblMailList.mail_Address2, 
                        dbo_tblMailList.mail_City, dbo_tblMailList.mail_State, dbo_tblMailList.mail_Zip, dbo_tblMailList.mail_Phone1, dbo_tblMailList.mail_Email1, 
                        dbo_tblMailList.mail_Comp_GenAm, dbo_tblBidder.bidder_number, SUM(dbo_tblItem.item_pr) AS SumOfitem_pr, SUM(dbo_tblItem.item_premium) 
                        AS SumOfitem_premium, dbo_tblBidder.bidder_type, dbo_tblSale.sale_id
  FROM         (dbo_tblMailList LEFT JOIN
                        dbo_tblBidder ON ((dbo_tblMailList.mail_ID = dbo_tblBidder.bidder_mail_id) AND (dbo_tblBidder.bidder_sale_id IN (319)))) LEFT JOIN
                        dbo_tblSale ON ((dbo_tblBidder.bidder_sale_id = dbo_tblSale.sale_id) AND (dbo_tblSale.sale_id IN (319))) LEFT JOIN
                        dbo_tblItem ON ((dbo_tblBidder.bidder_sale_id = dbo_tblItem.item_sale_id) AND (dbo_tblBidder.bidder_number = dbo_tblItem.item_bidder_number))
  GROUP BY dbo_tblMailList.mail_FirstName, dbo_tblMailList.mail_LastName, dbo_tblMailList.mail_Address1, dbo_tblMailList.mail_Address2, 
                        dbo_tblMailList.mail_City, dbo_tblMailList.mail_State, dbo_tblMailList.mail_Zip, dbo_tblMailList.mail_Phone1, dbo_tblMailList.mail_Email1, 
                        dbo_tblMailList.mail_Comp_GenAm, dbo_tblBidder.bidder_number, dbo_tblBidder.bidder_type, dbo_tblMailList.mail_Comp_Art, 
                        dbo_tblMailList.mail_Comp_IndArt, dbo_tblMailList.mail_Comp_Fire, dbo_tblMailList.mail_Comp_Ceramic, dbo_tblSale.sale_id
  HAVING      (dbo_tblMailList.mail_Comp_GenAm = 1)
  </code></pre>
  
  <p>If anyone has any ideas, please let me know.</p>
  
  <p>Thanks,
  James</p>
  
  <p><strong>EDIT</strong></p>
  
  <p>Using Gareth's <code>FROM</code> clause below I now have:</p>
  
  <pre><code>SELECT     dbo_tblMailList.mail_FirstName, dbo_tblMailList.mail_LastName, dbo_tblMailList.mail_Address1, dbo_tblMailList.mail_Address2, 
                        dbo_tblMailList.mail_City, dbo_tblMailList.mail_State, dbo_tblMailList.mail_Zip, dbo_tblMailList.mail_Phone1, dbo_tblMailList.mail_Email1, 
                        dbo_tblMailList.mail_Comp_GenAm, dbo_tblBidder.bidder_number, SUM(dbo_tblItem.item_pr) AS SumOfitem_pr, SUM(dbo_tblItem.item_premium) 
                        AS SumOfitem_premium, dbo_tblBidder.bidder_type, dbo_tblSale.sale_id
  FROM    (
              (   dbo_tblMailList 
                  LEFT JOIN dbo_tblBidder 
                      ON dbo_tblMailList.mail_ID = dbo_tblBidder.bidder_mail_id 
                      AND dbo_tblBidder.bidder_sale_id IN (319)
              )
              LEFT JOIN dbo_tblSale 
                  ON dbo_tblBidder.bidder_sale_id = dbo_tblSale.sale_id
          )
          LEFT JOIN dbo_tblItem
              ON dbo_tblBidder.bidder_sale_id = dbo_tblItem.item_sale_id 
              AND dbo_tblBidder.bidder_number = dbo_tblItem.item_bidder_number
  GROUP BY dbo_tblMailList.mail_FirstName, dbo_tblMailList.mail_LastName, dbo_tblMailList.mail_Address1, dbo_tblMailList.mail_Address2, 
                        dbo_tblMailList.mail_City, dbo_tblMailList.mail_State, dbo_tblMailList.mail_Zip, dbo_tblMailList.mail_Phone1, dbo_tblMailList.mail_Email1, 
                        dbo_tblMailList.mail_Comp_GenAm, dbo_tblBidder.bidder_number, dbo_tblBidder.bidder_type, dbo_tblMailList.mail_Comp_Art, 
                        dbo_tblMailList.mail_Comp_IndArt, dbo_tblMailList.mail_Comp_Fire, dbo_tblMailList.mail_Comp_Ceramic, dbo_tblSale.sale_id
  HAVING      (dbo_tblMailList.mail_Comp_GenAm = 1)
  </code></pre>
  
  <p>But I am getting the error <code>Extra ) in query expression ''.</code></p>
  
  <p>I think it may have something to do with <code>AND dbo_tblBidder.bidder_sale_id IN (319)</code>.  If I change this to <code>AND dbo_tblBidder.bidder_sale_id = 319</code>, I get the error:</p>
  
  <p><code>JOIN expression not supported.</code></p>
  
  <p>And it highlights the <code>dbo_tblBidder.bidder_sale_id = 319</code>.</p>
  
  <p><strong>CONCLUSION</strong></p>
  
  <p>Here is my final working code:</p>
  
  <pre><code>SELECT     mail_ID, mail_FirstName, mail_LastName, mail_Address1, mail_Address2, 
                        mail_City, mail_State, mail_Zip, mail_Phone1, mail_Email1, 
                        mail_Comp_GenAm, SUM(SumOfitem_pr) AS SumOfitem_price, SUM(SumOfitem_premium) AS SumOfitem_premiums
  FROM (
      SELECT     dbo_tblMailList.mail_ID, dbo_tblMailList.mail_FirstName, dbo_tblMailList.mail_LastName, dbo_tblMailList.mail_Address1, dbo_tblMailList.mail_Address2, 
                            dbo_tblMailList.mail_City, dbo_tblMailList.mail_State, dbo_tblMailList.mail_Zip, dbo_tblMailList.mail_Phone1, dbo_tblMailList.mail_Email1, 
                            dbo_tblMailList.mail_Comp_GenAm, SUM(dbo_tblItem.item_pr) AS SumOfitem_pr, SUM(dbo_tblItem.item_premium) 
                            AS SumOfitem_premium
      FROM    (
                  (   dbo_tblMailList 
                      LEFT JOIN dbo_tblBidder 
                          ON dbo_tblMailList.mail_ID = dbo_tblBidder.bidder_mail_id 
                  )
                  LEFT JOIN dbo_tblSale 
                      ON dbo_tblBidder.bidder_sale_id = dbo_tblSale.sale_id
              )
              LEFT JOIN dbo_tblItem
                  ON dbo_tblBidder.bidder_sale_id = dbo_tblItem.item_sale_id 
                  AND dbo_tblBidder.bidder_number = dbo_tblItem.item_bidder_number
      WHERE      dbo_tblMailList.mail_Comp_GenAm = 1 AND dbo_tblBidder.bidder_sale_id IN (319)
      GROUP BY dbo_tblMailList.mail_ID, dbo_tblMailList.mail_FirstName, dbo_tblMailList.mail_LastName, dbo_tblMailList.mail_Address1, dbo_tblMailList.mail_Address2, 
                            dbo_tblMailList.mail_City, dbo_tblMailList.mail_State, dbo_tblMailList.mail_Zip, dbo_tblMailList.mail_Phone1, dbo_tblMailList.mail_Email1, 
                            dbo_tblMailList.mail_Comp_GenAm, dbo_tblMailList.mail_Comp_Art, 
                            dbo_tblMailList.mail_Comp_IndArt, dbo_tblMailList.mail_Comp_Fire, dbo_tblMailList.mail_Comp_Ceramic
      UNION
      SELECT     dbo_tblMailList.mail_ID, dbo_tblMailList.mail_FirstName, dbo_tblMailList.mail_LastName, dbo_tblMailList.mail_Address1, dbo_tblMailList.mail_Address2, 
                            dbo_tblMailList.mail_City, dbo_tblMailList.mail_State, dbo_tblMailList.mail_Zip, dbo_tblMailList.mail_Phone1, dbo_tblMailList.mail_Email1, 
                            dbo_tblMailList.mail_Comp_GenAm, 0 AS SumOfitem_pr, 0 
                            AS SumOfitem_premium
      FROM    (
                  (   dbo_tblMailList 
                      LEFT JOIN dbo_tblBidder 
                          ON dbo_tblMailList.mail_ID = dbo_tblBidder.bidder_mail_id 
                  )
                  LEFT JOIN dbo_tblSale 
                      ON dbo_tblBidder.bidder_sale_id = dbo_tblSale.sale_id
              )
              LEFT JOIN dbo_tblItem
                  ON dbo_tblBidder.bidder_sale_id = dbo_tblItem.item_sale_id 
                  AND dbo_tblBidder.bidder_number = dbo_tblItem.item_bidder_number
      WHERE      dbo_tblMailList.mail_Comp_GenAm = 1
      GROUP BY dbo_tblMailList.mail_ID, dbo_tblMailList.mail_FirstName, dbo_tblMailList.mail_LastName, dbo_tblMailList.mail_Address1, dbo_tblMailList.mail_Address2, 
                            dbo_tblMailList.mail_City, dbo_tblMailList.mail_State, dbo_tblMailList.mail_Zip, dbo_tblMailList.mail_Phone1, dbo_tblMailList.mail_Email1, 
                            dbo_tblMailList.mail_Comp_GenAm, dbo_tblMailList.mail_Comp_Art, 
                            dbo_tblMailList.mail_Comp_IndArt, dbo_tblMailList.mail_Comp_Fire, dbo_tblMailList.mail_Comp_Ceramic
      )
  GROUP BY mail_ID, mail_FirstName, mail_LastName, mail_Address1, mail_Address2, 
                        mail_City, mail_State, mail_Zip, mail_Phone1, mail_Email1, 
                        mail_Comp_GenAm, SumOfitem_price, SumOfitem_premiums
  </code></pre>
  
  <p>Thanks Gareth!</p>