Subtotal in Query

R

Raj

Is there a way to have a subtotal at the end of a group like the SUBTOTAL
function in Excel?
Thanks
 
K

Ken Sheridan

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
 
R

Raj

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...
 
Top