MS Access for the Business Environment: Access Query Techniques: Using the TOP Keyword - Page 4
January 5, 2004
We will now move to SQL view, as we did in our previous lesson, because we wish to use direct SQL, and to work with queries at a level that goes beyond working with Design view.
11. Select SQL View using the View Selector button in the main toolbar (it appears under the File item on the main menu).
The SQL view editor appears, complete with a SELECT keyword in place, followed by the ubiquitous ending character for MS Access queries, the semicolon (";").
We have again arrived at a point where we can enter, display and / or modify a query using SQL directly, as we will throughout this lesson. As we found in earlier lessons, we can do many things here that might prove difficult (if not impossible) in Design view, or within the realm of wizardry. While we can construct many queries within the query design grid, SQL view is clearly the environment from which to gain a basic understanding of SQL.
We will begin by composing a simple core SELECT query, to generate a result dataset from which we will meet the core consumer requirement; we will begin by listing all transactions as a means of establishing the layout of the final results dataset, whereby we want to provide the shipping date, order and customer identification, and the total amount of the order shipped. It appears that the data we need can be found within the Orders and Order Details tables, and so our query will center on these tables throughout the session.
12. Type the following basic SELECT query into the editor:
SELECT Orders.OrderID, ShippedDate, ShipName, ShipAddress, ShipCity, ShipCountry, UnitPrice*Quantity-(UnitPrice*Quantity*Discount) AS Total FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID;
We can run the query at this stage, after saving it using File in the main menu.
13. Save the query as ACC08-01.
14. Select Query --> Run from the main menu.
The resulting dataset appears as partially shown in Illustration 7. We note that 2,155 records are returned, per the counter at the bottom of the Data view.
We have assembled our basic SELECT query, and have run it to obtain the full population of the information fields we require from the Orders and Order Details tables. Our objective in doing is to get a layout established for the results we ultimately seek, and to ascertain that the calculation we have inserted for the Total amount of each record is working correctly (a quick test of the math will indicate that the Total calculation is, indeed, accurate). Beginning our query development in this way will also allow us to obtain a familiarity for the underlying data as we proceed.
We note that the above query delivers a record set containing multiple instances of the same Order ID in many cases. This is because the Order Details table breaks order information into a separate line item for each product, so that, for example, if a customer purchases three products in the same order, three records will exist with the same Order ID in the Order Details table.
Our information consumers want a single line item for each order, so we will take care of that feature next.
15. Ensure that the query is saved as ACC08-01.
Let's modify the query to generate summed totals by Order ID. We will do this via two additions to our existing SQL, as shown in the next step.
16. Shift back to SQL view, once again, if necessary.
17. Add the following, immediately before UnitPrice, and just after ShipCountry, in the SELECT clause:
18. Insert a right parenthesis ")" between "Discount)" and "AS Total".
The objective is simply to enclose the calculation within the parentheses of a SUM function. The affected line within the SELECT clause should appear as below:
SUM(UnitPrice*Quantity-(UnitPrice*Quantity*Discount)) AS Total
19. Add the following line to the query, just below the FROM clause:
GROUP BY Orders.OrderID, ShippedDate, ShipName, ShipAddress, ShipCity, ShipCountry;
20. Remove the semicolon (";") that appears at the end of the line above that we just added.
The query should now resemble that shown in Illustration 8.
21. Select Query --> Run from the main menu.
The resulting dataset appears as shown in Illustration 9.
We note that the Order IDs are combined into single summed records. We can see also that the total number of records returned in the dataset is 830, which serves to confirm the fact that the product transactions with the same Order IDs have been combined.
22. Save the query as ACC08-02.