This tutorial illustrates the steps one would need to perform with Access to answer a question similar to question number #8 on Assignment 1.
The question we'll be answering is: What was the total amount of cash sales?
First, download DiveShop2000.mdb and open it with Access. (While you are at the website, open the ER diagram for the database - you will need it while answering questions.) In the window that opens, select "Queries" on the left, then click on "Create query in design view":
Use the dialog window that pops up to select database tables that you
would need for your query.
We get three tables with links between them:
Note that all the tables you select must be linked. If you select
just two disjoint tables (e.g., DIVECUST and DIVESTOCK) without
selecting any of the tables that link them, all of your queries will
return a cartesian product of the two tables. For the purpose of this
assignment, if you are getting more than a full page results, you are
probably using disjoint tables.
We can now select fields we need by clicking on them. The first two
columns are simple: we select two fields that we'll be constraining
and enter the constraints (="Cash" and ="Sale") to show that we only
want items that we sold and sold for cash. If we wanted to see
the descriptions of items that were sold, we would select
DIVESTOCK>Description as the third field. But we want to get the
total of (quantity * sale) price for each item. So we do something
else:
To get the quantity * sale price we type in the following into the "Field" row of the third column:
expression: [Qty]*[Sale Price]
To get the sum, we need to first hit the button with the summation
sign (Σ) in the toolbar to get the extra Total column to
appear. Then select Sum in it.
When we hit the "!" button in the toolbar we now get the result:
Note, that to get the grant total we need to select only the two
fields that we are constraining and use the third column for the
expression that we are summing. Adding any additional fields would
give us a separate line for each combination of items with a separate
total for each of them. For instance, suppose we added DIVEITEM>Qty,
like this:
We would now get 2 totals:
What does this mean? Adding an extra Qty columns tells Access that we
want to see separately the results with different values of Qty
field. What Access is telling us here is that once we limit ourselves
to orders paid in cash and sold, there are two possible values of Qty:
1 and 5. I.e., all sale orders paid in cash included either just one
item or five of them. We then see separately the total sales for
items sold individually and for items sold in groups of 5. To get a
single total we would need to delete the Qty column.