A summarized record for each unique field value is displayed in a crosstab query.

1

Week 7 - What We'll Be Working on This Week

In Access there is a lot more to queries than just creating Select queries that apply filters to the data in tables and other queries. It is possible to perform calculations in queries as a means of deriving new data from existing fields, and to summarize data using Aggregate functions. Crosstab queries, which are similar to Pivot Tables in Microsoft Excel, will save you hours of work in terms of summarizing table data, as well. In addition, Access provides a number of Special Purpose queries:

 Parameter queries will enable the user to specify criteria for the query when the query is run

 AutoLookup queries will enable you to enter one field value into the Datasheet View of the

query and have Access automatically look up and enter other values into the current record.

 Action queries will cause changes to be made to an object when the query is run:

o Update, Append, Delete and Make-Table queries will all potentially save you a

tremendous amount of time and make life very, very sweet.

Goals:

 Creating a calculation field in a query page 3

 Concatenating fields in a query page 6

 Summarizing with Aggregate functions page 9

 Adding other functions to the Aggregate Summary page 11

 Adding Captions page 13

 Creating a Crosstab query page 15

 Creating a Parameter query page 20

 Creating a Parameter query for a range of dates page 23

 Creating an AutoLookup query page 26

 Observing the impact of the AutoLookup query on the table page 28

 Documenting the database page 32

 Creating an Update query page 38

 Creating an Append query page 44

 Creating a Delete query page 48

 Creating a Make-Table query page 50

This is where we're headed this week. So let's get to it!

2

Goals for this section:

 Creating a calculation field in a query

 Concatenating fields in a query

 Summarizing with Aggregate functions

 Adding other functions to the Aggregate Summary

 Adding Captions

 Creating a Crosstab query

Calculated Data Type

Before we discuss setting up calculations in a query, I want to discuss the new Calculated data type that first appeared in Access 2010 (which some of you used in your last assignment). With the calculated data type, you can now store a formula or expression that will be updated automatically when the components of the expression change. The disadvantage of defining a field in a table as a Calculated data type is that the calculation can only use fields in that table. The advantage of using a Calculated data type is that it short cuts doing calculations. By putting the calculation in the table, you do not have to repeat the calculation where ever it is needed. All you do is just add the field to the form, report or query and it is done for you. Using a query to perform calculations helps to avoid the 'bulking up' of your table with unnecessary information. A query calculates the information whenever you need it. I strongly recommend that you put your calculations in your queries rather than tables as this allows for greater flexibility in your data design.

Performing Calculations in a Query

If you need to calculate any of the fields in your database tables, don't do it on a calculator and enter the results into the table manually, and don't export the data to Excel in order to get your answers. Instead, create a query, include the Number or Currency fields to be calculated, and create a formula in a blank column of the QBE Design grid. Calculation results may be based on any available fields in any of the objects on which the query is based. It is important to note that the fields that are calculated in a query do not need to be present in the recordset of the query. As long as the fields are present in the record source for the query, Access will recognize the data in those fields. Our objective is this: We'll use the qryWorkOrdersByCompletionDate query to calculate the total cost for each work order. This calculation will involve summing the curMaterialCost and the curLaborCost fields. To create a calculation on the Query Design window, place the cursor in the Field cell of the empty column in which you want to create the calculation. Type the appropriate formula, being sure to enclose all referenced fields in square brackets and spell the field names exactly as they are defined in the table. For example, the formula [curLaborCost]+[curMaterialCost] would sum the Labor Cost and Material Cost values for each record in the recordset. If you would like to work on a larger screen in order to better see what you're typing, you can press Shift F2 to display the Zoom box, and type the formula there. Once the formula has been created in this way, when you run the query Access will assign a temporary name of Expr with a sequential number (Expr1, Expr2, etc.) and a colon to the calculation field. You have the option of changing this label directly in the Field cell of the calculation column of the QBE Design grid. However, when you change the label in the

3

Field cell, be careful not to delete the colon because Access needs it to know which the field label is and where the formula begins.

Hands-On Activity: Creating a Calculation Field in a Query

Before beginning: Your Home Tech Repair database file is open, and there are no objects currently open.

1. Display the qryWorkOrdersByCompletionDate query in Design View.

2. In the QBE Design grid, place the cursor in the next available (that is, empty) Field cell (to the right of

the last filled column in the grid).

3. Press Shift F2, to open the Zoom box for the field. When you're typing long entries such as formulas

in a field, you may find it helpful to zoom the window to make it easier to see what you're doing.

4. In the Zoom box, type the following formula:

[curMaterialCost]+[curLaborCost]

Note: Make sure you enclose each of the field names in square brackets, and that you type the field name exactly as it appears in the table on which this query was based. If you don't, the calculation won't work.

4

5. Click OK, to enter the calculation.

6. Widen the column that contains the newly created calculation, to view it fully.

7. Run the query and observe the results: Access has summed the values in the Material Cost and Labor Cost fields, it has formatted the result as currency (to match that of the two calculated fields), and it has assigned a column heading label of Expr1 to the calculation. This column heading may be

changed.

8. Switch to Design View.

9. In the Field cell of the calculation, select Expr1.

10. Type Total Cost as the new label for the calculated field.

Note: When creating a label for the newly created calculation field, there is no need to use the Leszynski Naming Convention, since it is only a field label. It is fine to use spaces. Also, make sure you leave the colon after the field name

5

11. Run the query again, and observe the new column-heading label. Total Cost is now reflected,

instead of Expr1.

12. Choose File / Save As /Save Object As /Save As command.

13. Type qryWorkOrderTotalCosts as the name of the query.

14. Click OK

15. Close the query.

At this point you should have 7 queries created in your database: qryBidInfo qryCACustomers qryCustomersAndWorkOrders qryMissingHelpers qryPostJuly2003WorkOrders qryWorkOrdersByCompletionDate qryWorkOrderTotalCosts

6

Concatenating Fields in a Query

The term concatenate means to join, and it is a procedure that may be used for combining the contents

of multiple fields into a single field. For example, in a table you might have Last Name and First Name

fields, but want to reflect that information as Last Name, First Name in a single field. Rather than typing

the same data again (which as you know is a big "No No"), you can simply create a calculation using the

& character as a concatenation operator. If you want spaces or any punctuation (such as commas) to be

included in the calculation result, they must be enclosed in quotation marks.

This is the reason why it is advisable to break field names down into the smallest possible component

parts, as we indicated in the first lessons of this course. It's very easy to combine multiple fields into a

single field in queries, but it isn't so easy to break fields down (such as the use of composite primary key

fields).

Our objective is to list the Total Cost for all work orders in a query, with the employee name reflected as

Last Name, First Name in a single field. Because we have already calculated the Total Cost in the

qryWorkOrderTotalCosts query, we will add the strFirstName field to that query so it's available to us, and

then build the new query on the basis of qryWorkOrderTotalCosts.

Remember: Queries may be based on tables or on other queries.

Hands-On Activity: Concatenating fields in a query

Before beginning: Your Home Tech Repair database file is open, and there are no objects currently open.

1. Display the qryWorkOrderTotalCosts query in Design View.

2. In the tblEmployeeHRData field list, double-click the strFirstName field to add it to the QBE Design

grid.

3. Move the strFirstName field to the immediate right of the strLastName column.

4. Re-save and close the query. We will now create a new query based on this query.

5. On the Create / Queries group, click the Query Design icon to create a new query object.

6. On the Show Table window, activate the Queries tab to see the available queries on which the new query can be based.

7. Select qryWorkOrderTotalCosts and click Add (or double-click the object name).

7

8. Once the qryWorkOrderTotalCosts query object has been added to the upper portion of the Query

Design window, close the Show Table window.

9. Expand the upper pane and the field list.

10. In the Field List, double-click each of the following fields to add them to the QBE Design grid:

strWorkOrderNumber memDescription Total Cost

11. Place the cursor in the 4th Field cell, and press Shift F2 to display the Zoom box. 12. In the Zoom box, enter the following expression, being careful to type the field names exactly as they

are reflected in the record source: Supervisor:[strLastName]&", "&[strFirstName]

8

Note: The only space in this formula is between the comma and the close quotation marks. There should not be any spaces anywhere else in the expression.

13. Observe the entry:

 The & concatenation operator tells Access to join the next element in the string to the previous item.

 The quotation marks are used because all spaces and punctuation must be indicated as special characters.

 Supervisor: is the label we're assigning to the new field.

Note: You don't have to wait for Access to assign a temporary name of Expr with a number to the field and then change it.

14. Click OK.

15. Run the query and widen the Supervisor column to fully see its contents.

16. Observe the results: The first and last names are joined in a single field and there is a space after the

comma.

17. Save the query as qryCostsByEmployee.

18. Close the query

9

Summarizing with Aggregate Functions

Sometimes you aren't interested in each and every row in your table. You would rather see calculations across groups of data. For example, you might want the total product purchase amount for all companies in a particular state, or you might want to know the average of all sales for each month in the last year. To get these answers, you need a Totals query. A Totals query groups the fields you specify. Every output field must either be one of the grouping fields or the results of a calculation using one of the available aggregate functions. Because all fields are calculated, you cannot update any fields returned by a totals query. The available aggregate functions are:

Function Description

Sum Calculates the sum of all the values for this field in each group. You can specify this function only with number or currency fields.

Avg Calculates the arithmetic average of all the values for this field in each group. You can specify this function only with number or currency fields. Access does not include any Null values in the calculation.

Min Returns the lowest value found in this field within each group. For numbers, Min returns the smallest value. For text, Min returns the lowest value in collating sequence, without regard to case. Access ignores Null values.

Max Returns the highest value found in this field within each group. For numbers, Max returns the largest value. For text, Max returns the highest value in collating sequence, without regard to case. Access ignores Null values.

Count Returns the count of the rows in which the specified field is not a Null value. You can also enter the special expression COUNT(*) in the Field row to count all rows in each group, regardless of the presence of Null values

StDev Calculates the statistical standard deviation of all the values for this field in each group. You can specify this function only with number or currency fields. If the group does not contain at least two rows, Access returns a Null value.

Var Calculates the statistical variance of all the values for this field in each group. You can specify this function only with numbers or currency fields. If the group does not contain at least two rows, Access returns a Null value.

First Returns the value for the field from the first row encountered in the group. Note that the first row might not be the one with the lowest value. It also might not be the row you think is "first" within the group. This is because First depends on the actual physical sequence of stored data. It essentially returns an unpredictable value from within the group.

Last Returns the value for the field from the last row encountered in the group. Note that the last row might not be the one with the highest value. It also might not be the row you think is "last" within the group. This is because Last depends on the actual physical sequence of stored data. It essentially returns an unpredictable value from within the group.

Our objective is to create a query that lists Customer ID's with the Material and Labor Costs for the customers' work orders. Initially, some of the customers will be listed multiple times, because they have multiple work orders assigned to them. But then we will tell Access to group the records by the Customer ID field, and calculate the total and average Material Labor costs. In this way, each customer will be listed only once, including those with multiple work orders. Because the qryPostJuly2002WorkOrders query contains all of the fields we need for our new query, we will make a copy of that query object, give the new query a different name, and then make the necessary changes to it. This will save time.

10

Hands-On Activity 1: Summarizing with Aggregate Functions

Before beginning: Your Home Tech Repair database file is open, and there are no objects currently open.

1. In the Navigation Panel, right-click on the qryPostJuly2003WorkOrders object.

2. From the shortcut menu, choose Copy.

3. Right-click anywhere in the white area of the Database window, and choose the Paste command

from the shortcut menu.

4. Type qryAggregateTotals as the name of the new query.

5. Click OK.

6. Display the qryAggregateTotals query in Design View.

7. Select the strWorkOrderNumber column in the QBE Design grid, and delete it.

8. Also delete the dtmCompletionDate and memDescription fields. You should now have just three fields in the QBE Design grid:

9. Run the query and observe the results: Some of the customers are listed multiple times. If we tell Access to group by the contents of the Customer ID column and sum the contents of both the curMaterialCost and the curLaborCost fields, it will list each customer only once with a total cost for Material and Labor for each record.

10. Switch to Design View.

11. On the Design / Show/Hide group, click the Totals icon

A Total: row is added to the QBE Design grid, with Group By specified for each field.

11

You will need to leave Group By for the field whose contents are to be grouped (in this case, the Customer ID field) and change the Group By entry to a function to tell Access how to summarize the data in the two currency fields.

12. In the curMaterialCost column, click in the Total cell.

13. Click the v to display the drop-down list, and choose Sum to request a total of the Material Costs for customers.

14. Do the same for the curLaborCost field.

15. Run the query and observe the results:

 Each customer ID number is listed only once.

 Access has summed the values for multiple customer records and reflected the total in both the Material Cost and the Labor Cost columns.

 Access has also added SumOf to the beginning of the field name. You have the option of adding captions to change the column headings.

But suppose you want to see not only the totals, but also an average of the Material and Labor costs for each customer. The good news is that you can add the same field to the QBE Design grid as many times as necessary, and simply change the function in the Total: row.

Hands-On Activity 2: Adding other functions to the Aggregate Summary

Before beginning: The qryAggregateTotals query is displayed in Datasheet View.

1. Switch to Design View.

2. In the Field List, double-click the curMaterialCost field, to add it to the grid a second time.

3. Double-click the curLaborCost field to add it to the grid a second time.

4. Change the Total: cells of the two newly added fields from Group By to Avg.

12

5. Move the second curMaterialCost field to the immediate right of the first curMaterialCost field.

6. Run the query and observe the results: The Sum and Avg of both the Material Cost and the Labor

Cost fields are now calculated.

7. Re-save the query. Leave it open.

Applying Captions to the Summary Fields

As we have seen, Access automatically named the summary fields by simply adding SumOf and AvgOf

to the beginning of the field name. If you want to assign a different heading to the Summary fields, you

need to display the query in Design View, display the Properties window for each field, and add a caption.

13

Hands-On Activity: Adding Captions

Before beginning: The qryAggregateTotals query is displayed in Datasheet View.

1. Switch to Design View.

2. Place the cursor anywhere in the first curMaterialCost column (that is, the one that will provide a Sum of the Material Costs).

3. On the Design / Show/Hide group, click the Property Sheet icon.

4. Place the cursor on the Caption line of the Field Properties window, and enter Total Material as the caption.

5. With the Field Properties window still open, click to place the cursor in the second curMaterialCost

column.

Notice that it is not necessary to close the Field Properties window after entering each caption. As long as the Field Properties window remains open, clicking on a different object will enable you to set the properties for that object.

6. Place the cursor on the Caption line of the Field Properties window, and enter Avg Material as the

caption.

7. Using the same technique, add the captions of Total Labor and Avg Labor to the curLaborCost fields.

8. Once all of the captions have been added, close the Field Properties window.

9. Run the query. The field headings have been changed.