Home and Learn: Microsoft Access Course
Microsoft Access - Asking questions of your Data
In previous lessons, we've constructed two database tables in Access and created a one-to-many relationship between the two. If you haven't done these lessons, then download the database here (Right-click > Save As):
Access Database After Validation Rules
Let's now write some Access queries so we can ask questions about our data. We'd like to know the following:
What are the profit margins for each Widget?
What are the total sales figures for each color Widget?
What are the total profits for each color Widget?
Which month of the year is the most profitable?
Which day of the week is the most profitable?
Which year was the most profitable?
To answer these questions, you'll create something called a calculated field.
At the top of Access, click on the Create ribbon. From the Create ribbon, click on the Query Design item on the Queries panel:
If you have an earlier version of Access, you'll see a popup box with your tables listed. Select both tables and then close the popup box down. In later versions of Access, you'll see an Add Tables panel open on the right of your screen. Again, select both tables. Click the button at the bottom that says Add Selected Tables. In all Access versions, you should now be looking at this.
We have our tables at the top and area at the bottom where we can construct our queries. Let's tackle the first question.
What are the profit margins for each Widget?
To work out the profit margins for each Widget, we only need to deduct the buy price from the total price. We want to say this:
[TotalPrice] - [BuyPrice]
But we'd like to add a new field (column) to display the figures. To do this, we need to add a calculated field. This is quite easy.
First, though, let's tell Access to display the Widget colors. In the first column of the grid at the bottom, click the dropdown arrow next to Field. Select the Widgets, WidgetColor field:
In the next column of the data grid, select the Widgets.BuyPrice field:
In the third column, select the Widgets.TotalPrice field:
You should then have this:
We can enter the calculated field in the fourth column. Enter this in the Field text area at the top of the fourth column:
ProfitMargin: [TotalPrice]-[BuyPrice]
Don't miss out the colon (:) after ProfitMargin. Anything before the colon will be used as a field name; anything after will be the expression that Access calculates.
If your eyesight's not too good, you can bring up a zoom box. To do this, click inside the empty field box to the right of TotalPrice:
If you right-click, you should see a menu with zoom on it. Or, on your keyboard, hold down the SHIFT key. With the SHIFT key held down, press F2. This zoom box appears:
Type your expression:
If your text is still too small, click the Font button and increase the font size.
Click OK on the Zoom box and your expression will appear in the field:
Let's run this query and see what happens.
On the Query Design ribbon at the top of Excel, click the Run item on the Results panel:
You should see this:
What Access has done is the display every single entry for the Widget Colors - all 10,000 or so of them! This is not what we want. We only want each unique value to display.
Displaying Unique Values in Microsoft Access
Go back to Query Design by either clicking View > Design View on the Home ribbon at the top of Access, or click the little ruler icons in the bottom right of your screen:
If you want to go back to Datasheet View, click the little table icon two to the left of the Design View icon.
To get unique values, we need to bring up the Property Sheet again. Either hit ALT + ENTER on your keyboard, or click the Property Sheet icon on the Query Design ribbon:
With the property sheet open, click anywhere on a white area in the Query Design window (in other words, not on one of the tables at the top, or the grid at the bottom). Your Property Sheet should look like this:
Notice that there is an item on the list with the property name Unique Values. This is set to No. Change this to Yes.
Run your query again. You should see this:
That's much better - exactly what we want! Notice the highlighted column above, the one on the end. That's the calculated field. The field is called - ProfitMargin. Just as we specified with this:
ProfitMargin: [TotalPrice] - [BuyPrice]
Save your query. Call it qryProfitMarginsByColor. You should see it appear in the All Access Objects list on the left:
You can double click the query to run it anytime you like.
Now that we've answered one question, let's tackle another. We'll do that in the next lesson below. We want to know something about the sales figures.
<-- Previous Lesson: Validation Rules | Next Lesson: More Queries -->
<--Back to the Microsoft ACCESS Contents Page