MS Access for the Business Environment: Access Query Techniques: Subqueries, Part 1 - Page 5
December 1, 2003
Our next step will be to add a total, via a SUM() function, of the Spend amount by supplier, by product. The business requirement did not specify any need to generate a total by product or even by supplier (although total supplier Spend might be deemed useful); the information consumers simply asked for a ranking of suppliers by amount of Spend for each. We will get there in a couple of steps, mainly to embellish our understanding of the data enroute to our final destination.
First, we'll modify the query to add the SUM() function. Our assumption will be, for purposes of this exercise, that Spend is computed from the Unit Price amount (in the Suppliers table) times total units (in our case, units on hand plus those we have formally ordered, and therefore obligated ourselves to purchase) of any given product.
22. Shift back to SQL view, once again.
23. Add a comma after the line underneath the SELECT keyword in query ACC0702, as shown:
CompanyName, ProductID, ProductName,
24. Add the following expression immediately after the new comma:
Sum((UnitPrice)*(UnitsInStock+UnitsOnOrder)) AS Spend
25. Save the query as ACC0703.
The query should now resemble that shown in Illustration 11.
26. Select Query --> Run from the main menu.
The resulting dataset appears as partially shown in Illustration 12.
We note that, while the number of records returned remains the same number as we saw retrieved in our last query (77), we now present the supplier information that is associated with the various products. The total Spend value, based upon the query we have evolved, represents the total for each supplier-product combination. We can thus see that the dataset approaches our requirement, with the remaining steps to summarize total Spend by supplier, then to rank the suppliers, based upon the total Spend amount, to provide the basis for the simple ranking that the information consumers have requested.
27. Shift back to SQL view, once again.
28. Remove the following from the line immediately underneath the SELECT keyword in query ACC0703, as shown:
To leave the following:
CompanyName, Sum((UnitPrice)*(UnitsInStock+UnitsOnOrder)) AS Spend
29. Remove the following from the line underneath the GROUP BY keyword in query ACC0703, as shown:
, ProductID, ProductName
To leave the following:
30. Save the query as ACC0704.
The query should now resemble that shown in Illustration 13.
31. Select Query --> Run from the main menu.
The resulting dataset appears as partially shown in Illustration 14.