New Perspectives on Microsoft Office Access 2003 Tutorial 5

1
Microsoft Office Access 2003

• Tutorial 5 Enhancing a Tables Design and
  Creating Advanced Queries

2
Create a Lookup Wizard field in a table

• To make a form easier to use, you will often add
  a Lookup Wizard to the form.
• Instead of remembering a particular value, you
  can pick the value from a list.
• This approach insures that you cannot enter an
  invalid value.
• When you create the Lookup Wizard, you will
  provide a list of acceptable values from which
  other users will make a selection.

3
Start the Lookup Wizard process

• To start the Lookup Wizard process
• Start Access and open the database to be modified
• Open the desired table in Design view
• Find the field you want to modify as a Lookup
  Wizard
• Click on the right side of the Data Type text box
  for the field, and then click the Lookup Wizard
  option
• The first Lookup Wizard dialog box will appear

4
Lookup Wizard dialog box 1
5
Lookup Wizard dialog box 2
6
Lookup Wizard dialog box 3
7
A lookup field in Datasheet view
8
Display related table records in a subdatasheet

• You can form a relationship between two tables
  based on a common field.
• You can display the data from the related table
  as a subdatasheet in the primary table's
  datasheet.
• To see the subdatasheet, simply click the expand
  indicator in the row for which you'd like to see
  the subdatasheet.

9
Illustration of a subdatasheet
10
Create an input mask for a table field

• Some fields require special characters to make
  them more readable. A phone number, for example,
  often includes parentheses and/or dashes. You can
  create an input mask that will display these
  characters as a predefined format.
• This predefined format can be used to enter or
  display data in a field.
• You can set up literal display characters that
  will automatically appear in a field. When you
  provide literal display characters, the user will
  not need to enter those characters.
• The Input Mask Wizard makes it easy for you to
  create an input mask.

11
An input mask for a phone number field
12
Input Mask character descriptions
13
Define data validation criteria

• You may want to view only records that match a
  particular criteria. You can do this by creating
  a query that uses a pattern match.
• You can use the like comparison operator using
  the asterisk (), the question mark (?), or the
  number symbol(). These wildcard characters allow
  you to create much more flexible patterns.
• You can also create a List of values match by
  creating a list of valid values and then using
  the In comparison operator to define a condition
  with two or more values. The non-matching values
  select records that do not match the criteria
  specified.

14
A design grid with a pattern match field
15
Use a list of values to expand the selection
possibilities
16
Use both the And and Or logical operators in the
same query

• There will be circumstances where you will want
  to use both the And and Or logical operators in
  the same query.
• The In operator naturally creates an Or
  condition. You can also use the key word Or in
  the criteria row to create an Or condition.
• Placing two conditions on the same row of the
  Criteria in the Query design grid creates an And
  condition.
• Placing two conditions on separate rows in the
  Query design grid creates an Or condition.

17
Sample design grid with And and Or conditions in
the same query
18
The query result for the combined use of And and
Or
19
Create a parameter query

• Sometimes when you create a query, you don't know
  exactly which records the user might want to see.
  
• To allow flexibility in the query, you can create
  a parameter query.
• The parameter query will prompt the user to enter
  the value they want to use to select records.
• Once the user has supplied this information,
  those records that match the value will be
  displayed in the query datasheet.

20
Defining a parameter query
21
Creating a Crosstab Query

• A crosstab query performs aggregate function
  calculations on the values of one database field
  and displays the results in a spreadsheet format.
• An aggregate function performs an arithmetic
  operation on selected records in a database.
• To access the crosstab query wizard, create a new
  query and select the Crosstab Query Wizard option
  from the New Query dialog box.

22
Aggregate Functions
23
The Crosstab Query Wizard dialog box 1
24
The Crosstab Query Wizard dialog box 2
25
The Crosstab query recordset
26
The Find Duplicates Query

• The find duplicates query finds duplicate records
  in a table or query.
• Locating duplicates helps avert potential
  problems (assigning two different product numbers
  to the same product).
• To create a Find Duplicates Query, select the
  Find Duplicates Query Wizard in the New Query
  dialog box when you create a new query.

27
The Find Duplicates Query
28
The Find Unmatched Query

• A find unmatched query is a select query that
  finds all records in a table or query that have
  no related records in a second table or query.
• You can, for example, find all customers who have
  not placed an order.
• To create a Find Unmatched Query, select the Find
  Unmatched Query Wizard in the New Query dialog
  box when you create a new query.

29
The Find Unmatched Query dialog box
30
The Top Values Query

• This query allows you to limit the number of
  records that display as a result of a query.
• Top Values property
• Create a query in design view with the necessary
  fields and sorting. Enter the number of records
  (or percentage of records) you want selected in
  the Top Values text box on the Query Design
  toolbar and then click the Run button.

31
The Top Values Query