Microsoft Access查询总计 [英] microsoft access query grand total

问题描述

我有一个Microsoft Access查询，该查询工作正常并且给出了所需的结果，但我想在底部添加一个总计行

I have a microsoft access query which is working fine and giving the required result but i want to add a grand total row at the bottom

我的查询是

SELECT Product.Description
    , Count(Product.PID) AS CountOfPID
    , Sum(SalesOrderProduct.NbrItemsDispatched) AS SumOfNbrItemsDispatched
    , Sum(SalesOrderProduct.ExtendedPrice)
FROM Product 
LEFT JOIN 
(
    SalesOrder 
    RIGHT JOIN SalesOrderProduct 
        ON SalesOrder.SOID = SalesOrderProduct.SOID
) 
    ON Product.PID = SalesOrderProduct.PID
GROUP BY Product.Description, SalesOrder.Status
HAVING SalesOrder.Status <>  'Open' or  SalesOrder.Status is null;

并且在没有数据的地方，必须显示0，并且不应该显示$符号

and also where there is no data then 0 must be displayed and $ sign should not be displayed

推荐答案

我强烈建议您不要这样做.最近有几个问题是在哪里结束的.总计是一个显示问题，属于另一个查询或报告.

I strongly suggest that you do not do this. There have been several questions recently where it ended in a problem. The grand total is a display issue and belongs in another query or in a report.

如果您仍然需要这样做，可以在查询中使用UNION.

If you still feel the need to do this, you can use a UNION in your query.

联合查询可能看起来像:

A union query might look like:

SELECT 0 As srt, AText, Count(ID)  FROM Table1
GROUP BY Srt, AText
UNION
SELECT 1 As Srt, "Total" As AText ,Count(ID) FROM Table1

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