MS Access中查询表达式中的语法错误（缺少运算符）

小编典典

MS Access中查询表达式中的语法错误（缺少运算符）

sql

我收到错误消息：

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)'.

我以前遇到过此问题，并且知道它不喜欢我的括号设置，但是我所做的一切似乎都无法使MS Access感到满意。

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)

如果有人有任何想法，请告诉我。

谢谢，詹姆斯

编辑

使用FROM下面的Gareth子句，我现在有：

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)

但是我得到了错误 Extra ) in query expression ''.

我认为这可能与之有关AND dbo_tblBidder.bidder_sale_id IN (319)。如果将其更改为AND dbo_tblBidder.bidder_sale_id = 319，则会收到错误消息：

JOIN expression not supported.

它突出了dbo_tblBidder.bidder_sale_id = 319。

结论

这是我的最终工作代码：

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

谢谢加雷斯！

阅读 278

2021-05-23

共1个答案

小编典典

在访问中，不能有多个联接而不用括号将它们分开，即

SELECT  *
FROM    A
        INNER JOIN B
            ON A.ID = B.AID
        INNER JOIN C
            ON B.ID = C.BID;

无效，它必须是：

SELECT  *
FROM    (A
        INNER JOIN B
            ON A.ID = B.AID)
        INNER JOIN C
            ON B.ID = C.BID;

因此，您的from子句将需要为：

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

注意：我从联接中删除了所有不必要的括号，以减少查询中的混乱情况（不必将每个谓词都括在括号中），并夸大了制表符缩进以清楚显示括号在何处打开和关闭

编辑

我忘记了，您不能在Access的JOIN子句中应用常量表达式，您需要创建一个子选择，

dbo_tblMailList 
LEFT JOIN dbo_tblBidder 
    ON dbo_tblMailList.mail_ID = dbo_tblBidder.bidder_mail_id 
    AND dbo_tblBidder.bidder_sale_id IN (319)

你需要做

dbo_tblMailList AS m
LEFT JOIN (SELECT * FROM dbo_tblBidder WHERE bidder_sale_id IN (319)) AS b
    ON m.mail_ID = b.bidder_mail_id

因此，您的完整查询将是：

SELECT  m.mail_FirstName, 
        m.mail_LastName, 
        m.mail_Address1, 
        m.mail_Address2, 
        m.mail_City, 
        m.mail_State, 
        m.mail_Zip, 
        m.mail_Phone1, 
        m.mail_Email1, 
        m.mail_Comp_GenAm, 
        b.bidder_number, 
        SUM(i.item_pr) AS SumOfitem_pr, 
        SUM(i.item_premium) AS SumOfitem_premium, 
        b.bidder_type, 
        s.sale_id
FROM    (
            (   dbo_tblMailList AS m
                LEFT JOIN 
                (   SELECT  bidder_mail_id , bidder_number, bidder_type, bidder_sale_id
                    FROM    dbo_tblBidder 
                    WHERE   bidder_sale_id IN (319)
                ) AS b
                    ON m.mail_ID = b.bidder_mail_id 
            )
            LEFT JOIN dbo_tblSale AS s
                ON b.bidder_sale_id = s.sale_id
        )
        LEFT JOIN dbo_tblItem AS i
            ON b.bidder_sale_id = i.item_sale_id 
            AND b.bidder_number = i.item_bidder_number
GROUP BY 
        m.mail_FirstName, m.mail_LastName, m.mail_Address1, m.mail_Address2, m.mail_City, m.mail_State,
        m.mail_Zip, m.mail_Phone1, m.mail_Email1, m.mail_Comp_GenAm, b.bidder_number, b.bidder_type, s.sale_id;

（我使用短表别名来尝试压缩代码，而不是必须遵循的约定）

2021-05-23