Access Tutorial

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.