Microsoft Access 2002

1
Microsoft Access 2002

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

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
This dialog box allows you to specify a list of
allowed values for the selected field that
already exist in a table or query in the
database.
Click the first option button, then click the
Next button.
You can also type in the values if you wish to do
so.
5
Lookup Wizard dialog box 2
This dialog box lets you select the specific
fields from the table you have already selected.
In this case, two fields have been selected.
Click on a field to highlight it.
Then click the select button to move it to the
right-hand pane.
6
Lookup Wizard dialog box 3
This dialog box allows you to adjust the width of
the lookup field.
Field values for the lookup field from the
database table are shown here.
Position the cursor and double-click to adjust
field width, then click the Finish button.
7
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.
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
The figure below illustrates how a table would
look when a subdatasheet has been expanded.
Notice that this figure shows the Employer
information in the main datasheet and that the
subdatasheet contains related records in the
Position table.
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
A phone number, for example, often requires
parentheses around the area code and a dash after
the first three digits of the number. The input
mask in the figure below shows where these
parentheses and dashes will be placed.
12
Input Mask character descriptions
This figure shows the input mask characters that
are available and the meaning of each character.
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
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.
15
Use a list of values to 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.
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
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.
18
The query result for the combined use of And and
Or
The figure below shows the datasheet for the
previous design grid. 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.
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
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.
21
Design and create a custom form

• You can create a custom form by modifying an
  existing form or creating a new form. Either way,
  the custom form is created in the Form Design
  window.
• You can create a simple form or a much more
  complex form with multiple pages.
• You place objects on the form, called controls,
  that will be either bound or unbound controls.
• Bound controls are linked to a particular field
  in the underlying table.
• Unbound controls are not linked to any particular
  field.
• An example of an unbound control is a label
  control that is on the form simply to provide
  some information on the form

22
Plan your custom form first
When designing a custom form, its a very good
idea to draw your design on paper before you
begin.
The developer of this design has placed X's on
the design to indicate not only where the fields
will be placed, but also how long each field will
be.
This design shows a multi-page form.
23
Add, select, move, resize, delete, and rename
controls

• You can add and delete controls as you wish.
• When you add a bound control to the form, you
  will see both a text box and a label.
• The text box is where the data will appear when
  the form is displayed
• The label will contain the name of the field
• To move a control, you must first select it
• When a control is selected, sizing handles will
  be displayed on the control
• The handle on the upper-left side of the control
  is its move handle. Drag the move handle to the
  position you want to place the control
• Click on any of the remaining sizing handles and
  drag to the position you want to resize the
  control
• You can rename the control by changing the
  control's name property to the name you wish to
  assign to it

24
Resizing and moving controls
In order to move or resize a control, you must
have that control selected. In the figure below,
the EmployerName text box is selected and has
just been moved and resized. When you see the
sizing handles, the control is selected and can
be moved around on the form.
25
Add Form Headers and Footers

• To create a form header, you must add a form
  header and form footer section to the form.
• To add these sections to your form, select Form
  Header/Footer from the View menu.
• If you do not want to have one or the other (the
  header or the footer section), you can reduce its
  height to zero, which will eliminate it from the
  form's display
• Once you have these sections, you can add labels
  to them to document the form.
• When creating a title on the form, you will often
  want to change its font size and weight to make
  it stand out on the form.

26
Form Design view with header and footer sections
shown
Add header and footer sections by clicking View
on the menu bar and then clicking Form
Header/Footer.
To eliminate the Header or Footer sections, drag
the bottom line of that section so that its width
is zero
27
Add a graphic image to a form

• To enhance the appearance of a form, you may
  choose to add a picture to the form.
• The image tool on the toolbox will allow you to
  create a control that contains a graphical image.
  
• When you draw the image control on the form, you
  will be prompted to supply a name of a valid
  graphic image file.
• Whatever size you set for the control will
  determine the size of the picture on the form.

28
A form with a graphic image inserted
This form has had an image inserted into the Form
Header section.
29
Use Control Wizards to create a multi-page form
with a subform

• Because Access is a relational database you will
  often want to create multi-page forms.
• You can insert a page break control or a tab
  control to accomplish this task.
• The tab control places two tabbed pages, by
  default, on your form.
• Once the tab control is in place, you can add
  controls to any of the tabs just as you would to
  the form.
• If you want to add a form to one of the tabs, you
  can use the Subform/Subreport Wizard.

30
A multi-page form with tab controls
In this figure, a tab control has been added to
the form. Each tab contains information from
whatever table(s) you choose.
31
A Form tab with controls added
This figure shows controls added to the first tab
of the tab control. More tabs can be added if
needed to display more information.
In this case, Employer data will be displayed on
the first tab, and Positions data on the second
tab. Positions data will appear as a subform on
the second tab. This provides a great deal of
information on a single form. The user can click
the tab for the data they want to see.
32
Use Filter By Form

• Filter By Form can be used to specify which
  records should be displayed in the forms.
• When you click on the Filter By Form button, a
  blank form is displayed.
• In this blank form, you can enter values that
  will become the criteria that will determine
  which records are displayed.
• You can save the filter as a query that can be
  used to view the information later.
• When the filter has been saved as a query, you
  can reapply it to a form as desired.

33
Filter By Form example
In this figure, the user specified they want to
see records whose State field contains RI or
whose NAICSDesc field contains Tour Operators.
When the Apply Filter button is clicked, only
those records that match this criteria will be
displayed in the forms.