|Figure 1: The Order Details Table.|
Summary Query Data Exercise
Before creating the Query, you will first need to create the table or download the Summary_Data.accdb database. This consists of one Order Details table with three fields - OrderId (Number), ProductId (Number), and Amount (Currency). The primary key is a composite of OrderId and ProductId. If this was a full Access Database Application the tblOrderDetails would be a junction table between an Orders table and a Products table. However, for our purposes of this exercise we only need to use the Order Details table. Once this is in place you can begin the exercise.
- Click the QUERY DESIGN icon. This is located in the OTHER group of the CREATE RIBBON.
- Add tblOrderDetails to the Query Design Window from the SHOW TABLE dialogue box.
- Add the OrderId and Amount fields to the Query Design Grid. The quickest way to do this is to double click the two field names from the Table Diagram in the top section of the Query Design Window.
- We now need to show the TOTAL's row in the Query Design Grid. This is not shown by default so click the TOTAL's icon in the SHOW/HIDE group of the DESIGN ribbon. The TOTAL's row should now appear in the grid.
- We want our query to group all the records into individual orders, and calculate the total amount for each one. This is where the TOTALS row comes in. We are going to enter the GROUP BY clause in the TOTAL's row of the OrderId column. (NB This is actually the default setting, so you should not need to change it). All records sharing the same OrderId will now become a seperate and distinct group (ie row) in our query results. Then move over to the TOTAL's row of the Amount column. We are going to enter the SUM function into this cell. You can type this in directly or select it from the drop down list. This will give us the Sum of Amount's for each individual group in the query results.
|Figure 2: GROUP BY and SUM |
entered into the TOTAL row.
We can now run our query by clicking the RUN query or DATASHEET icon. Figure 3 below shows what our query results should look like:
|Figure 3: Query results showing a summary|
of our original data.