在访问中,不能有多个联接而不用括号将它们分开,即
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;
(我使用短表别名来尝试压缩代码,而不是必须遵循的约定)