Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS Access

Posted Dec 1, 2003

MS Access for the Business Environment: Access Query Techniques: Subqueries, Part 1 - Page 5

By William Pearson

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.


Illustration 11: The SELECT Query with Modifications

26.  Select Query --> Run from the main menu.

The resulting dataset appears as partially shown in Illustration 12.


Illustration 12: The Dataset Retrieved by the Modified SELECT Query (Partial View)

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:

ProductID, ProductName,

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:

CompanyName  

30.  Save the query as ACC0704.

The query should now resemble that shown in Illustration 13.


Illustration 13: The SELECT Query with Modifications

31.  Select Query --> Run from the main menu.

The resulting dataset appears as partially shown in Illustration 14.


Illustration 14: The Dataset Retrieved by the Modified SELECT Query (Partial View)



MS Access Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS Access Forum
Topic By Replies Updated
Help With Microsoft Access kasy 0 September 4th, 07:35 PM
Linked table not sorting or filtering - ODBC error Java 1 August 28th, 10:37 AM
Use Parameter in select statement (Sql in Microsoft Access) katty.jonh 1 July 25th, 06:45 AM
Query Issue algebroni 7 July 23rd, 04:22 PM