CODEWITHSUNDEEP
sqlms-access
user1488255
user1488255

Reputation:

microsoft access query grand total

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

my query is 

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;

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

Upvotes: 0

Views: 1465

Answers (1)

Fionnuala
Fionnuala

Reputation: 91316

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.

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

Upvotes: 2

Related Questions