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 Ofï¬�ce, 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 afï¬�liated with Microsoft in any manner. This publication may be used in assisting students to prepare for a Microsoft Business Certiï¬�cation exam. While this publication may be used in assisting individuals to prepare for a Microsoft Business Certiï¬�cation exam, Microsoft, its designated program administrator and Glencoe/McGraw-Hill do not warrant that use of this publication will ensure passing a Microsoft Business Certiï¬�cation 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: Deï¬�ne Data Needs Exercise 2: Deï¬�ne Field Data Types Exercise 3: Modify Field Properties Exercise 4: Set Validation Rules Exercise 5: Deï¬�ne and Modify Primary Keys Exercise 6: Deï¬�ne and Modify Multi-Field Primary Keys Exercise 7: Deï¬�ne 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 Deï¬�ne 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 ï¬�elds 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 modiï¬�cations. 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 ï¬�eld by the Inventory ï¬�eld. 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) Deï¬�ne 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 Deï¬�ne 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 ï¬�eld 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) Deï¬�ne 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 ï¬�elds within a table. For example, by specifying the Field Size, or maximum number of characters that a user can enter in a ï¬�eld, you can ensure that data in the Phone Number ï¬�eld contains ten digits. You also can set a memo ï¬�eld as Append Only. With this option, users can add data to a ï¬�eld, but they cannot delete data from it. Common properties for ï¬�elds in a table are shown in Table 1. 3. TABLE 1. 3 Common Field Properties Allow Zero Length Speciï¬�es that a Text, Memo, or Hyperlink ï¬�eld can accept strings of zero length, or null values, with no characters. Speciï¬�es that data can be added to a Memo ï¬�eld, but that the existing data in the ï¬�eld cannot be overwritten. Specifies the name of a Text field. Speciï¬�es the maximum number of characters a user can enter in a ï¬�eld. Speciï¬�es that data must be entered in a ï¬�eld. 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 ï¬�eld by creating a Boolean (or conditional) expression in the Validation ï¬�eld. 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 ï¬�eld violates a validation rule. Validation messages should contain information about the invalid data and how to ï¬�x 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 Deï¬�ne and Modify Primary Keys A primary key is a ï¬�eld 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 identiï¬�er for each customer in the database. You can identify each customer uniquely because no two customers have the same Customer ID number. A ï¬�eld with the AutoNumber data type is often used as the primary key because the numbers assigned to the ï¬�eld 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 identiï¬�es 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 Deï¬�ne and Modify Multi-Field Primary Keys A multi-ï¬�eld primary key is a table with two or more ï¬�elds deï¬�ned as the primary key. A multi-ï¬�eld key is used if a table has no single ï¬�eld that is appropriate to serve as the primary key. Although a primary key should include as few ï¬�elds as possible, if a table has no single ï¬�eld with a set of unique values, two or more ï¬�elds can be combined to create a unique value. In this exercise, the Product ID ï¬�eld in the Product Info table is not a suitable primary key. Multiple primary keys are assigned to the Product Name and Cost ï¬�elds to ï¬�x 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 ï¬�elds. 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 Deï¬�ne 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 ï¬�rst 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 ï¬�rst and second forms as well as the third form. TABLE 1. 6 Rules of normalization Rule 1NF 2NF Description of Rule Each ï¬�eld 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 ï¬�eld 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) Deï¬�ne 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 ï¬�elds and records you choose in the order you want. If two or more tables have ï¬�elds 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 ï¬�elds 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 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 ï¬�le 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 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 ï¬�les, XML Paper Speciï¬�cation (XPS) ï¬�les, and Access databases. In order to save ï¬�les in XPS format, you must download and install Microsoft’s XPS add-in. You also can import data from ordinary text ï¬�les, as long as the information is delimited. A delimited ï¬�le is a ï¬�le that uses delimiters, or separators, such as semicolons, colons, or tabs to separate information. An example of a delimited ï¬�le is a comma-separated values (CSV) ï¬�le. Access allows you to export data in a database ï¬�le to many different ï¬�les, 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 Ofï¬�ce 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 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 ï¬�elds are correctly placed and formatted. Layout View allows you to manipulate ï¬�elds 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