Succeeding in Business™ with Microsoft® Access® 2013: A Problem-Solving Approach

643 Pages·035.362 MB·English

by  Sandra Cable

Succeeding in Business™ with
Microsoft® Access® 2013:
A Problem-Solving Approach
“With knowledge comes opportunity, with
opportunity comes success.”
– Anonymous
Sandra Cable
Texas A&M University – Commerce
Australia • Brazil • Japan • Korea • Mexico • Singapore • Spain • United Kingdom • United States
Copyright 2015 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s).
Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.
This is an electronic version of the print textbook. Due to electronic rights restrictions, some third party content may be suppressed. Editorial
review has deemed that any suppressed content does not materially affect the overall learning experience. The publisher reserves the right to
remove content from this title at any time if subsequent rights restrictions require it. For valuable information on pricing, previous
editions, changes to current editions, and alternate formats, please visit www.cengage.com/highered to search by
ISBN#, author, title, or keyword for materials in your areas of interest.
Copyright 2015 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s).
Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.
Succeeding in Business™ with Microsoft® © 2015 Cengage Learning
Access® 2013: A Problem-Solving Approach
WCN: 02-200-203
Sandra Cable
ALL RIGHTS RESERVED. No part of this work covered by the copyright herein
Senior Product Team Manager: Donna Gridley
may be reproduced, transmitted, stored or used in any form or by any means
Content Developer: Jon Farnham graphic, electronic, or mechanical, including but not limited to photocopying,
recording, scanning, digitizing, taping, Web distribution, information networks,
Associate Content Developer: Angela Lang
or information storage and retrieval systems, except as permitted under Section
Product Assistant: Melissa Stehler 107 or 108 of the 1976 United States Copyright Act, without the prior written
permission of the publisher.
Development Editor: Ann Fisher
Director of Production: Patty Stephan
For product information and technology assistance, contact us at
Senior Content Project Manager: Matthew Cengage Learning Customer & Sales Support, 1-800-354-9706
Hutchinson
For permission to use material from this text or product,
Manufacturing Planner: Fola Orekoya submit all requests online at cengage.com/permissions
Further permissions questions can be emailed to
Market Development Manager: Kristie Clark
[email protected]
Market Development Manager: Gretchen
Swann
Library of Congress Control Number: 2013953094
Composition: GEX Publishing Services ISBN-13: 978-1-285-07758-1
ISBN-10: 1-285-07758-X
Art Director: GEX Publishing Services
Cengage Learning
Text Designer: Tim Blackburn
200 First Stamford Place, 4th Floor
Cover Designer: GEX Publishing Services Stamford, CT 06902
USA
Cover Illustration: GEX Publishing Services
Cengage Learning is a leading provider of customized learning solutions
Copy Editor: GEX Publishing Services
with office locations around the globe, including Singapore, the United
Proofreader: Kathy Orrino Kingdom, Australia, Mexico, Brazil, and Japan. Locate your local office at:
www.cengage.com/global
Indexer: Sharon Hilgenberg
Cengage Learning products are represented in Canada by Nelson
Education, Ltd.
For your course and learning solutions, visit www.cengage.com
Purchase any of our products at your local college store or at our
preferred online store www.cengagebrain.com
Printed in the United States of America
1 2 3 4 5 6 7 17 16 15 14 13
Copyright 2015 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s).
Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.
Brief
Contents
Preface xii
Introduction Introduction to Data Management with Microsoft Access 2013 2
Chapter 1 Preparing to Automate Data Management 30
Chapter 2 Building the Database 108
Chapter 3 Analyzing Data for Effective Decision Making 196
Chapter 4 Collecting Data with Well-Designed Forms 312
Chapter 5 Developing Effective Reports 378
Chapter 6 Automating Database Processing 464
Chapter 7 Enhancing User Interaction Through Programming 538
Glossary 596
Index 605
iii
Copyright 2015 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s).
Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.
Table of Contents
Preface xii
The Succeeding in Business Series Instructor Resources xiii
Succeeding in Business Series Walk-Through xv
About the Author xix
Author Acknowledgements xix
Introduction to Data Management with Microsoft Access 2013 2
About This Book and Microsoft Office Access 2013 3
Introduction to Access 2013 3
Starting Access 2013 in Windows 8 4
Starting Access 2013 in Windows 7 or Vista 4
Using the Backstage View and Quick Access Toolbar 6
Opening and Closing Files in Access 2013 7
Saving Files in Access 2013 8
Using Templates to Create Databases 10
Creating Databases for the Web 11
Understanding Intellisense 11
The Ribbon 11
Using the Keyboard to Initiate Commands 13
Adding Commands to the Quick Access Toolbar 14
Changing Views 16
Getting Help 16
The Fictional Company Used in this Book 18
Key Players 19
Company Goal: Expand Operations into Other Areas 20
How Will Access Be Used at 4Corners Pharmacy? 20
Managing Data for the Organization 23
Problem Solving in This Book 25
Steps To Success Exercises 26
End-Of-Chapter Continuing Case Problems 27
LEVEL 1 CASE PROBLEM: NHD Development Group Inc. 27
LEVEL 2 CASE PROBLEM: MovinOn Inc. 27
LEVEL 3 CASE PROBLEM: Hershey College Intramural Department 28
Chapter Summary 28
Conceptual Review 28
Chapter 1: Preparing to Automate Data Management 30
Chapter Introduction 31
Case Scenario 31
Level 1 Examining Existing and Missing Sources of Data 32
Discovering and Evaluating Sources of Existing Data 32
Identifying Sources of Missing Data 45
Assimilating the Available Information and Planning the Database 46
Evaluating Field Values and Assigning Appropriate Data Types 47
iv
Copyright 2015 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s).
Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.
Succeeding in Business with Microsoft Access 2013 Contents
Short Text and Long Text Data Types 48
Number Data Type 49
Currency Data Type 49
Date/Time Data Type 49
AutoNumber Data Type 49
Yes/No Data Type 50
OLE Object Data Type 50
Hyperlink Data Type 50
Attachment Data Type 50
Calculated Data Type 50
Lookup Data Type 51
Selecting the Correct Data Type 51
Assigning the Correct Field Size for Text Fields 52
Assigning the Correct Field Size for Number Fields 52
Dividing the Existing and Missing Data into Tables 53
Naming Conventions 58
Changing a Field Name 59
Steps To Success: Level 1 59
Level 2 Understanding and Creating Table Relationships 60
Understanding Relational Database Objects 60
Tables 60
Queries 60
Forms 61
Reports 63
Other Database Objects 64
Using a Database Instead of Excel Spreadsheets 65
Creating Table Relationships 71
One-to-Many Relationships 72
One-to-One Relationships 72
Many-to-Many Relationships 73
Understanding Referential Integrity 75
Referential Integrity Errors 75
Overriding Referential Integrity 77
Steps To Success: Level 2 78
Level 3 Identifying and Eliminating Database Anomalies by Normalizing Data 79
Normalizing the Tables in the Database 79
First Normal Form 83
Second Normal Form 87
Third Normal Form 88
Steps To Success: Level 3 90
Chapter Summary 91
Conceptual Review 92
Case Problems 93
Level 1 – Creating the Database Design for NHD Development Group Inc. 93
Level 2 – Creating the Database Design for MovinOn Inc. 98
Level 3 – Creating the Database Design for Hershey College 104
SAM: Skills Assessment Manager 107
Chapter 2: Building the Database 108
Chapter Introduction 109
Case Scenario 109
v
Copyright 2015 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s).
Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.
Contents Succeeding in Business with Microsoft Access 2013
Level 1 Creating the Database Tables 109
Reviewing the Database Design 109
Creating the Database Using the Database Design 115
Creating a Table by Entering Data in Datasheet View 116
Working in Design View 118
Adding Descriptions to Fields in Table Design View 120
Creating a Table in Design View 122
Evaluating and Testing the Table Design 122
Formatting Field Values Using an Input Mask 127
Steps To Success: Level 1 138
Level 2 Populating and Relating the Database Tables 142
Creating Additional Tables for 4Corners Pharmacy 142
Populating the Database Tables 145
Importing and Copying Records from One Table to Another 145
Importing Data from an Excel Workbook 148
Working with Primary and Foreign Keys 156
Creating an Index 157
Creating One-to-Many Relationships Between Tables 159
Creating a Many-To-Many Relationship Between Tables 165
Using a Subdatasheet to View Related Records 168
Steps To Success: Level 2 171
Level 3 Maintaining and Securing a Database 173
The Database Administrator Role 173
Compacting and Repairing a Database 175
Backing Up a Database 178
Documenting the Database Design 179
Securing a Database from Unauthorized Use 181
Using Database Encryption 181
Hiding Database Objects from Users 183
Steps To Success: Level 3 184
Chapter Summary 185
Conceptual Review 186
Case Problems 187
Level 1 – Creating the Database for NHD Development Group Inc. 187
Level 2 – Creating the Database for MovinOn Inc. 189
Level 3 – Creating the Database for Hershey College 192
SAM: Skills Assessment Manager 195
Chapter 3: Analyzing Data for Effective Decision Making 196
Chapter Introduction 197
Case Scenario 197
Human Resources 197
Level 1 Organizing and Retrieving Information From a Database 197
Filtering and Sorting Data 197
Filtering by Selection 199
Filtering by Form 200
Sorting Data to Increase Information Content 201
Filtering Using the Filter Arrow 202
vi
Copyright 2015 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s).
Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.
Succeeding in Business with Microsoft Access 2013 Contents
Using Queries to Answer Business Questions 203
Using the Simple Query Wizard to Create a Query 204
Including Summary Statistics in a Query for Data Analysis 208
Creating a Query in Design View 211
Creating Queries with Multiple Criteria 214
Specifying Sort Order in Queries 215
Running a Query 216
Enhancing Query Criteria to Improve Data Analysis 220
Using Wildcards for Inexact Query Matches 220
Using Comparison Operators to Refine Query Criteria 223
Verifying and Printing Query Results 228
Modifying Query Datasheets 229
Steps To Success: Level 1 230
Level 2 Creating More Complex Queries 231
Evaluating Data Using Special Types of Queries 231
Using Queries to Find Duplicate Records 232
Using Queries to Find Unmatched Records 234
Limiting the Records in the Query Results 239
Using Parameter Values in Queries 241
Analyzing Data from More Than One Table 244
Refining Relationships with Appropriate Join Types 251
Using Logical Operators to Specify Multiple Conditions 258
Performing Calculations with Queries 265
Calculating Statistical Information 265
Creating Calculated Fields in a Query 268
Concatenating in Queries 273
Steps To Success: Level 2 274
Level 3 Exploring Advanced Queries and Queries Written in Structured
Query Language 275
Analyzing Query Calculations 275
Creating a Crosstab Query 277
Modifying Data Using Queries 282
Archiving Data with Make-Table Queries 283
Adding Records to Tables with Append Queries 285
Removing Records from Tables with Delete Queries 287
Updating Data with an Update Query 289
Making Decisions in Queries 291
Customizing Queries Using Structured Query Language 294
Exploring the Components of an SQL Query 296
Creating a Union Query Using SQL 299
Steps To Success: Level 3 302
Chapter Summary 303
Conceptual Review 304
Case Problems 304
Case 1 – Managing Customer Information for NHD Development Group Inc. 304
Case 2 – Retrieving Employee Information for MovinOn Inc. 307
Case 3 – Managing Equipment and Preparing for Games in the Hershey College
Intramural Department 309
vii
Copyright 2015 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s).
Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.
Contents Succeeding in Business with Microsoft Access 2013
Chapter 4: Collecting Data with Well-Designed Forms 312
Chapter Introduction 313
Case Scenario 313
Level 1 Developing Simple Forms To Facilitate Data Entry 314
Designing Forms for Efficient Data Entry 314
Verifying Database Integrity 315
Examining and Entering Data 318
Creating Simple Forms for Data Entry and Editing 319
Creating a Form Using the Form Tool 320
Using the Form Wizard 322
Examining a Form in Design View 327
Developing a Consistent User Interface 329
Changing Label Properties in a Form 332
Adding an Unbound Graphic to a Form 334
Steps To Success: Level 1 335
Level 2 Creating Forms That Use Fields From More Than One Table 337
Adding a Subform to an Existing Form 337
Modifying the Form Layout 340
Creating a Form from a Query 344
Adding Command Buttons to a Form 346
Exploring Other Form Properties 349
Controlling Form Printing 351
Printing a Selected Record in Form View 354
Steps To Success: Level 2 354
Level 3 Creating Forms for Completing Daily Business Tasks 355
Improving the Usability of Forms 355
Locating a Record Quickly 356
Adding a Calculation to a Form 361
Streamlining the Main Pharmacy Processes in a Complex Form 363
Step 1: Registering New Customers or Confirming Customer Identity 364
Step 2: Filling and Refilling Prescriptions 364
Creating Forms with Many Subforms 365
Creating Multipage Forms Using Page Break 368
Creating Forms Using Tab Controls for Multiple Subforms 369
Changing Tab Order and Tab Stop 371
Steps To Success: Level 3 371
Chapter Summary 373
Conceptual Review 373
Case Problems 374
Case 1 – Providing Efficient Data-Entry Forms for NHD Development Group Inc. 374
Case 2 – Providing Data-Entry Forms for MovinOn Inc. 375
Case 3 – Creating Data-Entry Forms for the Hershey College Intramural Department 376
Chapter 5: Developing Effective Reports 378
Chapter Introduction 379
Case Scenario 379
Level 1 Creating Simple Reports and Labels 380
Understanding Reports 380
Choosing the Appropriate Type of Report 381
Planning Basic Reports 384
viii
Copyright 2015 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s).
Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.