computer test on ACCESS2013

LECTURE NOTES

· Introduce queries

BOXES

· InSight: Designing Queries vs. Using a Query Wizard (AC 124)

FIGURES

· Figure 3-6

TEACHER TIP

Explain that the datasheet from the query is a temporary view of the data. All of the data still exists in the participating tables even though they might not be displayed in the query datasheet.

CLASSROOM ACTIVITIES

1. Group Activity: Divide students into groups of two or three. Have students consider a table of student's records. Each group should brainstorm about different queries (questions) they might want to ask about the student data. Have students make a list of possible queries including the fields that would be needed and the criteria that would specify which records would be displayed. Additionally, have the groups think about who might need the results of their list of queries. If students have trouble getting started, give them one of the following examples:

· A list of students who are from a particular state

· A list of students with a GPA of 3.0

· A list of students with a GPA under 2.0

· A list of students who are math majors

2. Quick Quiz:

· True/False: No Query Wizard exists in Access. (Answer: False)

· True/False: The result of a query is referred to as a recordset. (Answer: True)

AC 125 Creating and Running a Query

LECTURE NOTES

· Demonstrate how to create a query

· Demonstrate how to run a query

BOXES

· ProSkills: Decision Making: Comparing Methods for Adding All Fields to the Design Grid (AC 128)

FIGURES

· Figure 3-7, Figure 3-8

TEACHER TIP

Explain that a query result differs from a table's Datasheet view in that only selected fields are displayed. The results of the query will be displayed in order by the primary key of the table unless a different sort order is specified.

CLASSROOM ACTIVITIES

1. Class Discussion: What is the difference between a query datasheet and a table datasheet? (Answer: Although a query datasheet looks just like a table datasheet and appears in Datasheet view, a query datasheet is temporary, and its contents are based on the criteria you establish in the design grid. In contrast, a table datasheet shows the permanent data in a table. However, you can update data while viewing a query datasheet, just as you can when working in a table datasheet or form.)

2. Quick Quiz:

· True/False: If you accidentally add the wrong field to the design grid, you will not be able to remove the field from the grid. (Answer: False)

LECTURE NOTES

· Demonstrate how to update data using a query

BOXES

· Query Datasheet vs. Table Datasheet (AC 129)

TEACHER TIP

You can use the query datasheet to update data in a table. It is important to realize that the query datasheet is a temporary view of the data. However, when you update data by means of the query datasheet, the updates are placed directly into the underlying table. You can only update fields that are represented in the query. To observe the actual changes made to the table, you will want to close the query and open the table in Datasheet view.

Caution students about updating table data in a query.If only a portion of the fields are represented in the query, there may be fields that are not getting updated.

CLASSROOM ACTIVITIES

· True/False: A query datasheet is temporary. (Answer: True)

· True/False: You should check the table to verify that changes you made in the query datasheet were also made in the table. (Answer: True)

2. Class Discussion:

Ask students to discuss the difference between a query datasheet and a table datasheet.

LECTURE NOTES

· Demonstrate how to create a query using multitables

FIGURES

· Figure 3-9

TEACHER TIP

Explain to students that a multitable query is a query based on more than one table. If you want to create aquery that retrieves data from multiple tables, the tables must have a common field.

CLASSROOM ACTIVITIES

1. Creative Thinking Activity: Consider using some “real world examples.” Use the class situation, for example. Say that a class has one teacher and many students. Each student in the class has one teacher, but the teacher has many students. So you can say that the relationship between teacher and student is a one-to-many relationship. You can use many examples to reinforce the concepts of relationships. If you use the mother and child relationship, you can say this is a one-to-many relationship because each child has only one mother, but the mother may have many children. If, on the other hand, you use the parent (both mother and father) and child relationship, this would be a many-to-many relationship because each child has “many” (more than one) parents and each parent can have many children. One final example would be the husband and wife relationship. This is a one-to-one relationship because each husband has one wife, and each wife has one husband.

2. Quick Quiz:

· True/False: A relationship must be established between tables based on a common field that exists in both tables before you can create a query to display data from both tables at the same time. (Answer: True)

· True/False: A multitable query is a query based on only two tables. (Answer: False)

LECTURE NOTES

· Show how to use AutoFilter to sort data

· Show how to sort on multiple fields in Design view

BOXES

· Reference Window: Sorting a Query Datasheet (AC 133)

FIGURES

· Figure 3-10, Figure 3-11, Figure 3-12, Figure 3-13

TEACHER TIP

You can control the order of data displayed in a query by altering its sort key. Students will choose a field on which they want the data to be sorted and then specify whether they want the data sorted in ascending or descending order. Sometimes they may want to specify multiple fields as the sort key if they want to arrange data in sorted order within groups of data. They will find that they can easily sort the query in the query design grid.

CLASSROOM ACTIVITIES

1. Quick Quiz:

· The sort that allows you to sort numerically from lowest to highest is a ____ sort.(Answer: A)

A. Number, Ascending

B. Number, Descending

C. Text, Ascending

D. Text, Descending

· _____is the process of rearranging records in a specified order or sequence. (Answer: Sorting)

2. Class Discussion:

What is the AutoFilter? (Answer: It enables you to quickly sort and display field values in various ways. A menu gives options for sorting and displaying field values. The first two options on the menu enable you to sort the values in the current field in ascending or descending order. Unless you save the datasheet or form after you’ve sorted the records, the rearrangement of records is temporary.) Ask students to share some examples of when AutoFilter might be used.

LECTURE NOTES

· Demonstrate how to display records using Filter by Selection

BOXES

· Reference Window: Using Filter By Selection (AC 136)

FIGURES

· Figure 3-14, Figure 3-15

TEACHER TIP

Sometimes database users will want to isolate a certain portion of data in their query results. This is done by setting a filter, or a set of restrictions, to specify what records they want to view in the results. There are two methods used to make these selections, “Filter By Selection” or “Filter By Form.” If you no longer want to view the data with the filter applied, you can simply remove the filter.

CLASSROOM ACTIVITIES

1. Quick Quiz:

· True/False: A filter is a set of restrictions you place on the records in an open datasheet or form to permanently isolate a subset of the records.(Answer: False)

· True/False: The simplest technique for filtering records is Filter By Selection.(Answer: True)

2. Class Discussion:

What is the difference between Filter By Selection and Filter By Form? (Answer: Filter By Selection lets you select all or part of a field value in a datasheet or form, and then display only those records that contain the selected value in the field. Filter By Form changes your datasheet to display blank fields. Then you can select a value using the arrow that appears when you click any blank field to apply a filter that selects only those records containing that value.) Ask students to give examples of when you would use each.

LAB ACTIVITIES

Have students try filtering data by various fields and values. Show them examples of setting and removing filters on several fields. Explain that this is just a temporary filter that can be removed at any time. The filter does not alter the data in the table at all.

LECTURE NOTES

· Demonstrate how to specify an exact match using a query

· Show how to modify a query

· Show how to use a comparison operator to match a range of values

FIGURES

· Figure 3-16, Figure 3-17, Figure 3-18, Figure 3-19, Figure 3-20, Figure 3-21, Figure 3-22, Figure 3-23, Figure 3-24, Figure 3-25

TEACHER TIP

Remind students that they have already learned how to select what fields they want to see in a query datasheet. In this section, they establish which records they want to see. Be sure to thoroughly cover the concepts of a condition and how comparison operators are used to establish the condition. Use Figure 3-16 to go over the various operators. Be sure students understand that where there are two symbols in an operator (such as the less than or equal to operator) there is no space between the two symbols. If they put a space between the symbols, an error will be created.

CLASSROOM ACTIVITIES

1. Quick Quiz:

· True/False: A comparison operator asks Access to compare the value in a database field to the condition value and to select all the records for which the relationship is true.(Answer: True)

· True/False: You can make changes to the query if the results are not what you expected or want to view. (Answer: True)

· To run a query again, double-click the query name in the _____ pane. (Answer: Navigation)

2. Creative Thinking Activity:

Have students think of additional examples of the use of the comparison operators.

LECTURE NOTES

· Discuss the logical operators And and Or for multiple selection criteria

· Demonstrate how to create a new query using the And logical operator

· Demonstrate how to create a new query using the Or logical operator

BOXES

· InSight: Understanding the Results of Using And vs. Or (AC 153)

FIGURES

· Figure 3-26, Figure 3-27, Figure 3-28, Figure 3-29, Figure 3-30

TEACHER TIP

When you need to use multiple conditions for a query, you will need to use the logical operators to combine conditions. The “And” logical operator specifies that both conditions must be met. The “Or” logical operator specifies that one or the other of the conditions must be met.

Spend some time going over the flowcharts in Figure 3-26.This will help the students understand how the condition works. Use the arrows to follow the chart under the current conditions.

CLASSROOM ACTIVITIES

1. Quick Quiz:

· True/False: If none of the conditions are met that you specify, Accessdoes not select the record.(Answer: True)

· To create an Or condition in the Query window, you enter two or conditions on _____. (Answer: A)

A. separate rows

B. the same row

C. separate design windows

D. separate datasheet columns

· When you want a record selected only if two or more conditions are met, use the _____. (Answer: And logical operator)

2. Class Discussion:

What is the difference between the And and Or logical operators? (Answer: When you use the And logical operator to define multiple selection criteria in a query, you narrow the results produced by the query, because a record must meet more than one condition to be included in the results. When you use the Or logical operator, you broaden the results produced by the query, because a record must meet only one of the conditions to be included in the results. This is an important distinction to keep in mind when you include multiple selection criteria in queries, so that the queries you create will produce the results you want.) Ask students to give examples of when each might be used.

LECTURE NOTES

· Show how to modify the font size

· Demonstrate how to change the alternate row color

FIGURES

· Figure 3-31, Figure 3-32

TEACHER TIP

Many of the formatting changes to a datasheet to improve its appearance or readability are similar to the types of changes you can also make in Word documents or Excel spreadsheets, such as modifying the font type, size, color, and the alignment of text. You can also apply different colors to the rows and columns in a datasheet to enhance its appearance.

CLASSROOM ACTIVITIES

1. Quick Quiz:

· A _______ is a predefined set of formats including colors, fonts, and other effects. (Answer: theme)

· True/False: When choosing a row color, be sure not to select a color that is too dark because it might obscure the data rather than enhance it. (Answer: True)

2. Class Discussion:

LECTURE NOTES

· Discuss calculated fields

· Show how to create calculated fields

· Demonstrate how to format a calculated field

BOXES

· Reference Window: Using Expression Builder (AC 157)

· Problem Solving: Creating a Calculated Field vs. Using the Calculated Field Data Type (AC 161)

FIGURES

· Figure 3-33, Figure 3-33, Figure 3-35

TEACHER TIP

Sometimes, students will want a query to actually perform a calculation for them. They will need to enter an “expression” into the query design grid. They have to be careful when creating an expression that they are following the rules of precedence. If students want to perform a calculation in a query, they will need to add a “calculated field” to the query design.

CLASSROOM ACTIVITIES

1. Quick Quiz:

· True/False: You must first save and name a query in order for its fields to be listed in the Expression Categories section. (Answer: True)

· True/False: An Expression Builder is a field that displays the results of an expression. (Answer: False)

2. Class Discussion:

In class, discuss the importance of understanding calculated fields. Values that are produced by calculated fields should not be stored as separate fields in a database table. If you store the results of a calculated field in a table and the data produced by the calculated field becomes outdated, you would have to update the records in the table datasheet with the current data. It is best to create a query that includes a calculated field to perform the calculation you want. Then, every time you open the query, the calculation is performed and the resulting query datasheet reflects the most current data.

LAB ACTIVITIES

Ask the students to use Expression Builder to build several expressions. If students become familiar with the use of Expression Builder, they will have a smoother experience in learning to develop calculated fields. Use Figure 3-33 to guide a discussion of the parts of the Expression Builder dialog box.

LECTURE NOTES

· Discuss aggregate functions

· Show how to apply aggregate functions using the Total Row

· Demonstrate how to create queries with aggregate functions

· Show how to calculate statistics for groups of records

FIGURES

· Figure 3-36, Figure 3-37, Figure 3-38, Figure 3-39, Figure 3-40, Figure 3-41

TEACHER TIP

Use Figure 3-36 to introduce the most commonly used aggregate functions. Be sure to include in your discussion the “Data Types Supported” so that students understand that they can only use these functions on supported data types.

CLASSROOM ACTIVITIES

1. Quick Quiz:

· True/False: Aggregate functions perform arithmetic operations on a database. (Answer: False)

· True/False: You can apply only one aggregate function to a table. (Answer: False)

2. Class Discussion:

Discuss the exercise for using record group calculations. Records are grouped by City, and aggregate functions are used to create totals for the groupings. Who would use this kind of report? What other ways might this table be grouped? Why would this be used in an organization? Who would use the report?

LECTURE NOTES

· Demonstrate how to group objects in the Navigation Pane

FIGURES

· Figure 3-42, Figure 3-43

TEACHER TIP

Discuss that as you continue to create objects in a database, you might want to display and work with them in different ways. The Navigation Pane provides options for grouping database objects in various ways to suit your needs.

CLASSROOM ACTIVITIES

1. Quick Quiz:

· True/False: You can hide the display of a group’s objects by clicking the bar for the group. (Answer: True)

· The main area for working with the objects in a database is referred to as the ________. (Answer: Navigation Pane)

2. Class Discussion:

Ask the class to discuss the default All Access Objects category. (Answer: The default All Access Objects category is a predefined category. You can also create custom categories to group objects in the way that best suits how you want to manage your database objects. As you continue to build a database and the list of objects grows, creating a custom category can help you to work more efficiently with the objects in the database.) Why might you create custom categories?

End of Tutorial Material

· Case Problems: A typical NP tutorial has four Case Problems following the Review Assignments. Short tutorials can have fewer Case Problems (or none at all); other tutorials may have five Case Problems. The Case Problems provide further hands-on assessment of the skills and topics presented in the tutorial, but with new case scenarios. There are four types of Case Problems:

· Apply. In this type of Case Problem, students apply the skills that they have learned in the tutorial to solve a problem.

· Create. In a Create Case Problem, students are either shown the end result, such as a finished Web site, and asked to create the document based on the figure provided, or students are asked to create something from scratch in a more free-form manner.

· Challenge. A Challenge Case Problem involves three or more Explore steps. These steps challenge students by having them go beyond what was covered in the tutorial, either with guidance in the step or by using online Help as directed.

· Troubleshoot. In this type of Case Problem, certain steps of the exercise require students to identify and correct errors-which are intentionally placed in the files. This is intended to promote problem solving and critical thinking.

A tutorial does not have to include each of the four types of Case Problems; rather, the tutorial’s content should dictate which Case Problems to include. It is possible, therefore, that some tutorials might have three Case Problems of one type and only one Case Problem of a different type. To the extent possible, the first Case Problem in a tutorial should be an Apply so that the Case Problems progress in degree of difficulty.

· ProSkills Exercises: This feature is new for Office 2013 and Windows 7. ProSkills exercises integrate the technology skills students learn with one or more of the following soft skills: decision-making, problem-solving, teamwork, verbal communication, and written communication. The goal of these exercises is to enhance students’ understanding of the soft skills and how to apply them appropriately in real-world, professional situations that also involve software application skills. ProSkills exercises are offered at various points throughout a text, encompassing the concepts and skills presented in a standalone tutorial or a group of related tutorials.

Glossary of Key Terms

·

· Aggregate functions (AC 161)

· All Access Objects (AC 167)

· And logical operator (AC 149)

· AutoFilter (AC 132)

· Calculated field (AC 156)

· Comparison operator (AC 140)

· Condition (AC 140)

· Design grid (AC 116)

· Editing mode (AC 118)

· Exact match (AC 141)

· Expand indicator (AC 123)

· Expression (AC 156)

· Expression Builder (AC 156)

· F2 key (AC 118)

· Field list (AC 116)

· Filter (AC 136)

· Filter By Form (AC 136)

· Filter By Selection (AC 136)

· Find command (AC 121)

· Group By operator (AC 166)

· Hide Fields (AC 119)

· Logical operators (AC 149)

· Navigation mode (AC 118)

· Nonunique (AC 133)

· Object Type (AC 167)

· Or logical operator (AC 149)

· Plus sign (AC 122)

· Primary sort field (AC 133)

· QBE (AC 117)

· Query by example (AC 117)

· Query Wizard (AC 124)

· Recordset (AC 124)

· Running (AC 116)

· Secondary sort field (AC 133)

· Select query (AC 116)

· Sort field (AC 131)

· Sorting (AC 131)

· Subdatasheet (AC 123)

· Theme (AC 154)

· Unhide Fields (AC 119)

· Unique (AC 133)

· Zoom box (AC 156)