A query can be created to calculate totals, averages, and other mathematical statistics on groups or subgroups in the database.

1.

Open the New School database, then open the Bookstore table in the datasheet view. This represents a list of all the supplies a fictional student named Bill purchased at the bookstore for one month.

Bill would like to find out how much he spent on each kind of item, such as notebooks, pens, etc. This can be accomplished by inserting a total row into a query. The total row includes aggregate functions such as sum, average, max, min, and count.

2.

Close the Bookstore table. Click on the Queries tab.

3.

Click on New, Design View.

4.

Select Bookstore from the list of available tables and queries. Click on Add, then close the Show Tables window..

5.

Add all fields into the QBE grid. (If you are not sure what to do, please review the Beginning Access handout at )

The QBE grid should look like this:

6.

Click on the 3 icon on the Query Design toolbar. This will add a new row, called the Total row, into the query. The QBE grid should look like this:

Notice that each Total row includes Group By as the default. This is what Access will use to determine the sorting/grouping subtotals. However, too many "group bys" will cause confusion. Let's remove some of the columns to get a clearer picture.

7.

Click at the top of the Item# column. The entire column should be selected. Press the Delete key to remove that column. Repeat for the Color column. The QBE grid should look like this:

8.

Bill wants to know how much he spent on each item. We need to change the Unit Price from Group By to Sum.

9.

Click on Group By in the Unit Price column. A list arrow appears at the right side. Click on the list arrow and select Sum.

10.

Just to refresh your memory, this was Bill's original list of purchases:

Notice that he bought notebooks, notebook paper, and diskettes several times.

11.

Click on the ! to run the query. Your results should look like this:

Access has gone through the list and "grouped" all like purchases, then created a subtotal based on each group.

Now look back at the original list. Bill bough red notebooks and blue notebooks. Suppose he wanted to get a more detailed list of subtotals, one that grouped by color as well?

12.

Go back to the Query design view. Add the Color field into the QBE grid between the Description and UnitPrice columns.

13.

Run the Query. Your results should look like this:

Since we added the Color filed as another Group By, the notebooks are now broken down into different colors, and a subtotal was created for each color.

14.

What about the SumOfUnitPrice label at the top of the column? You can change this to read whatever you prefer.

15.

Go back to the Query Design view. Click anywhere in the Unit Price column, then RIGHT click and select properties from the menu that appears. Click in the Caption on the General tab, and key in your preference. We will key in Total Spent. Notice that there is a space between Total and Spent. As a general rule, you do not use spaces between words in field names, but a caption is not really a field name, more like a nickname, and spaces are permitted. Run the Query. It should look like this:

Save the query as Total by Item. Then return to the Query Design view.

Bill would now like to get a grand total of what he has spent. We can modify the Total by Item query to give the grand total.

16.

In the Design View, select and remove the Description and Color columns. The design should now look like this:

17.

Run the query. Since we removed all Group By settings, only the grand total should appear.

Modify the column header by returning to the Design View and changing the caption to Grand Total. Run the query to view the change:

18.

Save this new query by clicking on File, Save As/Export. (Caution: If you just click on the Save icon, you'll save the Grand total query under the Total by Item query!). The next step gives you the opportunity to save the query under a new name. Change the query name to Grand Total.

19.

Close the query and return to the database window.

CREATING CALCULATED FIELDS IN QUERIES

Susan, the manager of the bookstore is considering an across the board price increase of 10%, but has concerns about what this would do to the average student. She decides to pick one average student at random and calculate how much more that student would spend with the price increase. Bill is her choice.

Calculated fields do not exist in the table themselves, but are based on fields that do exist in the table. Susan will multiply the Unit Price field by 10% to show the amount of the increase.

1.

Click on New, Design View to begin the query. Add the Bookstore table to the query. Close the Show Tables window. Add the Description and Unit Price fields into the QBE grid.

Unit Price is the last column in the query. Sue decides to put the New Unit Price in the next available column. Click in the first row of the next available column, then click on the Build icon on the toolbar (it looks like a magic wand). The Expression Builder window should open.

Some important things to remember as you create the mathematical expression:

The field names in the expression must be IDENTICAL to the field names in the table. If you misspell the field names, the expression will not work.

Mathematical operators are + addition; - subtraction; * multiplication; / division.

If you use Excel, think about how you write those formulas. You'll find this to be very similar.

Orders of precedence (the please excuse my dear Aunt Sally rule) still apply.

The expression will originally be named Expr1. You can rename the column using the caption property.

To avoid typos in the field names, paste whenever possible. The first column shows all objects available in the database; the middle column shows all fields available in the selected object; and the third column shows any values available for the selected field, such as in the example below.

2.

Double click on Tables in the first column. Double click on Bookstore.

3.

Click on Unit Price in the middle column, then click on paste. [Bookstore]![UnitPrice] should appear in the top window. [Bookstore]! indicates the Bookstore table, and [UnitPrice] is the field from that table. You could key these in yourself, but you run the risk of typos.

Finish the formula by adding *.1 after [Bookstore]![UnitPrice], so that it reads

[Bookstore]![UnitPrice] *.1

4.

Click on OK, then run the query. The results should look like this:

The column labeled Expr1 shows the price increase per item. However, there are too many decimal places in the results, and Expr1 needs to be renamed.

5.

Return to the query Design View. Click in the third column, Expr1. Note that it reads

Expr1: [Bookstore]![UnitPrice]*0.1. Everything before the colon can be changed, everything after the colon must remain exactly as it is - that's the part that's telling Access what to do mathematically. You can delete Expr1 and key in the new column name, or you can right click, go to properties, and change the caption as we did previously. Either way will work. Name the new column Unit Price Increase. Do not remove the colon.

To reduce the number of decimals and format for currency, right click in the New Unit Price column, and go to properties. Click on Format and choose Currency from the drop down list. Close the Properties window and run the query. The results should look like this:

6.

Save the query as Price Increase. Go to the design view. Remove the Description column. Susan wants to see the total increase in price, so add the total row. Change both total rows aggregate functions to Sum. Run the query. The results should look like this:

From this Susan can see that Bill, the average student, will spend an additional $3.71 per month if she decides to increase the prices. Close the query, do not save the changes.

Susan decides that she is going to raise some, but not all of the prices in the bookstore by 10%. First, she would like to keep a record of the old prices. To do this, she can use a Make Table query.

MAKE TABLE QUERY

1.

Start a new query and go to the design view. Add the Bookstore table. Close the Show Table window. Add all fields from the table into the QBE grid.

2.

Click on Query in the menu. Choose Make Table Query. Key in Old Bookstore Prices in the Table Name box. make sure that Current Database is selected. Click OK. Then (and this is very important) run the query. You should get a message that you are about to paste records. Click yes.

3.

Close the query without saving it. Click on the tables tab, and open the Old Bookstore Prices table. It should be identical to the Bookstore table.

Next, Susan would like to delete one of the items. Colored pencils do not sell well, and she needs the shelf space for other things. She will create a Delete query to remove the colored pencils.

DELETE QUERY

In a delete query and update query, it is important that you be sure to act on the correct records. For this reason, is it advisable to create a select query first, then change that to the delete or update query.

1.

Start a new query and go to the design view. Add the Bookstore table. Close the Show Table window. Add all fields from the table into the QBE grid.

2.

In the criteria row for the Description, key in Colored Pencils.

3.

Run the select query. The results should look like this:

Go back to the design view. Now that you are certain that the query has been created correctly, click on Query in the menu, Delete query. Run the query again. You should get a message that you are about to delete rows from the table. Click on yes. Close the query without saving it. Open the Bookstore table. Colored pencils should be removed from the records.

Now Susan wants to change the prices on notebooks from $1.99 to $2.25. She can create an Update query to do this.

UPDATE QUERY

1.

Start a new query and go to the design view. Add the Bookstore table. Close the Show Table window. Add all fields from the table into the QBE grid.

2.

First, create the select query to make sure you have the correct records. Key 3 Ring Notebook in the criteria row under description. Run the query. The results should look like this:

3.

Go back to the design view. Click on Query in the menu, then select Update Query. Notice that a new row is added into the QBE grid. You must fill in the Update To cell under Unit Price with 2.25.

4.

Run the query. You should get a message that you are about to update rows. Click yes. Close the query without saving it. Open the Bookstore table, and check the notebook price. It should now read $2.25.

Finally, one of Susan's coworkers has created another table of items for the bookstore that Susan might be interested in. Susan wants to add that list into the Bookstore table. For this, she will use an Append query.

APPEND QUERY

1.

Open the New Suggested Bookstore Items table. These are the items that Susan wants to add to the Bookstore table. Close the table.

2.

Start a new query and go to the design view. Add the New Suggested Bookstore Items table. Close the Show Table window. Add all fields from the table into the QBE grid.

3.

Click on Query in the menu. Choose Append Query. Key in Bookstore in the table name.

4.

Click on OK. Run the query. You should be prompted about adding rows. Click on yes. Close the query without saving it, then open the Bookstore table. The new items should have been added.

Note: In all of the above "action" queries (Make Table, Delete, Update and Append) we did not save the query itself. The reason for that is that once the query is run, it has done its job and is no longer needed. For instance, it would not make sense to run the Delete query on the colored pencils again, since the colored pencils have already been deleted. However, you could save the delete query and modify it to delete some other item.

Joe, who works in the Registrar's Office, would like to create a form where he can see both the personal information about the student and the student's schedule at the same time. This can accomplished in a Main Form/Subform.

The Main form will contain all the information about the student, while the Subform will show their schedule. Just to refresh your memory, the New School database contains a Student Info table and a Student Schedule table. SSN is the primary key in the Student Info table, and the secondary key in the Student Schedule table. For the Main Form/Subform to work, we will need to establish a relationship between the SSN field in the Student Info table and the SSN field in the Student Schedule table.

CREATING A RELATIONSHIP

1.

Close any queries or tables that are open. Do not close the database window.

2.

Click on the Relationship icon on the toolbar.

3.

Click on the Show Table icon (it has a yellow plus on it).

4.

Add the Student Info and Student Schedule tables, then close the Show Table window.

5.

Click on SSN in the Student Info table, and drag it over SSN in the Student Schedule table. The Relationships window should appear.

6.

Access has already determined that for every record in the Student Info table, there may exist more than one record in the Student Schedule table (i.e., a student may sign up for more than one class). You should see One-to-Many in the Relationship Type box. Click in the box to Enforce Referential Integrity.

7.

Click on Create. Notice that the line connecting the two tables has a 1 on the Student Info side and the infinity symbol on the Student Schedule side (that's the many in one to many). Click on the Save icon to save the relationship. Close the Relationship window.

Now that the relationship has been established, Joe can create the Main Form/Subform using the Form Wizard.

CREATING A MAIN FORM/SUBFORM USING A WIZARD

1.

Click on the Forms tab. Click on New, Form Wizard. Select the Student Info table from the drop down list. Click on OK.

2.

Add all fields into the form by clicking on the >> arrow.

3.

In the Tables/Queries list, click on the list arrow and select the Student Schedule table.

4.

Add all fields from the Student Schedule table EXCEPT SSN by clicking on the > arrow.

5.

Click on Next. Select view the data by Student Info. This will make the student information the main form (on top) and the student schedule the subform (on the bottom). Make sure that Form with Subforms is selected. Click Next.

6.

Select Datasheet, click Next. Select Standard. Click Next. Accept all the defaults and click Finish. The Main form/Subform should appear.

Notice that there are 2 sets of navigation buttons. The top set controls the subform - moving you from record to record (our subform has only one record for each student). The bottom set controls the main form, moving you from student to student. You can modify the size of each column in the subform by double clicking on the divider between each column.

Close the form, saving if you are prompted to.