XP
Microsoft Office Access 2003
Tutorial 2A: – Querying a Database
(Tutorial 2B starts on slide 27)
New Perspectives on 1
Microsoft Office Access 2003 Tutorial 3
XP
Learn how to use the Query
window in Design view
• Use the Query window in Design view to create a
query.
• Download NEJobs database.
• Start Access and open the NEJobs database.
• Click Queries.
• Doubleclick Create query in Design view.
• Select Employer and click Add.
• Then click Close.
New Perspectives on 2
Microsoft Office Access 2003 Tutorial 3
XP
Create, run, and save queries
• From the fields list, either drag or double-click fields (e.g., EmployerID) that
you want included in the query.
– A query result differs from a table's datasheet view in that only selected fields are
displayed
• If you move a field to the design grid and then you want to remove it, you can
click on the field's column, select it, and then press the delete key.
• The results of the query will be displayed in order by the primary key of the
table unless you specify another sort order.
• You can save the query and give it a name by pressing the save button on the
toolbar.
• Similarly add City, ContactFirstName, ContactLastName, and Website
fields to the design grid in that order.
• Then click Run (!).
• Save the query as EmployerAnalysis.
New Perspectives on 3
Microsoft Office Access 2003 Tutorial 3
XP
Selecting, displaying and sorting fields
New Perspectives on 4
Microsoft Office Access 2003 Tutorial 3
XP
A sample query datasheet
New Perspectives on 5
Microsoft Office Access 2003 Tutorial 3
XP
Update data using a query
• 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, close the
query and open the table in datasheet view.
New Perspectives on 6
Microsoft Office Access 2003 Tutorial 3
XP
Updating Data Using a Query
• For the record with EmploerID 10135, click the check box
in the Website field to place a check mark in it.
• For the record with EmployerID 10152, change the
ContactFisrtName field value to Mary and the
ContactLastName to Grant.
• Click the close window button (X).
• Now open Employer table to see if the changes you made
to records 10135 and 10152 were indeed made.
New Perspectives on 7
Microsoft Office Access 2003 Tutorial 3
XP
Creating a Multi-table Query
• Click Queries.
• Doubleclick Create query in Design view.
• Select Employer and click Add.
• Select AvailablePosition and click Add. Then click the
Close button.
• Place EmployerName, City, and StateProv fields from
Employer table and Openings, PositionTitle, StartDate,
and EndDate from AvailablePosition.
• Then click Run (!). Save this query as EmployerPositions.
New Perspectives on 8
Microsoft Office Access 2003 Tutorial 3
XP
Sort data in a query
• You can control the order of data displayed in a query by altering the
queries sort key. Choose EmployerPositions and click Design.
• Choose a field (e.g. EmployerName) on which you want the data to
be sorted.
• Click below EmployerName in the Sort row and then specify whether
you want the data sorted in ascending or descending order.
• Specify multiple fields (such as EndDate field in addition to
EmployerName) as the sort key if you want to arrange data in sorted
order within groups of data. Specify sort criteria for the query in the
query design grid.
• Click Run (!).
New Perspectives on 9
Microsoft Office Access 2003 Tutorial 3
XP
Sort results by data type
New Perspectives on 10
Microsoft Office Access 2003 Tutorial 3
XP
Sort single or multiple fields
New Perspectives on 11
Microsoft Office Access 2003 Tutorial 3
XP
Filter data in a query
• Sometimes you may want to isolate a certain portion of
data in your query results.
• You can set a filter, or a set of restrictions, to specify what
records you want to view in the results. Only those records
that match the criteria will display in the results datasheet.
• There are two methods used to make your selections:
Filter By Selection or Filter By Form. See the figure on
the next slide. So if you want to filter by State/Prov, click
the firt time MA appears and select it. Then click Filter By
Selection. Click Remove Filter button to remove it.
• If you no longer want to view the data with the filter
applied you can remove the filter.
New Perspectives on 12
Microsoft Office Access 2003 Tutorial 3
XP
Using Filter By Selection
New Perspectives on 13
Microsoft Office Access 2003 Tutorial 3
XP
Specify an exact match
condition in a query
• You can specify an exact match condition, which allows
you to specify that only records that match your criteria
will be displayed.
• To specify an exact match, use the criteria row in your
query design grid. (see slide 17).
• When you run the query after specifying an exact match,
only those records that meet the criteria will be displayed.
• An exact match condition specifies that the value in the
specified field must match the condition exactly.
New Perspectives on 14
Microsoft Office Access 2003 Tutorial 3
XP
Exact match operators and results
New Perspectives on 15
Microsoft Office Access 2003 Tutorial 3
XP
Change a datasheet's appearance
• You can make several alterations to the way a query
datasheet is displayed.
• You can change the font type, the font size, and the size of
the columns.
• To alter the font of the datasheet:
– Click Format on the menu bar
– Select Font from the dropdown menu
– In the Font dialog box, select the font you want and also change
the size of the font
• Columns can be resized individually or you can select and
resize all of them at one time.
New Perspectives on 16
Microsoft Office Access 2003 Tutorial 3
XP
Use a comparison operator to
match a range of values
New Perspectives on 17
Microsoft Office Access 2003 Tutorial 3
XP
Use the And and Or logical operators
• When you need to use multiple conditions for a query,
you can 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
• When you enter two conditions on the same row of the
query design grid, an And condition is created.
• If you enter two conditions that are on separate rows, an Or
condition is created.
New Perspectives on 18
Microsoft Office Access 2003 Tutorial 3
XP
Illustration of And and Or logic
New Perspectives on 19
Microsoft Office Access 2003 Tutorial 3
XP
Creating And condition
in the design grid
New Perspectives on 20
Microsoft Office Access 2003 Tutorial 3
XP
Use multiple undo and redo
• In Access 2003, you can Undo and Redo multiple actions
at one time.
• While working in design view for tables, queries, forms,
and reports, you can use the undo recent actions button.
• The Undo button on the design toolbar has an associated
list box. You can open the list box and then select the
action you want to undo.
• If you undo an action and then change your mind, you can
also redo actions by the same method, using the Redo
button.
New Perspectives on 21
Microsoft Office Access 2003 Tutorial 3
XP
Using the Undo actions button
New Perspectives on 22
Microsoft Office Access 2003 Tutorial 3
XP
Perform calculations in a query using
calculated fields and expressions
• Queries can be built to perform a calculation as part of the
query.
• Expressions can be entered into the query design grid.
• Make certain that you are following the rules of
precedence.
• To perform a calculation in a query, you must add a
calculated field to the query design.
• You have three options for entering expressions:
– Enter the expression directly into the field text box
– Enter the expression in the Zoom box
– Enter the expression in the Expression Builder (click on Build
button)
New Perspectives on 23
Microsoft Office Access 2003 Tutorial 3
XP
The Expression Builder dialog box
New Perspectives on 24
Microsoft Office Access 2003 Tutorial 3
XP
A calculated field in
the query datasheet
New Perspectives on 25
Microsoft Office Access 2003 Tutorial 3
XP
Using Aggregate Functions
New Perspectives on 26
Microsoft Office Access 2003 Tutorial 3
XP
Microsoft Office Access 2003
Tutorial 2B – Creating Forms and
Reports
New Perspectives on 27
Microsoft Office Access 2003 Tutorial 3
XP
Create a form using the Form Wizard
• You can create a form from scratch or you can create a
form using the Form Wizard.
• The Form Wizard will lead you through a series of choices
to help you develop the form based on a table or a query.
• You will need to select the type of layout you want for
your form, the style of the form, and a name for the form.
• After you have selected the table or query on which the
form will be based, you will select which field(s) will be
used on the form.
New Perspectives on 28
Microsoft Office Access 2003 Tutorial 3
XP
Create a form using the Form Wizard
• Start Access and open the NEJobs database.
• Click Forms.
• Doubleclick Create form by using wizard.
• Select AvailablePositions.
• Then select fields from Available Fields and click >. You can select all
the fields by clicking >>.
• To remove a field, select the field and click one at a time. You
can select all the fields by clicking >>.
• To remove a field, select the field and click <.
• Now select AvailablePositions, and then select desired fields
(PositionID, PositionTitle, Wage, HoursPerWeek, Experince,
StartDate, EndDate, Openings)
• Click Next and follow defaults.
New Perspectives on 51
Microsoft Office Access 2003 Tutorial 3
XP
Grouping report data
New Perspectives on 52
Microsoft Office Access 2003 Tutorial 3
XP
Sorting report data
• You can sort the data on a particular field or on
several fields.
• If you choose to sort on two or more fields, the
grouping is in order as selected on the Sort Order
portion of the Report Wizard.
– If you choose to sort on State and then on City, the
report would be sorted on State and then within each
State group, the data would be sorted on City
New Perspectives on 53
Microsoft Office Access 2003 Tutorial 3
XP
The Report Wizard Sort dialog box
New Perspectives on 54
Microsoft Office Access 2003 Tutorial 3
XP
Choose a report layout
New Perspectives on 55
Microsoft Office Access 2003 Tutorial 3
XP
Insert a picture in a report
• You can insert a picture into a report to improve
its appearance.
• The picture can be from scanned images, images
created in Microsoft Paint, or a picture created in
some other graphic program.
• Once the picture is inserted into the report, you
can move it around and size it to your preference.
New Perspectives on 56
Microsoft Office Access 2003 Tutorial 3
XP
Insert a picture
• The picture you insert must be created before you
try to insert the picture.
• Be sure you know where the picture is located on
your disk before you begin to insert the picture. To
insert the picture:
– Click Insert on the menu bar
– Select Picture from the drop-down menu
– Navigate to the location of the picture and select it
– Press the OK button to insert it
New Perspectives on 57
Microsoft Office Access 2003 Tutorial 3
XP
Moving a picture in a report
New Perspectives on 58
Microsoft Office Access 2003 Tutorial 3
XP
Preview and print a report
• Before printing the report, you can view it in Print
Preview by pressing the Print Preview button.
• If the preview looks OK, you can print the report.
To print the report:
– Click the File menu, and then click Print
– In the Print dialog box you can:
• Print the entire report
• Print a selected number of pages
• Set other printing options such as number of copies
New Perspectives on 59
Microsoft Office Access 2003 Tutorial 3
XP
Preview your report before printing it
New Perspectives on 60
Microsoft Office Access 2003 Tutorial 3