Here are three approaches. I'll assume you have a query or table with
fields Customer, Vendor, and Price and a row for each detail
transaction, named qryDetailItems. Substitute your actual names as
required.
A: Use a report
1) Create a query that returns one row for each Vendor for each
Customer, showing the total price of all transactions for each
combination, e.g.
SELECT Customer, Vendor,
Sum(Price) AS Amount
FROM qryOrderItems
GROUP BY Customer, Vendor
ORDER BY Customer, Vendor;
2) Create a report based on that query, grouped and subtotalled by
Customer. Format it as plainly as possible.
3) Export the report to Excel.
B: Do it in Excel. Create a query as in A, export it to Excel, and use
Excel features (e.g. SUMIF()) to produce the subtotals.
C: Fake it with a UNION query:
1) Start with the same query as in A, but remove the ORDER BY clause and
add a calculated field that we'll use later to distinguish between "per
vendor per customer" rows and "customer subtotal" rows:
SELECT " " AS SubTotal,
Customer, Vendor,
Sum(Price) AS SumOfPrice
FROM qryOrderItems
GROUP BY Customer, Vendor;
2) Make a copy of this and modify it to return the "customer subtotal"
rows you want. GROUP BY Customer so as to return one row per Customer:
SELECT "Subtotal" AS SubTotal,
Customer, " " AS Vendor,
Sum(Price) AS SumOfPrice
FROM qryOrderItems
GROUP BY Customer;
3) When each of these is working properly, paste the SQL from the second
onto the end of the first, separate them with the UNION keyword and add
an ORDER BY clause:
SELECT " " AS SubTotal,
Customer, Vendor,
Sum(Price) AS SumOfPrice
FROM qryOrderItems
GROUP BY Customer, Vendor
UNION
SELECT "Subtotal" AS SubTotal,
Customer, " " AS Vendor,
Sum(Price) AS SumOfPrice
FROM qryOrderItems
GROUP BY Customer
ORDER BY 2, 1, 3;
4) Export to Excel.
If you want to try these out on the order details table in the Northwind
sample database, create a new query there, paste in this SQL statement
and save it as qryOrderItems:
SELECT Customers.CompanyName AS Customer,
Suppliers.CompanyName AS Vendor,
CCur([Order Details].[UnitPrice]*[Quantity]*(1-[Discount])/100)*100 AS
Price
FROM (Customers INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID)
INNER JOIN (Suppliers INNER JOIN (Products INNER JOIN [Order Details]
ON Products.ProductID = [Order Details].ProductID)
ON Suppliers.SupplierID = Products.SupplierID)
ON Orders.OrderID = [Order Details].OrderID;
