按日期访问SQL筛选器查询两个值

debugcn 发表于 Dev

cfleming93

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

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

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;

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

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;

Access出现错误："At most one record can be returned by this subquery"。有人知道我在做什么错吗？

更新：

我发现在SELECT中嵌套SELECT只会尝试使用一个值，但是我想返回多个值。因此，我对代码进行了一些编辑，结果出现语法错误，我不确定为什么：

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函数，以便子查询将仅返回单行：

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;

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。