microsoft access 2007 lesson 3: creating...

Access 2007 Lesson 03: Creating Reports

2/9/2008 1

Microsoft Access 2007

Lesson 3: Creating Reports

In the previous lesson the information you retrieved from a database always was in the form of a

table. This may be all you need if you are the only person using the information – if you set up

the tables in the first place then you won’t have much trouble working with them. Frequently you

will be writing queries for other people to use, though, in which case it is a good idea to display

the query results in an attractive form that is easy to understand. Such displays are called

Reports.

Creating a Report Using the Report Wizard

Let’s create a report that lists each city and, under the city, lists employees living in this city with

their phone numbers.

We generally have shied away from Wizards in these lessons, but the Report Wizard is genuinely

useful.

Open the database ExampleDB

and select Reports Wizard

under the Create tab.

In the next window you will specify

what fields you are using as the basis

for the report.

First you need to select the table or

query on which the report will be

based. Click on the arrow icon at the

right of the Tables/Queries text

window, which will display all the

tables and queries created for this

database.

Choose the table tblEmployees,

which contains the information we

need.


2/9/2008 2

You now will see a list of available fields that were

included in this table. At the right of the Available

Fields list are four buttons:

> Add one field to the Selected Fields list

>> Add all Available Fields to the Selected

Fields

< Remove one field from the Selected

Fields list

<< Remove all fields from the Selected

Fields list

For our report we want a list of employees and their phone number for each city in the database.

We will need information from the following fields to create the report: City, First Name, Last

Name, and Phone Number.

At this point it helps to give a little thought to what the report will look like. Each “City” field

name is going to be seen several times – once for each resident. To make the report easy to read,

it would be best to group the name and phone number information under each city:

City #1

Names & phone numbers

City #2

Names & phone numbers

etc.

With this in mind, let’s select the City field first

because it appears to be the most important.

Do the same for the First Name, Last Name and

Home Phone fields.

Click on Next.


2/9/2008 3

Next you will be asked if you want to add any

grouping levels to the report.

As we suggested above, the report will be more

attractive and easy to read if we group the name and

phone number information under each city.

Select City as a grouping level.

You now will see City grouped above the other

fields.

Click Next.

In the next window, you will be able to sort the

information according to the different fields. Let’s

sort the records first by Last Name and then by First

Name, both in ascending order.

Click on Next.


2/9/2008 4

Next, you can choose the layout of the report.

A Stepped layout probably would be best for

this report.

Leave the Page Orientation set to Portrait for

the moment. We can always change this later.

Click on Next.

Choose a style. Take your pick here.

Finally, enter a Title for the report. Use “rpt”

as a prefix for the title of reports.

You also can specify if you want to preview or

modify the report.

Click on Finish when you are ready.


2/9/2008 5

If you preview the report, you will see the preview next. Note that the report uses the captions

you chose as field properties in the table to label the fields in the report.

Click on Close Print Preview. When you close the preview you will see the Design View of the

report. Don’t worry about this.


2/9/2008 6

When you looked at the Print Preview, you may have noticed that the “City”

column is not wide enough to show the city names. We need to change the

layout of the report.

Select the Layout View from the View menu.

Drag the City column

borders until they

show the names of all

the cities

We also would like to

change the title of the

report. Double-click

on the text box

containing the title

and change it to

“Cities and Phones.”

Also, pull down the

bottom of the title

text box to separate the title

from the table below.

Close the report window. When asked if you want to change the design of the report answer Yes.


2/9/2008 7

Creating a Report from a Query

It often is preferable to create a report from a query you already have created.

Use the query, qryEarlyHire, that you created in the previous lesson to create the report shown

below.

The report style is Civic

The report was saved with the name rptEarlyHire

The column widths have been changed

The text in the Hire Date column has been centered

The report title has been changed

Without creating a totally new query or report, produce the report shown below.

Hint: Think about modifying a criterion for the query, running the query again and saving it.

Then preview the report again.


2/9/2008 8

Create a new query, named “qryExemptOrNon” and use it for a report, a portion of which is

shown below.

The report style is Concourse

Save the report with the name rptExemptOrNon

Adjust the column widths

Change the title of the report and the size of the title’s text box

Change the label on the “E or N” column


2/9/2008 9

Adding a Field to a Report

Open the report, rptCitiesPhones.

Suppose that you would like to add a Zip Code to this report.

Go to the Layout View and adjust the column sizes to make room for another column in the

report.

Select the Home Phone column. We will add another column corresponding to the Zip Code

field to the right of this column.


2/9/2008 10

Under the Format tab, click

on Add Existing Fields.

Double-click on the ZipCode field in the list of fields.

You should see the new field in the report.


2/9/2008 11

Moving a Column in a Report

Suppose you would like the report to look like the below.

Go to Layout View and drag the Zip Code column to the left until it is between the City and Last

Name columns. Center the text in the Zip Code column.

You also can change colors, fonts, and text size in the Layout View. Experiment a little with this

to see how it works.


2/9/2008 12

Mail Merge Reports

A mail merge report is good example of the

power of a database to make our lives easier.

Suppose that you need to write a personalized

letter to all employees that have been with your

company more than ten years.

You could type multiple copies of the letter, one

for each employee, but that would take a lot of

time. A mail merge allows you to type the letter

up once and make it part of a report that merges

in needed information (names, addresses, etc.)

from a database.

Step 1: Create a Word Document

Earlier you downloaded this letter,

“Celebration Letter,” on your H: drive.

Celebration Letter looks like the picture at the

right. Note that a series of Xs have been

entered in the position where you would place

the inside address and greeting, as well as the

department name and hire date. When we do

the mail merge, information from the database

automatically will replace the Xs in the letter.


2/9/2008 13

Step 2: Create a Query

We will work with the EmployeesDB database. Create a query, the first few records of which are

shown below:

The criterion for this query is that the Hire Date is before January 1, 1995. Save the query as

qry1995.

Step 3: Mail Merge With the Word Document

Display the list of queries

for this database and

choose the one you have

created for the mail merge,

in this case qry1995.

Under the External Data

tab choose Export, More

and then Merge it with

Microsoft Office Word.


2/9/2008 14

In the next window, check Link your data to an

existing Microsoft Word document.

Click on OK.

Select the document, in this case the

Celebration Letter you downloaded earlier to

your H: drive.

You then will see a Word document showing the letter with the Mail Merge menu on the right,

as is illustrated below.

Look at the Mail Merge

menu on the right side

of the screen. The

default way to select

recipients is from an

existing list.

In this case, we see that

the recipients are being

selected from the query

qry1995 in the

ExampleDB database.

Click on Next: Write

your letter at the

bottom.


2/9/2008 15

Select the Inside

Address Block in the

letter.

Click on Address

block in the Mail

Merge menu.

In the Insert Address Block window,

select how you want the Inside Address

to look.

The choices for this particular letter are

illustrated at the right.

When you have made your choices click

on OK.

NOTE: Sometimes the string of Xs in the letter are not removed when you choose the

<<AddressBlock>>. If they are not, delete them.

Next select the Greeting Line in the letter and click

on Greeting line in the Mail Merge menu.

Choose a greeting line and click on OK.


2/9/2008 16

We need to fill in the name of the

department and the hire date yet.

Highlight the XXXXXX

representing the department name

in the letter.

Click on More items in the Mail

Merge menu.

You will see a list of fields

available from the query. Select

Dept Name and click on Insert.

After you have inserted the Dept Name field, a Close button will appear at the bottom of the

window. Click on Close.

Insert the Hire Date

field in the same way.

At this point, your

letter should look like

this.

Click on Next: Preview your letter at the bottom of the Mail Merge menu section.


2/9/2008 17

You will see the letter

as it will appear for the

first recipient in the

query.

You can preview the merged letters if you like. You also will have the

opportunity to edit the recipient list at this time.

When you are ready click on Next: Complete the merge.

Save the merged Word document at this point.

The next Mail Merge menu will prompt you to print the letters. Don’t print

anything for this lesson. It would waste a lot of paper.

Close the Word document when you are through.


2/9/2008 18

Using a Saved Word Document Created by a Mail Merge

You probably will want to use a Word document created by a mail merge more than once.

Open Microsoft Word and load the Word

document that you saved a moment ago. You

will be asked if you want to open the document

and place data from the database in it. Choose

Yes.

If you were printing the letters, under the Mailings tab, you would choose Finish & Merge and

Print Documents. Don’t actually print anything for this lesson

You can specify what records you want to include in the printing.

.


2/9/2008 19

In Class Practice: You are going to have records for sale at a convention and want to

create a sign that you can post showing information about each record. Open the

Records database that you created in the first lesson. Do the following.

1. Create a query that will list the records by label, number, and artist showing their

condition and value. List only records that are worth $10.00 or more. Sort by value in

descending order. Save the query as qryTenDollars.

2. Use this query to produce a report that looks like the one at the right. Save the report

as rptTenDollars

3

3. Create a Microsoft Word document that looks

like this one.

Use Word Art to create the “Records for

Sale” logo at the top.

The picture of a 45 rpm record is

available for download on the Access

lessons web site.

Put a $ sign before the string of Xs in

the Value line.

Save the document in your H: drive


2/9/2008 20

4. Do a mail merge using the qryTenDollars

query you created in part 1.

When you choose the Xs to insert the

value, don’t select the $ sign.

You may find the case of the Condition

is lower case. If so, use Format, Font on

the Word menu to change the font to All

Caps.