MS Access，如何将参数传递给子查询？

发布于 2024-12-05 22:31:55 字数 365 浏览 0 评论 0原文

我有两张桌子。一个包含我的所有客户，另一个包含他们的所有订单。我想创建一个如下所示的 sql 查询：

SELECT c.CustomerID, c.Firstname, c.Lastname, 
       (
        SELECT count(orderID) 
          FROM tbl_orders o 
         WHERE o.CustomerID = c.CustomerID
       ) as OrderCount
  FROM tbl_customers c;

我遇到的问题是访问不断要求我提供将在子选择 WHERE 子句中使用的参数“CustomerID”。显然，我想自动弄清楚。我该怎么做？

原文

i've got two tables. one contains all my customers, another one all their orders.
i would like to create an sql query that looks like this:

SELECT c.CustomerID, c.Firstname, c.Lastname, 
       (
        SELECT count(orderID) 
          FROM tbl_orders o 
         WHERE o.CustomerID = c.CustomerID
       ) as OrderCount
  FROM tbl_customers c;

The problem I have with this is that access keeps asking me for the parameter "CustomerID" which would be used in the subselect WHERE clause. Obviously, I'd like to figure it out automatically. How do I do this?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

心作怪 2024-12-12 22:31:55

没有明显的原因，它应该有效。也许您在某个字段上拼写错误。检查 CustomerID 是否是 tbl_orders & 的一部分tbl_customers。

您可以通过以下链接找到有关子查询的一些信息：SubQueryDoc

回复收藏 0 原文

月下客 2024-12-12 22:31:55

为什么不尝试使用 join 和 group by 呢？对我来说，它似乎更简单、更丰富。

SELECT c.CustomerID, c.Firstname, c.Lastname, count(o.orderId) as Orders, max(o.OrderDate) as LastOrder
FROM tbl_customers c LEFT JOIN tbl_orders o 
ON o.CustomerID = c.CustomerID
GROUP BY c.CustomerID, c.Firstname, c.Lastname

可以在查询设计视图中直观地设计这样的查询。然后，您可以切换到 SQL 视图，并复制或编辑生成的 SQL 语句。

Why not trying with a join and a group by ? it seems simpler and richer to me.

SELECT c.CustomerID, c.Firstname, c.Lastname, count(o.orderId) as Orders, max(o.OrderDate) as LastOrder
FROM tbl_customers c LEFT JOIN tbl_orders o 
ON o.CustomerID = c.CustomerID
GROUP BY c.CustomerID, c.Firstname, c.Lastname

Such a query can be designed visually in the query design view. You can then switch to SQL View, and copy or edit the generated SQL statement.

回复收藏 0 原文