LECTURE
NOTES
·
Introduce
the topic of querying a database
TEACHER TIPS
Introduce this
chapter by reminding students that when use an online search engine, such as
Google they are really asking questions of a database. When they access the
online card catalog at the library, they also are querying a database.
1. Quick Quiz:
1)
What
is a query? (Answer: A question presented in a way that Access can process)
AC 74: Project — Querying a Database
LECTURE
NOTES
·
Review
the types of questions that you can ask a database, such as JSP Recruiters
using Figure 2-1
·
Review
the tasks that will be covered in this chapter:
o
Creating
queries using the Simply Query Wizard and Design view
o
Using
criteria in queries
o
Creating
and using parameter queries
o
Sorting
data in queries
o
Joining
tables in queries
o
Performing
calculations in queries
o
Creating
crosstab queries
FIGURES
and TABLES: Figures — 2-1
CLASSROOM ACTIVITIES
1.
Ask students for other questions they could ask the JSP Recruiters database.
2.
Ask students for sample questions that might be asked of a student database.
Ask students for other examples of searching or querying a database.
AC 76: Plan Ahead Box (Critical Thinking): Query
Design Guidelines
LECTURE
NOTES
·
Discuss
the general guidelines for query design:
o
Identify
the fields
o
Identify
restrictions
o
Determine
whether special order is required
o
Determine
whether more than one table is required
o
Determine
whether calculations are required
o
If
data is to be summarized, determine whether a crosstab query would be
appropriate
TEACHER TIPS
These guidelines are intended to help students
with their critical thinking skills. Students should understand the principles
of query design. Spend a significant amount of class time reviewing each of
these guidelines and use example databases to which students can relate. These
points are emphasized at appropriate locations in the chapter. Also, Cases and
Places 3, 4, and 5 challenge students to apply these guidelines to creating
queries.
CLASSROOM ACTIVITIES
1.
Assign a Project: Divide the class into small groups. Assign each group a
different database and have them create five sample queries. Example databases
include: University database (Students, Teachers, Courses); Employment database
(Employees, Departments); Bookstore database (Books, Authors, Publishers); and Video
Store database (Movies, Directors, Actors, DVDs). When they have created
queries, have them apply the guidelines.
LECTURE
NOTES
·
For
figures that match those in the book, change screen resolution to 1024 x 768
·
Start
Windows Vista and click Microsoft Office Access 2007
·
Review
the steps to start Access
·
Review
the steps to open a database
TEACHER TIPS
Review any special log on procedures for your
location at this time.
You can use the Lecture Success
System for Access in conjunction with the Figures in the Book. To do this,
start Access and open the database from the appropriate folder. Then, start
your slide show containing the figures for the project. You can switch back and
forth between the slide show and Access by using the alt+tab key combination. You can use the Figures in the Book
to show the steps students should follow. If students need additional
reinforcement or ask questions about the task, you can switch to Access to do a
live demonstration.
LECTURE
NOTES
·
Define
query
·
Use
Figures 2-2 through 2-4 to review how to use the Simply Query wizard to create
a query
·
Using
Figure 2-5, discuss the various ways you can use a query:
o
View
the results
o
Print
the results
o
Change
the design
·
Define
criterion
·
Use
Figures 2-6 through 2-9 to review how to use a criterion in a query
·
Review
the steps to print the results of a query
·
Describe
the steps to create a query in Design view using Figures 2-10 and 2-11
·
Use
Figure 2-12 to review the step to add fields to the design grid
FIGURES
and TABLES: Figures — 2-2, 2-3, 2-4, 2-5, 2-6, 2-7, 2-8, 2-9, 2-10, 2-11, 2-12
BOXES:
1.
Other Ways: Encourage your students to explore other ways of viewing query results.
TEACHER TIPS
Use Figure 2-11 to point out the field row and
the criteria row in the design grid. A query can be a subset of the records in
a table, a subset of the fields in a table, or a subset of both records and
fields. The order of the fields also can be changed. Access uses
Query-by-Example (QBE) to query a database. Query-by-Example is a query
manipulation language for relational databases in which users indicate the
action to be taken by completing on-screen forms. The query feature of
Microsoft Excel uses QBE as does Microsoft Works.
CLASSROOM
ACTIVITIES
1.
Assign a Project: Have students research QBE, an early language for creating
queries and then share the results of their research with the class.
LECTURE
NOTES
·
Use
Figures 2-13 and 2-14 to review the steps to use text data in a criterion
·
Define
wildcard and explain the difference between an asterisk and a question mark
·
Use
Figures 2-15 and 2-16 to review the steps to use a wildcard
·
Describe
the steps for using criteria for a field not included in the results using
Figures 2-17 through 2-19
·
Discuss
parameter queries and point out the advantages
·
Use
Figures 2-20 through 2-22 to describe the steps to create a parameter query
·
Review
the steps to save a query using Figures 2-23 and 2-24
·
Use
Figure 2-25 to describe using a saved query
·
Use
Figures 2-26 and 2-27 to review the steps to use a number in a criterion
·
Use
Figures 2-28 and 2-29 to define comparison operator and review the specific
comparison operators
·
Discuss
using compound criterion and explain the difference between AND criterion and
OR criterion
·
Use
Figures 2-30 and 2-31 to review the steps to use AND criterion
·
Use
Figures 2-32 and 2-33 to review the steps to use OR criterion
FIGURES
and TABLES: Figures — 2-13, 2-14, 2-15, 2-16, 2-17, 2-18, 2-19, 2-20, 2-21,
2-22, 2-23, 2-24, 2-25, 2-26, 2-27, 2-28, 2-29, 2-30, 2-31, 2-32, 2-33
BOXES:
1.
BTW: Removing a Table from a Query.
Review the step to remove a table from a query.
2.
BTW: The BETWEEN Operator. Describe
the purpose of the BETWEEN operator.
3.
Other Ways: Encourage your students to explore other ways of saving a query.
TEACHER TIPS
Students should understand that although the
answer displays in Datasheet view, no table exists. It is a dynamic or virtual
set of records. When a query design is saved, only the design is saved not the
answer. Query results, therefore, always show the most current data.
When a criterion is entered in a query, the
criterion is an example of the expected result. Entering criterion in a query
is similar to entering an author’s name in a search of a library card catalog
or an electronic library database. The asterisk and question mark wildcards are
the same wildcards that are used with the search features in Windows and other
Microsoft applications. Access automatically adds the LIKE operator and quotation
marks to criteria that use wildcards.
Comparison operators are used with both numeric
and text data. The order of the greater than or equal to (>=) and less than
or equal to (<=) operators is fixed; they cannot be reversed. The <>
operator also can be used to indicate NOT.
You can use a Venn diagram to illustrate the
concept of AND and OR criterion visually:
Several of the exercises at the end of the chapter
require students to use compound criteria.
You can use either the View button or the Run
button to view query results. The Run button appears on the Design tab only.
CLASSROOM
ACTIVITIES
1.
Class Discussion: Design a truth table on the board and fill in the values with
the students. Ask students for other uses of a truth table.
2.
Class Discussion: Place the students in small groups and ask them brainstorm
other uses for parameter queries.
3.
Quick Quiz:
1)
If
“H*” is entered as a criterion in a field in the design grid, what does it
mean? (Answer: That the value in the field must start with “H” followed by any
collection of characters)
2)
If
“H?” is entered as a criterion in a field in the design grid, what does it
mean? (Answer: That the value in the field must start with “H” followed by any
single character)
3)
What
is the purpose of the Show check box in the design grid? (Answer: The Show
check box indicates whether a field is displayed in the result. A check mark in
the check box indicates that a field is to be included in the result. If there
is no check mark, a field will not appear in the results.)
4)
What
five comparison operators can be used in queries? (Answer: >, <, >=,
<=, NOT)
LAB
ACTIVITIES
1.
Have students use Access Help to find other wildcards that can be used in
queries.
2.
Have students do the queries in this section and vary the case of the letters
in the criteria, for example, enter fd89 instead of FD89 and report on their
findings.
3.
Have students research the IN operator and use the operator in a query.
LECTURE
NOTES
·
Define
sort, sort key, major key, primary sort key, minor key, secondary sort key
·
Discuss
the reasons for sorting records
·
Use
Figure 2-34 to illustrate clearing the design grid
·
Use
Figures 2-35 through 2-37 to illustrate sorting data in a query
·
Use
Figures 2-38 through 2-40 to illustrate omitting duplicates
·
Use
Figures 2-41 and 2-42 to illustrate sorting on multiple keys
·
Use
Figures 2-43 and 2-44 to illustrate creating a top-values query
TEACHER TIPS
Students often have a difficult time
understanding the difference between a major key and a minor key. Emphasize
that the major key must be to the left of the minor key. Mention that it is not
possible to sort on an asterisk. To sort a query that includes an asterisk, add
the individual fields that must be sorted to the design grid and remove the
check mark from the Show check box.
If students right-click a field and a property
sheet other than the Query Properties sheet appears, it probably is because
they did not right-click in the second field of the grid. Close the sheet that
appears, and position the mouse in the second field of the grid.
It is very important that students either close
a query or reset the value in the Return box to All after they complete a
top-values query. Otherwise, the next query they create may have incorrect results.
FIGURES
and TABLES: Figures — 2-34, 2-35, 2-36, 2-37, 2-38, 2-39, 2-40, 2-41, 2-42,
2-43
BOXES:
1.
BTW: OR Criteria in a Single Field. Explain how to combine two criteria with OR
in a single field.
1.
Other Ways: Encourage your students to explore other ways of opening the
property sheet.
TEACHER TIPS
Students often have problems sorting on multiple
keys because they do not understand the phrasing of sort questions. For
example, in the phrase city within state, the major sort key is state
which follows the word within. In this type of query, however, it is usual to
display the city field before the state field. To display city before state but
sort by city within state, add the state field again to the design grid and
remove the check mark from the first state field (major sort key). The Access
Help system provides additional information on the procedure.
CLASSROOM
ACTIVITIES
1.
Critical Thinking: Sorting means ordering records in a particular way. When
would it be useful to sort data in ascending order? Why? When would it be
useful to sort data in descending order? Why?
AC 97: Plan Ahead Box (Critical Thinking);
Determine Whether Special Order Is Required
·
Determine
whether sorting is required
·
Determine
the sort key(s)
·
If
using two sort keys, determine major and minor keys
·
Determine
sort order
·
Determine
restrictions
LECTURE
NOTES
·
Explain
about joining tables using Figure 2-45
·
Use
Figures 2-46 through 2-49 to describe how to join tables
·
Review
the step to save a query using Figure 2-50
·
Use
Figures 2-51 through 2-53 to describe how to change join properties
·
Review
the steps to create a report involving a join using Figures 2-54 through 2-57
·
Review
the step to print a report
·
Use
Figures 2-58 and 2-59 to describe how to restrict records in a join
FIGURES:
2-45, 2-46, 2-47, 2-48, 2-49, 2-50, 2-51, 2-52, 2-53, 2-54, 2-55, 2-56, 2-57,
2-58, 2-59
BOXES:
1.
BTW: Join Types. Review the different
types of joins.
TEACHER TIPS
There are two primary reasons why Access would
not add a join line between two related tables: (1) the fields do not have
matching names or (2) the data type and data length are not identical on both
fields. If fields are identical but do not have matching names, students should
click the appropriate field in one of the tables and drag a join line to the
matching field in the other table. If the fields do not have identical data
types and data length, students should open the table that is in error in
Design view, correct the problem, and then create the query. If students do not
correct the error now, they will be unable to create the relationship between
the Recruiter and Client tables in Chapter 3.
The specific type of join shown in this chapter
(option 2) is a right outer join because all the records in the one table in
the relationship will display. Changing the join type to option 3 would create
a left outer join because all records in the many table in the relationship
would display.
AC 104: Plan Ahead Box
(Critical Thinking); Determine Whether More Than One Table Is Required
·
Determine
whether more than one table is required
·
Determine
the matching fields
·
Determine
whether sorting is required
·
Determine
restrictions
·
Determine
join properties
TEACHER TIPS
Use Figure 2-45 to explain joining to students.
One of the key features of relational database management systems is the
ability to join tables on matching fields.
CLASSROOM
ACTIVITIES
1.
Assign a Project: Divide the class into small groups. Assign each group a
different database. Example databases include: University database (Students, Teachers,
Courses); Employment database (Employees, Departments); Bookstore database
(Books, Authors, Publishers); and Video Store database (Movies, Directors,
Actors, DVDs). Ask students to create sample questions that would rely on data
from more than one table.
2.
Critical Thinking: Identify some reasons for changing the join type properties.
LECTURE
NOTES
·
Explain
calculated field
·
Use
Figures 2-60 through 2-63 to describe how to use a calculated field in a query
·
Use
Figures 2-64 and 2-65 to review the steps to change a caption
·
Define
aggregate function and discuss the different built-in functions
·
Use
Figures 2-66 through 2-69 to review the steps to calculate statistics
·
Use
Figures 2-70 through 2-72 to describe how to use criteria in calculating
statistics
·
Use
Figures 2-73 and 2-74 to discuss grouping and review the steps to use grouping
FIGURES
and TABLES: Figures — 2-60, 2-61, 2-62, 2-63, 2-64, 2-65, 2-66, 2-67, 2-68,
2-69, 2-70, 2-71, 2-72, 2-73, 2-74
BOXES:
1.
BTW Expression Builder. Have students
read the information on the Expression Builder.
2.
Other Ways: Encourage your students to explore other ways to open a property
sheet.
TEACHER TIPS
Review the mathematical operators that can be
used in calculated fields. When more than one mathematical operator appears in
an expression, multiplication and division are performed before addition and
subtraction. To change the order of precedence, use parentheses. Make sure
students understand that only field names must be enclosed in brackets. If
constants, such as .05, are used in an expression, they are not enclosed in
brackets.
Explain the aggregate functions that Access
supports:
|
COUNT |
Determines
the number of items or values. |
|
SUM |
Adds
the values. |
|
AVG
(average) |
Determines
the arithmetic mean of values. |
|
MAX
(largest value) |
Determines
the greatest value. |
|
MIN
(smallest value) |
Determines
the smallest value. |
|
STDEV
(standard deviation) |
Measures
how widely values are dispersed from the average value. |
|
VAR
(variance) |
Squares
the standard deviation. |
|
FIRST |
Determines
the first item or value. |
|
LAST |
Determines
the last item or value. |
AVG, SUM, STD, and VAR can be used only with
numeric fields. An aggregate function is different from a calculated field. A
calculated field is a mathematical operation performed on individual records.
An aggregate function is performed on groups of records.
AC 113: Plan Ahead Box (Critical Thinking); Determine Whether
Calculations Are Required
·
Determine
whether calculations are required
·
Determine
a name for the calculated field
·
Determine
the format for the calculated field
TEACHER TIPS
Point out to students that storing data that
easily can be calculated from other fields wastes space. It also makes the
database more difficult to maintain.
CLASSROOM
ACTIVITIES
1.
Class Discussion: Use the JSP Recruiters database to determine if additional
calculated fields are useful. Would any calculated fields be useful in the
Recruiter table.
LAB
ACTIVITIES
1.
Have students use the expression builder to create queries that use calculated
fields. Students should report on how useful they found the expression builder.
LECTURE
NOTES
·
Discuss
crosstab queries using Figure 2-75
·
Use
Figures 2-76 through 2-81 to review the steps to create a crosstab query
·
Describe
how to customize the Navigation Pane using Figures 2-82 and 2-83
·
Review
the step to quit Access
FIGURES
and TABLES: Figures — 2-75, 2-76, 2-77, 2-78, 2-79, 2-80, 2-81, 2-82, 2-83
BOXES:
1.
BTW Certification: For more information
on the MCAS program see Appendix F or visit the Access 2007 Certification Web
page.
2.
BTW Quick Reference: Point out the
location of the Quick Reference Summary and the Access 2007 Quick Reference Web
page.
TEACHER TIPS
Use Figure 2-75 to
help students understand the row and column data. Crosstab queries can be
created in Design view but it is much easier to use the Crosstab Query Wizard.
§
Learn
It Online is a series of online student exercises that test your knowledge of
chapter content and key terms.
§
Apply
Your Knowledge is a student assignment that helps you to reinforce the skills
and apply the concepts you learned in this chapter.
§
Extend
Your Knowledge is a student assignment that challenges you to extend the skills
you learned in this chapter and to experiment with new skills. You may need to
use Help to complete the assignment.
§
Make
It Right is a student assignment that requires you to analyze a presentation
and correct all errors and/or improve the design.
§
In
the Lab (Lab): In the Lab is a series of student assignments that ask you to
design and/or create a presentation using the guidelines, concepts, and skills
presented in this chapter. The assignments are listed in order of increasing
difficulty.
§
Cases
and Places is a series of student assignments where you apply your creative
thinking and problem solving skills to design and implement a solution.