MS Access对SQL查询的问题 [英] MS Access to SQL query issue

问题描述

我有应该在MS Access中使用的查询，但是该数据库是SQL数据库.当我在SQL环境中运行此查询时，它运行良好.但是，在MS Access中运行时，出现错误.我对SQL的了解很少(来自MySQL)，对MS Access的了解甚少.

I have this query that is supposed to be used in MS Access, but the database is an SQL database. When I run this query in an SQL environment, it works perfectly. However, when ran in MS Access, I get errors. I know little about SQL to begin with (coming from MySQL), and even less about MS Access.

该查询应该可以为我提供某种投标人类型中的某人对某个项目进行投标的总人数(无论他们是否中标)，在该投标人类型中中标的项目的总价以及投标人类型，全部进行一次拍卖.这是下面的查询.

The query is supposed to give me the total number of people within a certain bidder type who bid on an item (whether they won it or not), the total price of items won within that bidder type, and the bidder type, all for a single auction. Here is the query below.

SELECT     Total.count, SUM(dbo_tblItem.item_premium + dbo_tblItem.item_pr) AS SumTotal, dbo_tblBidder.bidder_type
FROM         dbo_tblBidder LEFT OUTER JOIN
                  dbo_tblItem ON dbo_tblItem.item_bidder_number = dbo_tblBidder.bidder_number   AND 
                  dbo_tblItem.item_sale_id = dbo_tblBidder.bidder_sale_id LEFT OUTER JOIN
                      (SELECT     COUNT(bidder_type) AS count, bidder_type
                        FROM          dbo_tblBidder AS tblBidder_1
                        WHERE      (bidder_sale_id = 235)
                        GROUP BY bidder_type) AS Total ON dbo_tblBidder.bidder_type = Total.bidder_type
WHERE     (dbo_tblBidder.bidder_sale_id = 235)
GROUP BY dbo_tblBidder.bidder_type, Total.count
ORDER BY dbo_tblBidder.bidder_type

MS Access告诉我:

MS Access tells me:

查询表达式中的语法错误(缺少运算符).

Syntax error (missing operator) in query expression ".

然后，它突出显示了dbo_tblBidder.bidder_number中的"mber"，并显示:

Then, it highlights "mber" from dbo_tblBidder.bidder_number where it says:

dbo_tblItem开启dbo_tblItem.item_bidder_number = dbo_tblBidder.bidder_number

dbo_tblItem ON dbo_tblItem.item_bidder_number = dbo_tblBidder.bidder_number

我不知道突出显示是否实际上是任何内容的一部分.

I don't know if the highlighting is actually part of anything or not.

推荐答案

当您连接两个以上的表时，Access需要加上括号，并且对它们的位置非常挑剔. (尽管查询的数据源之一是子查询，而不是实际的表，但它与关于联接和括号的表的处理方式相同.)建议您在Access的查询设计器中将其构建为新查询，只是为了查看它如何放置括号用于您的联接表.

When you join more than 2 tables, Access requires parentheses and is very finicky about their placement. (Although one of your query's data sources is a subquery rather than an actual table, it's treated same as a table regarding joins and parentheses.) Suggest you build this as a new query in Access' query designer simply to see how it places the parentheses for your joined tables.

计数"是一个保留字，因此我在查询中出现的任何位置都用该名称括起来，以减少混淆数据库引擎的机会.

"count" is a reserved word, so I bracketed that name everywhere it appeared in the query to reduce the chance of confusing the database engine.

使用LEFT JOIN代替LEFT OUTER JOIN来访问Access的数据库引擎.

Use LEFT JOIN instead of LEFT OUTER JOIN for Access' db engine.

我认为这可能接近您的需求.

I think this may be close to what you need.

SELECT
    Total.[count],
    SUM(dbo_tblItem.item_premium + dbo_tblItem.item_pr) AS SumTotal,
    dbo_tblBidder.bidder_type
FROM         
    (dbo_tblBidder LEFT JOIN dbo_tblItem
        ON (dbo_tblItem.item_bidder_number = dbo_tblBidder.bidder_number
           AND dbo_tblItem.item_sale_id = dbo_tblBidder.bidder_sale_id)
        )
    LEFT JOIN (
        SELECT     COUNT(bidder_type) AS [count], bidder_type
        FROM          dbo_tblBidder
        WHERE      bidder_sale_id = 235
        GROUP BY bidder_type
        ) AS Total
        ON dbo_tblBidder.bidder_type = Total.bidder_type
WHERE     dbo_tblBidder.bidder_sale_id = 235
GROUP BY dbo_tblBidder.bidder_type, Total.[count]
ORDER BY dbo_tblBidder.bidder_type;

这篇关于MS Access对SQL查询的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！