MS Access中查询表达式中的语法错误(缺少运算符) [英] syntax error (missing operator) in query expression in MS Access

问题描述

我遇到了错误:

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感到满意.

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.

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)

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

If anyone has any ideas, please let me know.

谢谢， 詹姆斯

编辑

使用下面的Gareth的FROM子句，我有:

Using Gareth's FROM clause below I now have:

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，则会收到错误消息:

I think it may have something to do with AND dbo_tblBidder.bidder_sale_id IN (319). If I change this to AND dbo_tblBidder.bidder_sale_id = 319, I get the error:

JOIN expression not supported.

并突出显示dbo_tblBidder.bidder_sale_id = 319.

结论

这是我最后的工作代码:

Here is my final working 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

感谢Gareth！

推荐答案

在访问中，如果没有用括号将它们分开的话，就不能有多个联接.

In access you cannot have multiple joins without separating them with parentheses, i.e.

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子句必须为:

So, your from clause would need to be:

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

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

编辑

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

I forgot, you cannot apply the constant expression in the JOIN clause in Access, you would need to create a subselect,

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 

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

So your full query would be:

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;

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

这篇关于MS Access中查询表达式中的语法错误(缺少运算符)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！