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
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
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). .
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
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,...