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.
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.
Type the following basic SELECT
query into the editor:
Orders.OrderID, ShippedDate, ShipName, ShipAddress,
UnitPrice*Quantity-(UnitPrice*Quantity*Discount) AS Total
Orders INNER JOIN [Order Details]
Orders.OrderID = [Order Details].OrderID;
We can run the query at this stage, after saving it using File
in the main menu.
Save the 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.
Illustration 7: The Core Query Results Dataset (Partial
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
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.
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.
Shift back to SQL
view, once again, if necessary.
following, immediately before UnitPrice, and just after
ShipCountry, in the SELECT clause:
Insert a right
parenthesis ")" between "Discount)" and "AS Total".
objective is simply to enclose the calculation within the parentheses of a SUM
function. The affected line within the SELECT clause should appear
SUM(UnitPrice*Quantity-(UnitPrice*Quantity*Discount)) AS Total
following line to the query, just below the FROM clause:
GROUP BY Orders.OrderID, ShippedDate, ShipName, ShipAddress, ShipCity,
semicolon (";") that appears at the end of the line above that we just
query should now resemble that shown in Illustration 8.
Illustration 8: The SELECT Query with Modifications
--> Run from the main menu.
The resulting dataset appears as shown in Illustration 9.
Illustration 9: The Dataset Retrieved by the Modified
SELECT Query (Partial View)
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.
Save the query