按日期访问SQL筛选器查询两个值 [英] Access SQL Filter query by date for two values

问题描述

我有一个表达式可以显示所有订单进出之前的总库存:

I have an expression that shows the total inventory before all orders in and out:

(Query3只是将库存清单与接收和发出的订单结合在一起，并且效果很好)

(Query3 just combines the stock list with ingoing and outgoing orders, and works fine)

SELECT Query3.Products.ID, Query3.ProductName, Query3.StandardCost, Query3.OnHand,
  Nz(Sum([OrderJoin.Quantity]),0) AS Outgoing, Nz(Sum([Query1.Quantity]),0) AS Incoming,
  [OnHand]+[Outgoing]-[Incoming] AS OnHandAfter, [StandardCost]*[OnHandAfter] AS TotalCost
FROM Query3
GROUP BY Query3.Products.ID, Query3.ProductName, Query3.StandardCost, Query3.OnHand;

但是我一直在尝试按用户输入的日期对其进行过滤.我的代码不正确，我不确定为什么

But what I've been trying to do is filter it by a user-inputted date. The code I have is incorrect, and I'm not sure why

SELECT Query3.Products.ID, Query3.ProductName, Query3.StandardCost, Query3.OnHand, 
  (SELECT NZ(Sum([OrderJoin.Quantity]),0)
  FROM Query3
  WHERE (((Query3.ShippedDate)>[Enter End Date] Or (Query3.ShippedDate) Is Null))
  GROUP BY Query3.Products.ID, Query3.ProductName, Query3.StandardCost, Query3.OnHand;
    ) AS Outgoing, 
  (SELECT NZ(Sum([Query1.Quantity]),0)
  FROM Query3
  WHERE (((Query3.DateReceived)>[Enter End Date] Or (Query3.DateReceived) Is Null))
  GROUP BY Query3.Products.ID, Query3.ProductName, Query3.StandardCost, Query3.OnHand;
    ) AS Incoming,
[OnHand]+[Outgoing]-[Incoming] AS OnHandAfter, [StandardCost]*[OnHandAfter] AS TotalCost
FROM Query3
GROUP BY Query3.Products.ID, Query3.ProductName, Query3.StandardCost, Query3.OnHand;

访问出现错误:"At most one record can be returned by this subquery".有人知道我在做什么错吗?

Access comes up with the error: "At most one record can be returned by this subquery". Does anyone know what I'm doing wrong?

更新:

我发现在SELECT中嵌套SELECT只会尝试使用一个值，但是我想返回多个值.所以我稍微修改了一下代码，发现语法错误，我不确定为什么:

I have found that having the nested SELECTs in SELECT will only try to use one value, but I want to return multiple. So I edited the code a bit and it comes up with syntax errors and I'm not sure why:

SELECT Q1.Products.ID, Q1.ProductName, Q1.StandardCost, Q1.OnHand, 
  [Q1.OnHand]+[Q2.Outgoing]-[Q3.Incoming] AS OnHandAfter,
  [StandardCost]*[OnHandAfter] AS TotalCost
FROM (Query3 Q1
LEFT JOIN
  (SELECT Q2.Query3.Products.ID, Q2.Query3.ProductName, Q2.Query3.StandardCost,
    Q2.Query3.OnHand, NZ(Sum([OrderJoin.Quantity]),0) AS Outgoing
  FROM Query3 Q2
  WHERE (((Query3.ShippedDate)>[Enter End Date] Or (Query3.ShippedDate) Is Null))
  GROUP BY Q2.Query3.Products.ID, Q2.Query3.ProductName, Q2,Query3.StandardCost,
    Q2.Query3.OnHand) ON Q1.Query3.Products.ID = Q2.Query3.Products.ID) 
LEFT JOIN
  (SELECT Q3.Query3.Products.ID, Q3.Query3.ProductName, Q3.Query3.StandardCost,
    Q3.Query3.OnHand, NZ(Sum([Query1.Quantity]),0) AS Incoming
  FROM Query3 Q3
  WHERE (((Query3.DateReceived)>[Enter End Date] Or (Query3.DateReceived) Is Null))
  GROUP BY Q3,Query3.Products.ID, Q3.Query3.ProductName, Q3.Query3.StandardCost,
    Q3.Query3.OnHand) ON Q1.Query3.Products.ID = Q3.Query3.Products.ID
GROUP BY Q1.Query3.Products.ID, Q1.Query3.ProductName, Q1.Query3.StandardCost, Q1.Query3.OnHand;

推荐答案

这是来自类似问题的答案.使用MAX函数，以便子查询仅返回单行:

Here's an answer from a similar question. Use the MAX function so that the subqueries will only return a single row:

SELECT query3.products.id,
       query3.productname,
       query3.standardcost,
       query3.onhand,
       (
                SELECT   MAX(Nz(SUM([OrderJoin.Quantity]),0))
                FROM     query3
                WHERE    query3.shippeddate>[Enter End Date]
                OR       query3.shippeddate IS NULL
                GROUP BY query3.products.id,
                         query3.productname,
                         query3.standardcost,
                         query3.onhand;) AS outgoing,
       (
                SELECT   MAX(Nz(SUM([Query1.Quantity]),0))
                FROM     query3
                WHERE    query3.datereceived>[Enter End Date]
                OR       query3.datereceived IS NULL
                GROUP BY query3.products.id,
                         query3.productname,
                         query3.standardcost,
                         query3.onhand;) AS incoming,     
        [OnHand]+[Outgoing]-[Incoming] AS onhandafter,    
        [StandardCost]*[OnHandAfter] AS totalcost 
FROM query3 
GROUP BY query3.products.id, query3.productname, query3.standardcost, query3.onhand;

这篇关于按日期访问SQL筛选器查询两个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！