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
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
You May Also Find These Documents Helpful
-
4) If the retained earnings account increases from the beginning of the year to the…
- 1324 Words
- 14 Pages
Powerful Essays -
What is a trebuchet? How can a trebuchet use gravity? What does gravity have to do with launching an object? To begin, a trebuchet is a…
- 852 Words
- 4 Pages
Good Essays -
You may want to develop a set sleep schedule when you are in college. Sleep deprivation is common amongst college students who balance work, classes and social lives. Unless you get the right hours of sleep, you will have a tough time concentrating in class.…
- 457 Words
- 3 Pages
Good Essays -
Boone, L. E. & Kurtz, D. L. (2012 Update). Contemporary business (14th ed.). Hoboken, NJ: John Wiley & Sons.…
- 5882 Words
- 24 Pages
Good Essays -
This course provides the foundation for understanding the broad concepts of successful planning, organization, and implementation within the realm of information technology. This course uses real-world examples and identifies common mistakes and pitfalls in project management. Topics covered include project scoping, estimating, budgeting, scheduling, tracking and controlling.…
- 3473 Words
- 14 Pages
Powerful Essays -
As a young child, I tended to become ill often. The doctors blamed my weak immune system on the fact that I contracted mononucleosis and was hospitalized for two months at a young age. The consensus was that over time my immune system would improve and I would outgrow any other issues. I continued to get very ill with bleeding sores constantly lining my mouth and throat throughout my elementary schooling. I noticed my condition was drastically worsening as I ended my sophomore year of highschool. I entered my junior year chronically ill. I missed over thirty percent of the year due to being so ill. I went almost all of junior year teaching myself the information my peers were able to learn in class. I became so physically tired that even getting out of bed was exhausting. I was living with such pain that my parents decided to seek the top…
- 467 Words
- 2 Pages
Good Essays -
When examining your own personal assessment I found some interesting views of myself by working through the Pearson Prentice Hall Self-Assessment Library. Some of the judgments resulting from the tests have surprised me, while other results confirmed what I already suspected. This assessment will examine and give some insight on who I am in the business world, how I work with others in the realm of business, and life within my organization. I will also explain how the Holy Spirit has worked in my life to transform my weakness into strengths to become a better person, employee, and leader.…
- 1301 Words
- 6 Pages
Better Essays -
Copyright 2009 Management Consulted. All Rights Reserved. Notice of Rights No part of this book and its contents may be reproduced, modified, or transmitted in any form or by any means, electronic, mechanical, photocopying, or otherwise, without the prior written permission of the publisher (“Management Consulted”).…
- 27361 Words
- 110 Pages
Powerful Essays -
Edition e-text]. McGraw Hill/Irwin, 2004, New York, NY. Retrieved September 27, 2006 from University of Phoenix, Resource, MKT/463Marketing Website; https://ecampus.phoenix.edu/secure/resource/resource.asp…
- 1320 Words
- 5 Pages
Better Essays -
A project completed in partial fulfilment of the representation of the Bachelor of Science Degree in Business Administration.…
- 1441 Words
- 6 Pages
Better Essays -
Topics Title Page Acknowledgements Table of Contents Executive Summary Introduction Objectives of the Study Concepts and Model Introduced Industry and Organizational Overview Methodology Followed Tabulation and Finding Interpretations and conclusions Recommendations Limitations Future Scope Appendices Bibliography Page No. 01 03 04 05 06 07 08 12 19 20 40 42 43 44 45 52…
- 7540 Words
- 31 Pages
Powerful Essays -
A dissertation submitted in partial fulfilment of the University of Greenwich‟s [BSc. (Hons) (Business Information Technology)]…
- 36623 Words
- 147 Pages
Powerful Essays -
© Springer-Verlag London Limited 2008 First edition Springer-Verlag London Limited 2002, 1-85233-332-4 Apart from any fair dealing for the purposes of research or private study, or criticism or review, as permitted under the Copyright, Designs and Patents Act 1988, this publication may only be reproduced, stored or transmitted, in any form or by any means, with the prior permission in writing of the publishers, or in the case of reprographic reproduction in accordance with the terms of licences issued by the Copyright Licensing Agency. Enquiries concerning reproduction outside those terms should be sent to the publishers. The use of registered names, trademarks, etc. in this publication does not imply, even in the absence of a specific statement, that such names are exempt from the relevant laws and regulations and therefore free for general use. The publisher makes no representation, express or implied, with regard to the accuracy of the information contained in this book and cannot accept any legal responsibility or liability for any errors or omissions that may be made. 9 8 7 6 5 4 3 2 1 Springer Science+Business Media springer.com…
- 65866 Words
- 264 Pages
Powerful Essays -
Edexcel BTEC Level 3 Nationals specification in Business – Issue 2 – June 2010 © Edexcel Limited 2010…
- 4288 Words
- 18 Pages
Powerful Essays -
PUBLISHED BY Microsoft Press A Division of Microsoft Corporation One Microsoft Way Redmond, Washington 98052-6399 Copyright © 2012 Microsoft Corporation All rights reserved. No part of the contents of this book may be reproduced or transmitted in any form or by any means without the written permission of the publisher. Library of Congress Control Number (PCN): 2012950240 ISBN: 978-0-7356-7050-1…
- 18035 Words
- 73 Pages
Powerful Essays