MS Access for the Business Environment: Access Query Techniques: Using the TOP Keyword - Page 4

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.

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.

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.

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.

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.

SUM(

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

GROUP BY Orders.OrderID, ShippedDate, ShipName, ShipAddress, ShipCity,
   ShipCountry;

The query should now resemble that shown in Illustration 8.

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.