Microsoft Access 2002

1
Microsoft Access 2002

• Tutorial 5 Enhancing a Tables Design, and
  Creating Advanced Queries and Custom Forms

2
New Data Files!

• Dont work on the file you created so far
• Go to Tutorial.05 folder

3
Preventing Input Errors

• Different techniques
• Lookup Wizard
• Pick value from list
• Input Mask
• Input is always on correct form, cant enter
  letters in number field etc.
• Validation Criteria
• Anything can be entered, but only specific values
  are accepted by Access

4
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.

5
A lookup field in Datasheet view
When the user selects a description, Access will
return the correct code that corresponds to the
description. This figure below shows what the
Lookup field looks like in the table datasheet.
In this example, the NAICS description is
displayed.
When you select a description, Access will lookup
the code that goes with the descriptions.
6
Input mask for a table field

• Some fields require special characters to make
  them more readable.
• Example phone number, [phone]
• 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.
• If literal display characters are defined, the
  user will not need to enter those characters.
• Use the Input Mask Wizard

7
Input Mask character descriptions
This figure shows the input mask characters that
are available and the meaning of each character.
More characters/options available. Press F1 to
see all.
8
Data Validation

• Demonstrate in Jobs database

9
Pattern Match Query

• 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.
• Wildcard characters
• Match with any number of characters
• ? Match a single character
• Match a single digit
• Use with like operator
• Like 508
• Like ab?cd (give an example of a string that
  matches)
• Can also use with in operator
• In (ME, NH, WI)
• In (Ma, DE?)
• Not In (ME, MA)

10
A design grid with a pattern match field
This figure illustrates a criteria that specifies
that only records whose phone numbers begin with
508 will be included in the datasheet. Notice
that the following the 8 creates a wildcard,
meaning that anything that follows the 508 is
acceptable.
11
A list of values expand the selection
possibilities
This design grid shows a field with a list of
values. As long as the State value in any record
is one of those in the list, the record will be
included in the datasheet.
12
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.

13
And and Or conditions in the same query
Placing conditions in the same criteria row
creates an AND condition. In this example, both
criteria also create OR conditions, resulting in
a match condition using AND and OR.
14
Result for the combined use of And and Or
The criteria for the State field specified that
the State must be either MA or NH. The second
criteria, for the Position Title field, specified
that the values must be Waiter/Waitress or
Kitchen Help. The criteria, in this case, uses
the Or operator. This criteria could have also
been created by placing the two positions on
separate lines in the query design grid.
15
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.

16
Defining a parameter query
Note that the parameter query is enclosed in
brackets. This text will be the user prompt when
the query is run.
The figure below shows a parameter query placed
in the criteria row.
When the parameter query is run, a dialog box
will be displayed asking the user to enter a
value that they want to specify for the criteria.