Access 2007 Advanced Exercises

Access 2007 Advanced Exercises

Lead Consultants
C. Jacqueline Schultz, Ph.D.
Career and Business Education Instructor Warrensville Heights High School Warrensville Heights, Ohio

Linda Wooldridge, M.B.A.
School of Information Technology Instructor Santa Susana High School Simi Valley, California

glencoe.com

Screen Capture Credits Abbreviation Key: MS = Screen shots used by permission of Microsoft Corporation. ©2007 MS Access: MS Excel: p. 1; MS Access: pp. 2-24; Internet Explorer: p. 21.

Copyright © 2009 The McGraw-Hill Companies, Inc. All rights reserved. No part of this publication may be reproduced or distributed in any form or by any means, or stored in a database or retrieval system, without prior written consent of The McGraw-Hill Companies, Inc., including, but not limited to, network storage or transmission, or broadcast for distance learning. Microsoft, Microsoft Office, Microsoft Word, Microsoft Excel, Microsoft Access, Microsoft PowerPoint, Internet Explorer, and Windows and all other Microsoft names and logos are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Microsoft product screenshots reprinted with permission from Microsoft Corporation. Glencoe/McGraw-Hill is independent from Microsoft Corporation, and not affiliated with Microsoft in any manner. This publication may be used in assisting students to prepare for a Microsoft Business Certification exam. While this publication may be used in assisting individuals to prepare for a Microsoft Business Certification exam, Microsoft, its designated program administrator and Glencoe/McGraw-Hill do not warrant that use of this publication will ensure passing a Microsoft Business Certification exam. Between the time that Web site information is gathered and published, it is not unusual for some sites to have changed URLs or closed. URLs will be updated in reprints or on the Online Resource Center when possible. Printed in the United States of America Send all inquiries to: Glencoe/McGraw-Hill 21600 Oxnard Street, Suite 500 Woodland Hills, CA 91367 MHID 0-07-880263-6 (Student Edition) ISBN 978-0-07-880263-8 (Student Edition)

Exercise 1: Define Data Needs Exercise 2: Define Field Data Types Exercise 3: Modify Field Properties Exercise 4: Set Validation Rules Exercise 5: Define and Modify Primary Keys Exercise 6: Define and Modify Multi-Field Primary Keys Exercise 7: Define Tables in Databases Exercise 8: Create Tables Based on the Structure of Other Tables Exercise 9: Create and Modify Queries Exercise 10: Open Databases Exercise 11: Format and Modify a Chart Exercise 12: Import and Export Data Exercise 13: Set Printing Options

1 3 5 6 8 9 10

12 13 14 17 19 22

Table of Contents

iii

Step-By-Step
Choose Start>All Programs>Microsoft Office®>Microsoft Office Excel 2007. In Excel, click Office Click Open. Locate and open the data file Product Info.xlsx. Save as: Product Info-[your first initial and last name] (for example, Product Inforgupta). .

EXERCISE 1
Define Data Needs
When you begin to design a database, you should think about the types of data that you need to include. One way to prepare data use in a database is to set up your data fields in a spreadsheet program such as Microsoft Excel. When entering data in an Access database, consider whether the data should be calculated by the database or entered by the user. Stored data will remain the same until the user manually changes it. Calculated data changes in response to other data modifications. Examples of stored data and calculated data are shown in Table 1.1. TABLE 1.1 Examples of stored data and calculated data Stored Data
Product ID Product name

Calculated Data
Total value of all products in inventory. Total number of products in inventory. Total Value is calculated by multiplying the Product cost field by the Inventory field. Average monthly inventory levels.

Read each field name. Note that the fields Product ID, Product Name, Cost, and Inventory all contain stored data. Click in cell E2 in the Total Value field (see Figure 1.1). Your screen should look like Figure 1.1. Continued on the next page.
Product cost Inventory

FIGURE 1.1 Product Info worksheet

Field with calculated data

Cell E2

If a database stores customer address information, the data remains the same until a user goes into the database and changes it. If the database tracks the total value of a product, the value in inventory changes when the price changes.

Records Examples of fields with stored data

Exercise 1

Advanced Access

1

Step-By-Step
Click in cell D2 (see Figure 1.2). Select 1000. Key: 500. Press . Your screen should look like Figure 1.3. Note that the value in cell E2 changes to $132,600. Save and close the Product Info spreadsheet and exit Excel. Continue to the next exercise.

EXERCISE 1

(Continued)

Define Data Needs
FIGURE 1.2 Stored data in Product Info worksheet

Stored data, such as the price of a inventory in an automotive parts warehouse database, must be changed manually. Calculated data depends on other data. For example, the total value of all gaskets held in the warehouse’s inventory would change if either the price of head gaskets went up or the quantity of gaskets went up or down. You can calculate stored data in Excel and import it to Access. You also can create calculated and stored data directly in Access.

FIGURE 1.3 Calculated data changed

Change in cell D2 causes calculated value in cell E2 to change

Exercise 1

Advanced Access

2

Step-By-Step
Click Start>All Programs>Microsoft Office®>Microsoft Office Access 2007. Click Office Open. Navigate to the Phil’s Pick-a-Part database file. Ask your teacher how and where to copy the database before working in it. Select the database file and click Open. In the Navigation Pane, right-click the Product Info table and select Design View from the menu. In the Field Name column, click in the blank cell under Cost. Key: QtyInStock. Press . . Click

EXERCISE 2
Define Field Data Types
You can use various data types to organize the data in your database. Some data is best presented as text, while other data should appear as a number. Sometimes, data should be stored as a combination of both, or as one of two values, such as Yes or No. A Memo data type, which can store up to 65,535 characters, is best for storing long text data, such as a comment or note about a particular order or product. A Currency data type is best for storing monetary values. To ensure that data in a field supports searches or meets certain conditions, use a data type that supports a conditional expression, or Boolean operator, such as AND, OR, or NOT. One kind of Boolean operator is a comparison operator, such as equal, not equal, less than, more than, and so on. Common data types that support Boolean operators are shown in Table 1.2. TABLE 1.2 Common Data Types that Support Boolean Operators Text
Number Date/Time

Currency
Yes/No AutoNumber

FIGURE 1.4 Product Info Stock field added to parts database

Click the Data Type dropdown arrow Number. Your screen should look like Figure 1.4. Under Field Properties, click in the Caption box and key: Stock Quantity. Click Save . Click the . Navigation pane . From the Product Info table Data type drop-down arrow list of data types, select

Cost field and press Currency. Continued on the next page.

Change the Data Type to

Exercise 2

Advanced Access

3

Step-By-Step
Under Field Properties, click in the Format box. Choose Currency. Click Datasheet View Click Yes again. Your screen should look like Figure 1.5. Close the Product Info table. Open the Customer Info table in Design View. Click in the Notes field Data Type box. Click the drop-down arrow and select Memo. Your screen should look like Figure 1.6. .

EXERCISE 2

(Continued)

Define Field Data Types
FIGURE 1.5 Product Info Datasheet View

Click Yes to save changes.

New field defined and added

FIGURE 1.6 Customer Info Design View
Click Save . Close the Customer Info table. Continue to the next exercise.

The data types you assign will depend on how you want to use the data. A Date/Time data type stores dates. A Number data type performs calculations. The Memo data type saves notes. To ensure that data meets a condition, change a field’s data type to support searches or comparisons.

Exercise 2

Advanced Access

4

Step-By-Step
In your Phil’s Pick-a-Part database, in the Navigation Pane, double-click the Customer Info. Choose Home>Views> Design View .

EXERCISE 3
Modify Field Properties
Access allows you to set and modify properties for entire tables and for individual fields within a table. For example, by specifying the Field Size, or maximum number of characters that a user can enter in a field, you can ensure that data in the Phone Number field contains ten digits. You also can set a memo field as Append Only. With this option, users can add data to a field, but they cannot delete data from it. Common properties for fields in a table are shown in Table 1.3. TABLE 1.3 Common Field Properties
Allow Zero Length Specifies that a Text, Memo, or Hyperlink field can accept strings of zero length, or null values, with no characters. Specifies that data can be added to a Memo field, but that the existing data in the field cannot be overwritten. Specifies the name of a Text field. Specifies the maximum number of characters a user can enter in a field. Specifies that data must be entered in a field.

Click the Customer Name field. In Field Properties, click in the Field Size box. Key: 80. Press .

Click the row selector to the left of Telephone. While still holding the mouse button, drag down until the bold black line is above the Description field. Release the mouse. Scroll down the field names list and click in the Notes field. Scroll down the Field Properties and click in the Append Only box. Click the drop-down arrow and select Yes. Your screen should look like Figure 1.7.

Append Only Caption Field Size Required

FIGURE 1.7 Customer Info table edited

Customer Name field

Notes field Click Save table. Continue to the next exercise. . Close the

Access assigns a default field name if you do not enter a caption for a field.

Exercise 3

Advanced Access

5

Step-By-Step
In your Phil’s Pick-a-Part file, in the Navigation Pane, double-click the Customer Info table. Choose Home>Views> Design View .

EXERCISE 4
Set Validation Rules
You can use the Validation Rule property to restrict the type and amount of data users can enter into a field by creating a Boolean (or conditional) expression in the Validation field. Validation rules use conditional expressions to specify that the data meets certain criteria. You can use the Validation Text property to customize the error message that Access displays when data that is entered into a field violates a validation rule. Validation messages should contain information about the invalid data and how to fix the error. Examples of validation rules are shown on page 7 in Table 1.4. FIGURE 1.8 Customer Info table validation added

Click in the Account Start Date field box. Under Field Properties, click in the Validation Rule box and key: >=#01/01/2009#. Click in the Validation Text box and key: Date entered must be after January 1, 2009. Your screen should look like Figure 1.8. Choose Design>Views> Datasheet View . Click Yes to save changes. Your screen should look like Figure 1.9. In the Data Integrity warning box, click Yes. Validation rule Validation text

FIGURE 1.9 Customer Info table in Database View
Continued on the next page.

Exercise 4

Advanced Access

6

Step-By-Step
Scroll to the right until you can see the Account Start Date field. Click in the first record under the heading. Highlight the year 2009 in the date and key: 2006. Press . Your screen should look like Figure 1.10. In the warning dialog box, click OK. Highlight the date and key: 2009. Press .

EXERCISE 4

(Continued)

Set Validation Rules
FIGURE 1.10 Validation error dialog box

Invalid start date

Validation error dialog box with validation text

Close the table. Continue to the next exercise.

If you enter data into a field that violates a validation rule, Access prevents you from moving to another field until the problem is fixed. The Validation Text property tells you how to fix the error.

TABLE 1.4 Sample Validation Rules Validation Rule
>0 100 AND =[StartDate] >=#01/01/2008# [RequiredDate]Tools> Primary Key .

EXERCISE 5
Define and Modify Primary Keys
A primary key is a field that ensures that each record in a table is unique. By default, Access records in a table are sorted based on the primary key. In the Phil’s Pick-a-Part database, for example, the Customer ID acts as a unique identifier for each customer in the database. You can identify each customer uniquely because no two customers have the same Customer ID number. A field with the AutoNumber data type is often used as the primary key because the numbers assigned to the field increase automatically with each new record. Characteristics of a good choice for a primary key are shown in Table 1.5. FIGURE 1.11 Customer Info table Datasheet View

Click the record selector for the Customer Name field. Choose Design> Tools>Primary Key .

Choose Design>Views> Datasheet View Click Yes to save the changes to the Customer Info table. Your screen should look like Figure 1.11. Choose Design>Views> Design View Click Save . and close .

Customer Name alphabetized as Primary Key

Customer ID out of sequence when no longer Primary Key

the Customer Info table. Continue to the next exercise.

TABLE 1.5 Choosing a primary key A good candidate for a primary key… Some database fields would not make a good choice for primary keys. For example, you may have more than one John Smith in the Name field.
1. 2. 3. Is a value that uniquely identifies each record in the table. Is a field that is never empty. It always contains a value. Is a value that does not change.

Exercise 5

Advanced Access

8

Step-By-Step
In your Phil’s Pick-a-Part database, open the Product Info table in Design View. Click the record selector for the Product Name. Hold down the and click the record selector for Cost. Choose Design>Tools> Primary Key . key

EXERCISE 6
Define and Modify Multi-Field Primary Keys
A multi-field primary key is a table with two or more fields defined as the primary key. A multi-field key is used if a table has no single field that is appropriate to serve as the primary key. Although a primary key should include as few fields as possible, if a table has no single field with a set of unique values, two or more fields can be combined to create a unique value. In this exercise, the Product ID field in the Product Info table is not a suitable primary key. Multiple primary keys are assigned to the Product Name and Cost fields to fix this problem. Because there are no two products with the same name or price in the Phil’s Pick-a-Part database, the multiple primary keys assign a unique value to the relationship between these two fields. FIGURE 1.12 Product Info table multiple primary keys

Your screen should look like Figure 1.12. Primary key icon Choose Design>Views> Datasheet View Click Yes. Your screen should look similar to Figure 1.13. Click Design View . Two Primary Key fields selected .

Click the record selector for the Product ID field. Click Primary Key Click Close . on the

FIGURE 1.13 Product Info table Datasheet View

Product Info table. Select Yes to save changes. Continue to the next exercise.

A multi-field primary key is also referred to as a composite key.

Product Name now determines listing order

Exercise 6

Advanced Access

9

Step-By-Step
In your Phil’s Pick-a-Part database, open the Customer Info table. Scroll to the right until you see the Account Manager field. Read the Account Managers’ last names listed in the Customer Info table. Close the Customer Info table. Choose Create>Tables> Table .

EXERCISE 7
Define Tables in Databases
When you design a database, it should be structured correctly so that the data is accurate, easy to work with, and accommodates your needs. When you add new tables to any database, analyze your design for errors to see if your tables are normalized, or structured correctly without repeated groups of information. A welldesigned database typically contains 3NF tables. A 3NF table is a table that is normalized to the third order. This means that they comply with the first three rules of normalization. The three rules to create a 3NF table are shown in Table 1.6. The rules of form build on the previous rules, so a Third Normal Form table complies with all the rules of the first and second forms as well as the third form. TABLE 1.6 Rules of normalization Rule
1NF 2NF

Description of Rule
Each field in database table contains a single value, and the table has no repeating groups of information Each non-key field in the table must be dependent on the entire primary key (including multi-field primary keys) Each non-key field in the table is dependent only on the primary key

Choose Datasheet> Views>Design View key: Sales Info. Click OK. .
3NF

In the Save As dialog box

FIGURE 1.14 New Sales Info table
Your screen should look like Figure 1.14. Key: Account Manager. Press . Default Primary Key field Continued on the next page.

Table saved as Sales Info

Exercise 7

Advanced Access

10

Step-By-Step
Click the Data Type dropdown arrow and select Text. Press twice.

EXERCISE 7

(Continued)

Define Tables in Databases
FIGURE 1.15 Sales Info table 3NF fields Design View

Key: First Name. Press . Set the Data Type to Text. Press twice.

Key: Cell Phone. Click in the field below Cell Phone. Key: Employee Number. Press . Data Type drop-down arrow

Click the drop-down arrow and select Number. Press twice. Key: Client. Press .

Click the drop down arrow and select Text. Your screen should look like Figure 1.15. Choose Design>Views> Datasheet View . In the dialog box, click Yes to save changes to the table. Key the information into the table as it is shown in Figure 1.16. Click Close to close

FIGURE 1.16 Populated Sales Info table Datasheet View

All fields are dependent only on primary key

No repeating groups

the Sales Info table. Click Yes to save changes to table layout, if necessary. Continue to the next exercise.

Exercise 7

Advanced Access

11

Step-By-Step
In your Phil’s Pick-aPart database, in the Navigation Pane, rightclick the Sales Info table and select Copy. Right-click in an open area of the Navigation Pane and select Paste. In the Paste Table As dialog box, click in the Table Name box. Key: Customer Contacts.

EXERCISE 8
Create Tables Based on the Structure of Other Tables
Rather than using a table template, or taking the time to build a new table for your database using Design View or Datasheet View, you can use an existing table’s structure to create a new table. You can create a table by copying and pasting the structure of an existing table in the Navigation Pane. You can edit the table name using the Paste Table As dialog box. FIGURE 1.17 Paste Table As dialog box

Structure Only option Under Paste Options, select the Structure Only option. Your dialog box should look like Figure 1.17. Click OK. In the Navigation Pane, double-click the Customer Contacts table. Your screen should look like Figure 1.18. Close the table. In the Navigation Pane, right-click the Customer Contacts table. Select Rename. Key: Quick Customer Contacts. Press . Structure from Sales Info table New Customer Contacts table

FIGURE 1.18 Table structure copied to Customer Contacts

Continue to the next exercise.

Exercise 8

Advanced Access

12

Step-By-Step
In your Phil’s Pick-a-Part file, choose Create> Other>Query Wizard (see Figure 1.19). In the New Query dialog box, make sure the Simple Query Wizard option is selected. Click OK. In the Simple Query Wizard dialog box, under Tables/Queries, click the drop-down arrow and select Table: Sales Info. Under Available Fields, click the double right arrow to select all fields in the Sales Info table. Your dialog box should look like Figure 1.20. Click Next. Leave the Detail option selected and click Next. Do not change the title for your query. Click Finish. Continued on the next page.

EXERCISE 9
Create and Modify Queries
A query gathers data from one or more tables based on criteria. Queries allow you to retrieve and display information from tables so that you can edit the results. A query is made up of the fields and records you choose in the order you want. If two or more tables have fields with the same name, you must identify which table you want the query to draw from. The information you need to provide to run a query is: 1) the criteria that you want the data to meet. 2) the fields that you want to include. 3) the tables from which you will retrieve the data. FIGURE 1.19 Simple Query Wizard

Query Wizard button

FIGURE 1.20 Simple Query Wizard with all table fields selected

All fields in table added

To view the results of a query, choose Design> Results>Run or switch to Datasheet View.

Exercise 9

Advanced Access

13

Step-By-Step
Choose Home>Views> Design View .

EXERCISE 9

(Continued)

Create and Modify Queries
FIGURE 1.21 Sales Info Query Design View

Your screen should look like Figure 1.21. Choose Design>Query Setup>Show Table . Asterisk field adds entire table to field list area

In the Show Table dialog box, select the Customer Info table and click Add. Click Add again. Your screen should look like Figure 1.22. Close the Show Table dialog box. Right-click the header of the Customer Info_1 table and select Remove Table. Continued on the next page. Show field check box Field list area

FIGURE 1.22 Sales Info Query duplicate tables added

Another way to add all the fields from a table to query is to open the query in Design View. Then, double-click the asterisk (*) at the top of the list of fields in the query.

Duplicate Customer Info table

If you try to close an unsaved query, Access will prompt you to save it.

Exercise 9

Advanced Access

14

Step-By-Step
In the Sales Info Query, click the Customer Info table. Scroll through the field list and double-click the Account Manager field. Scroll to the top of the Customer Info table and double-click the Customer Name field. Choose Design>Results> Datasheet View .

EXERCISE 9

(Continued)

Create and Modify Queries
FIGURE 1.23 Sales Info Query Datasheet View

Same field from two different tables

Your screen should look like Figure 1.23. Click Design View .

In the field list area, under the Account Manager field of the Customer Info table, uncheck the box in the Show field. Click Datasheet View Your screen should look like Figure 1.24. Click Save . Close the .

FIGURE 1.24 Query with duplicate field drawn from single table

Sales Info Query. Click Office and select

Close Database. Continue to the next exercise.

Exercise 9

Advanced Access

15

Step-By-Step
Click Office Open. In the Open dialog box, navigate to where your Phil’s Pick-a-Part file is. Select the database file and click the Open drop-down arrow. Your dialog box should look similar to Figure 1.25. Select Open Exclusive. Click Office and . Click

EXERCISE 10
Open Databases
When you open a database in Access, by default it can still be opened and edited by others at the same time. This is called shared access. If you need to ensure that you are the only one who can open and make changes to the database, you can select the Open Exclusive option. That means that no one else can open or edit the database because you have exclusive access. Access also offers an Open Exclusive Read Only option so that you and other users can view the database at the same time but cannot edit it. This read-only mode is helpful in a multi-user environment if you want to view a file but want to avoid making any accidental changes. FIGURE 1.25 Open dialog box

select Close Database. Click Office and

select Open. Select the database file again and click the Open drop-down arrow. Database file selected Select Open Exclusive Read-Only. Your screen should look like Figure 1.26. Click Office the database. Continue to the next exercise. and close Open button drop-down menu

FIGURE 1.26 Database opened exclusive read-only

Press + to display the Open dialog box.
Read-Only message

Exercise 10

Advanced Access

16

Step-By-Step
Click Office select Open. Select your Phil’s Pick-aPart database. Click Open. In the Navigation Pane, click the Navigation Bar. Under Filter By Group, select Forms. Double-click the All Orders Chart form. Your screen should look like Figure 1.27. Choose Design>Type> Change Chart Type In the Properties dialog box, on the Type tab in the left column, select Bar. Click the 3D Bar Clustered chart type (see Figure 1.28). Your screen should look like Figure 1.28. Click the largest graphed quantity on the chart (see Figure 1.28). In the Properties dialog box, click the Border/Fill tab. Under Fill, in the Fill Type box, select Gradient. Under the Border area, click the Border Color drop-down arrow. Select White. . and

EXERCISE 11
Format and Modify a Chart
Access offers many tools to present data, but often one of the easiest and most effective ways to present data is through the use of charts. Depending upon the type of data and your audience, the same data can be presented in several different chart formats. Access allows you to easily change the format and type of a chart to improve the presentation of your data. FIGURE 1.27 All Orders Chart

Navigation Bar

Change Chart Type button

FIGURE 1.28 Chart type to 3D Bar Clustered

Properties dialog box

3D Bar Clustered Click this bar Chart background

Continued on the next page.

Exercise 11

Advanced Access

17

Step-By-Step
Click in an open area of the chart space to select the chart background. In the Properties box, under Fill, click the Color drop-down arrow and select MediumSeaGreen. In the Properties box, click the Show/Hide tab. Under Show by default, uncheck the Field buttons/drop zones option. Your screen should look similar to Figure 1.29. In the Properties box, click the General tab. Under Add, click Add Title .

EXERCISE 11

(Continued)

Format and Modify a Chart
FIGURE 1.29 Changes to chart formatting

Click Chart Workspace Title in the chart window. In the Properties box, click the Format tab. In the Caption box, highlight the default text and key: Parts Purchases by Volume. Choose Design>Tools> Property Sheet .

FIGURE 1.30 Title added to chart

Your screen should look similar to Figure 1.30. Close the All Orders Chart. Continue to the next exercise.

Exercise 11

Advanced Access

18

Step-By-Step
In your Phil’s Pick-a-Part database, in the Navigation Pane, show Tables. Choose External Data> Import>Text File .

EXERCISE 12
Import and Export Data
You can gather and present different types of information in Access by importing the data. You can import Excel files, XML Paper Specification (XPS) files, and Access databases. In order to save files in XPS format, you must download and install Microsoft’s XPS add-in. You also can import data from ordinary text files, as long as the information is delimited. A delimited file is a file that uses delimiters, or separators, such as semicolons, colons, or tabs to separate information. An example of a delimited file is a comma-separated values (CSV) file. Access allows you to export data in a database file to many different files, programs, or databases. FIGURE 1.31 Importing a CSV file

Select the Append a copy of the records to the table: option. Click the drop-down arrow. Select the Quick Customer Contacts table. Your screen should look similar to Figure 1.31. Click the Browse button. In the File Open dialog box, select the Customer Contacts file. Click Open. In the Get External Data – Text File dialog box, click OK. Click Next twice. Click Finish. Click Close. Double-click the Quick Customer Contacts table. Your screen should look like Figure 1.32. Continued on the next page.

Text File import button

Append a copy option

FIGURE 1.32 CSV data imported into Quick Customer Contacts

If there is no Save As>XPS option in your Office menu, you must install the XPS add-in.

Data from CSV file entered into empty table

Exercise 12

Advanced Access

19

Step-By-Step
Close the Quick Customer Contacts table. Open the Customer Info table and scroll to the last column on the right. In the Attachment field, in the first record, doubleclick the attachment (see Figure 1.33). Your screen should look like Figure 1.33. In the Attachments dialog box, select the listed attachment and click Save As. Save the attachment in the location specified by your teacher. Click OK. Choose Office >Save

EXERCISE 12

(Continued)

Import and Export Data
FIGURE 1.33 Customer Info table attachment

Attachment field

Double-click attachment

First record attachment

FIGURE 1.34 Saving as XPS file

As>XPS (see Figure 1.34). Continued on the next page. Publish as XPS option

To export an attachment to a record in a database table, use the Save As command.

If you want to keep a file in its original format, you can link to it or attach the file to the database.

Exercise 12

Advanced Access

20

Step-By-Step
In the Publish as XPS dialog box, navigate to the location given by your teacher. Select the Open file after publishing option and click Publish (see Figure 1.35). Your screen should look like Figure 1.36.

EXERCISE 12

(Continued)

Import and Export Data
FIGURE 1.35 Publish as XPS file dialog box

Saving Customer Info as XPS Close the application displaying the XPS document. Close the Customer Info table. Continue to the next exercise. Open file option Publish button

To save or publish a database object as an XPS document or PDF file, choose External Data> Export>PDF or XPS. To save or publish a copy of the database object as a PDF or XPS file, choose Office>Save As>PDF or XPS.

FIGURE 1.36 Published XPS file

Document displayed in Internet Explorer

Exercise 12

Advanced Access

21

Step-By-Step
In your Phil’s Pick-a-Part database, in the Navigation Pane, click the Navigation Bar and select Reports. Open the All Orders report. Choose Home>Views> Layout View .

EXERCISE 13
Set Printing Options
Sometimes you might want to make sure that the data in a report is kept together to avoid excess pages and to improve the overall appearance of the report. Print Preview lets you review each page and helps you make sure that text and fields are correctly placed and formatted. Layout View allows you to manipulate fields and groups, but it does not show page breaks or certain other elements. Access also lets you use the Keep Groups Together property to keep data together so that a portion of a record does not display on one printed page with the remainder on the next. You can use the Force New Page property to print a section of data on a separate page. FIGURE 1.37 All Orders report Layout View

Scroll to the end of the report. Your screen should look like Figure 1.37. Select Format>Views and click the View drop-down arrow. Select Print Preview .

View dropdown arrow Layout View shows fit on one page

Choose the Print Preview>Zoom>Two Pages . Your screen should look like Figure 1.38. Continued on the next page.

FIGURE 1.38 All Orders report Print Preview

You can also right-click an open area of the report window to select Print Preview.

Close Print Preview Broken grouping over two pages Print Preview shows two pages at a time

Exercise 13

Advanced Access

22

Step-By-Step
Click Close Print Preview View . . Choose

EXERCISE 13

(Continued)

Set Printing Options
FIGURE 1.39 All Orders report with Force New Page After Section
Home>Views>Layout

Choose Arrange>Tools> Property Sheet .

In the Property Sheet, under Selection Type, click the drop-down arrow. Select Detail. On the Format tab, click the Force New Page box. Select After Section. Right-click in an open area of the report window and select Print Preview Your screen should look like Figure 1.39. Close Print Preview. In the Property Sheet, under Selection type, click the drop-down arrow. Select Detail. Click in the Force New Page box. Select None. Close the Property Sheet. Choose Format> Grouping & Totals> Group & Sort . Sorting window Your screen should look like Figure 1.40. Continued on the next page. Click to expand options Group & Sort button . Sections broken onto individual pages

FIGURE 1.40 Grouping and Sorting window

Exercise 13

Advanced Access

23

Step-By-Step
In the Group, Sort, and Total window on the Group on Customer Name bar, click More. Click the do not keep group together on one page drop-down arrow and select keep whole group together on one page. Your screen should look like Figure 1.41. Choose Format> Grouping & Totals> Group & Sort the window. Choose Format>Views> View>Print Preview Your screen should look like Figure 1.42. Close the report. In the dialog box, click Yes to save changes. Close your database. Exit Access. . to close

EXERCISE 13

(Continued)

Set Printing Options
FIGURE 1.41 Grouping sections together on same page

Groups are kept on same page

FIGURE 1.42 All Orders report grouped in sections Print Preview

Groups are not broken over pages

Exercise 13

Advanced Access

24