Subtotal in Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a way to have a subtotal at the end of a group like the SUBTOTAL
function in Excel?
Thanks
 
Databases and spreadsheets are very different animals, so don't expect the
former to behave like the latter.

The best place to include subtotals is in a report based on the query, where
you can use an unbound text box control in a group footer with a
ControlSource such as:

=Sum([YourFieldName])

You can if you wish include subtotals as an extra column in a query by using
a correlated subquery, e.g.

SELECT Customer, OrderDate, OrderAmount,
(SELECT SUM(OrderAmount)
FROM Orders
WHERE Orders.OrderID = Customers.OrderID) As TotalCustomerAmount
FROM Customers INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID

Ken Sheridan
Stafford, England
 
Thank you

Ken Sheridan said:
Databases and spreadsheets are very different animals, so don't expect the
former to behave like the latter.

The best place to include subtotals is in a report based on the query, where
you can use an unbound text box control in a group footer with a
ControlSource such as:

=Sum([YourFieldName])

You can if you wish include subtotals as an extra column in a query by using
a correlated subquery, e.g.

SELECT Customer, OrderDate, OrderAmount,
(SELECT SUM(OrderAmount)
FROM Orders
WHERE Orders.OrderID = Customers.OrderID) As TotalCustomerAmount
FROM Customers INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID

Ken Sheridan
Stafford, England

Raj said:
Is there a way to have a subtotal at the end of a group like the SUBTOTAL
function in Excel?
Thanks
Click to expand...
Click to expand...
 
Back
Top