Microsoft Office 365 Access™ 2019 Comprehensive

Microsoft? Office 365? AccessTM 2019 Comprehensive

Series Editor AMY KINSER

MORIARITY | KINSER | NIGHTINGALE

A01_KINS1969_01_SE_FM.indd 1

11/01/19 9:47 PM

Vice President of Courseware Portfolio Management: Andrew Gilfillan Executive Portfolio Manager: Samantha Lewis Team Lead, Content Production: Laura Burgess Content Producer: Shannon Stanton Development Editor: Nancy Lamm Portfolio Management Assistant: Bridget Daly Director of Product Marketing: Brad Parkins Director of Field Marketing: Jonathan Cottrell Product Marketing Manager: Heather Taylor Field Marketing Manager: Bob Nisbet Product Marketing Assistant: Liz Bennett Field Marketing Assistant: Derrica Moser

Senior Operations Specialist: Maura Garcia Senior Art Director: Mary Seiner Interior and Cover Design: Pearson CSC Cover Photo: Courtesy of Shutterstock? Images Senior Product Model Manager: Eric Hakanson Manager, Digital Studio: Heather Darby Digital Content Producer, MyLab IT: Becca Golden Course Producer, MyLab IT: Amanda Losonsky Digital Studio Producer: Tanika Henderson Full-Service Project Management: Pearson CSC (Amy Kopperude) Composition: Pearson CSC

Credits and acknowledgments borrowed from other sources and reproduced, with permission, in this textbook appear on appropriate page within text.

Microsoft and/or its respective suppliers make no representations about the suitability of the information contained in the documents and related graphics published as part of the services for any purpose. All such documents and related graphics are provided "as is" without warranty of any kind. Microsoft and/or its respective suppliers hereby disclaim all warranties and conditions with regard to this information, including all warranties and conditions of merchantability, whether express, implied or statutory, fitness for a particular purpose, title and non-infringement. In no event shall Microsoft and/or its respective suppliers be liable for any special, indirect or consequential damages or any damages whatsoever resulting from loss of use, data or profits, whether in an action of contract, negligence or other tortious action, arising out of or in connection with the use or performance of information available from the services.

The documents and related graphics contained herein could include technical inaccuracies or typographical errors. Changes are periodically added to the information herein. Microsoft and/or its respective suppliers may make improvements and/or changes in the product(s) and/or the program(s) described herein at any time.

Microsoft? and Windows? are registered trademarks of the Microsoft Corporation in the U.S.A. and other countries. This book is not sponsored or endorsed by or affiliated with the Microsoft Corporation.

Copyright ? 2020 by Pearson Education, Inc., New York, NY 10013. All rights reserved. Printed in the United States of America. This publication is protected by Copyright and permission should be obtained from the publisher prior to any prohibited reproduction, storage in a retrieval system, or transmission in any form or by any means, electronic, mechanical, photocopying, recording, or likewise. For information regarding permission(s), write to: Rights and Permissions Department.

Pearson Prentice HallTM is a trademark of Pearson Education, Inc. Pearson? is a registered trademark of Pearson plc Prentice Hall? is a registered trademark of Pearson Education, Inc.

Pearson Education Ltd., London Pearson Education Singapore, Pte. Ltd Pearson Education, Canada, Inc. Pearson Education?Japan Pearson Education Australia PTY, Limited

Pearson Education North Asia Ltd., Hong Kong Pearson Educaci?n de Mexico, S.A. de C.V. Pearson Education Malaysia, Pte. Ltd.

Library of Congress Cataloging-in-Publication Data available upon request

119

A01_KINS1969_01_SE_FM.indd 2

ISBN-10: 0-13-539196-2 ISBN-13: 978-0-13-539196-9

11/01/19 9:47 PM

Dedications

I dedicate this series to my Kinser Boyz for their unwavering love, support, and patience; to my parents and sister for their love; to my students for inspiring me; to

Sam for believing in me; and to the instructors I hope this series will inspire! Amy S. Kinser

I dedicate this book to my beautiful and amazing wife, April. Without her support and understanding, this would not have been possible. Also, to my wonderful son,

Patton, whose strength to overcome so many obstacles in his life inspires me to continue to do my best work. Brant Moriarity

For my wife, Amy, and our two boys, Matt and Aidan. I cannot thank them enough for their support, love, and endless inspiration. J. Eric Kinser

To my parents, who always believed in and encouraged me and have given me unconditional support, patience, and love. To my brother and my hero--may you

be watching from Heaven with joy in your heart. Jennifer Paige Nightingale

A01_KINS1969_01_SE_FM.indd 3

Dedications

iii

11/01/19 9:47 PM

About the Authors

Amy S. Kinser, Esq., Series Editor

Amy holds a B.A. degree in Chemistry with a Business minor from Indiana University, and a J.D. from the Maurer School of Law, also at Indiana University. After working as an environmental chemist, starting her own technology consulting company, and practicing intellectual property law, she has spent the past 18 years teaching technology at the Kelley School of Business in Bloomington, Indiana. Currently, she serves as the Director of Computer Skills and Senior Lecturer at the Kelley School of Business at Indiana University. She also loves spending time with her two sons, Aidan and J. Matthew, and her husband J. Eric.

Brant Moriarity

Brant P. Moriarity earned a B.A. in Religious Studies/Philosophy and a M.S. in Information Systems at Indiana University. He is a Senior Lecturer at the Indiana University's Kelley School of Business, where he teaches topics such as data management and analysis, as well as the strategic use of Information Systems in business. He is also the founder of Beats Per Minute Technologies, LLC, bringing the benefits of business analytics and custom database application development to small businesses and non-profit organizations.

J. Eric Kinser

Eric Kinser received his B.S. degree in Biology from Indiana University and his M.S. in Counseling and Education from the Indiana School of Education. He has worked in the medical field and in higher education as a technology and decision support specialist. He is currently a senior lecturer in the Operations and Decision Technology Department at the Kelley School of Business at Indiana University. When not teaching he enjoys experimenting with new technologies, traveling, and hiking with his family.

Jennifer Paige Nightingale

Jennifer Nightingale, associate professor at Slippery Rock University of Pennsylvania, has taught Information Systems Management since 2000. Before joining Slippery Rock University, she spent 15 years in industry with a focus in management and training. Her research expertise is in instructional technology, using technology as a teaching tool, and the impact of instructional technologies on student learning. She has earned numerous teaching and research honors and awards, holds an Ed.D. (instructional technology) and two M.S. degrees (information systems management and education) from Duquesne University, and a B.A. from the University of Pittsburgh.

iv

About the Authors

A01_KINS1969_01_SE_FM.indd 4

11/01/19 9:47 PM

Brief Contents

COMMON FEATURES CHAPTER 1: Understanding the Common Features of Microsoft Office 1

ACCESS ACCESS BUSINESS UNIT 1: Understanding and Using a Relational Database 49

CHAPTER 1: The Four Main Database Objects 50 CHAPTER 2: Tables, Keys, and Relationships 93 ACCESS BUSINESS UNIT 1 CAPSTONE146

ACCESS BUSINESS UNIT 2: Accessing, Maintaining, and Presenting Information 161 CHAPTER 3: Queries and Data Access 162 CHAPTER 4: Using Forms and Reports in Access 214

ACCESS BUSINESS UNIT 2 CAPSTONE262

ACCESS BUSINESS UNIT 3: Ensuring Consistent Data and Advanced Querying 277 CHAPTER 5: Advanced Tables 278 CHAPTER 6: Pattern Matching and Functions in Queries 335

ACCESS BUSINESS UNIT 3 CAPSTONE375

ACCESS BUSINESS UNIT 4: Leveraging Queries for Business Information and Intelligence 387 CHAPTER 7: Aggregated Calculations, Subquerying, and SQL 388 CHAPTER 8: Action Queries and Advanced Relationships 451

ACCESS BUSINESS UNIT 4 CAPSTONE 486

ACCESS BUSINESS UNIT 5: Producing Professional Forms and Reports 497 CHAPTER 9: Advanced Form Settings and Form Types 498 CHAPTER 10: Advanced Reports and Mailing Labels 564

ACCESS BUSINESS UNIT 5 CAPSTONE607

ACCESS BUSINESS UNIT 6: Developing a Polished User Interface and Experience 621 CHAPTER 11: Develop Navigation Forms and the User Interface 622 CHAPTER 12: Create a Refined User Experience with Macros 653

ACCESS BUSINESS UNIT 6 CAPSTONE 694

ACCESS BUSINESS UNIT 7: Implementing and Automating a Database with VBA 705 CHAPTER 13: Use VBA in Access 706 CHAPTER 14: Implement Your Database 747

ACCESS BUSINESS UNIT 7 CAPSTONE776

APPENDIX A: Normalize a Database for Effective Design 787 APPENDIX B: Access Microsoft Certification Reference 811 GLOSSARY815 INDEX824

Brief Contents

v

A01_KINS1969_01_SE_FM.indd 5

11/01/19 9:47 PM

Contents

Welcome to the Team Letter xxvii

COMMON FEATURES 1

CHAPTER 1: Understanding the Common Features of Microsoft Office 1

PREPARE CASE: Painted Paradise Resort & Spa Employee Training Preparation 1

Working with the Office Interface 2 Understand the Office Suite and Applications 2

Start, Save, and Manipulate Office Applications and Use the Office Ribbon 3

Opening Microsoft Word and the Start Screen 3 Using the Ribbon and Ribbon Display Options 5 Using Office Backstage, Your Account, and Document Properties 8 Saving a New Document to the Local OneDrive That Syncs to the Cloud 10 Closing a File, Reopening from the Recent Documents List, and Exiting an Application 15 Opening an Existing File in Microsoft Excel and Then Saving as Another Name 16 Zooming, Scrolling, and Navigating with Keyboard Shortcuts18 Using the Quick Access Toolbar to Save a Currently Open File 20

Manipulate, Correct, and Format Content in Excel 20

Checking Spelling 20 Entering, Copying, and Pasting Text 22 Using Undo to Correct a Mistake 23 Finding and Replacing Text 24 Using the Font Group and the Font Dialog Box 25 Using the Style Gallery with Live Preview 26 Inserting a Note and Total Using the Tell me what you want to do Box 27

Formatting, Finding Help, and Printing in Office 29 Format Using Various Office Methods 29

Using Excel to Enter Content, Apply Bold, and Apply a Fill Color 29 Opening an Excel Dialog Box 30 Inserting Images and Using Contextual Tools to Resize 31 Formatting Using the Mini Toolbar 33 Opening Shortcut Menus and Format Painter 35

Find Help, Print, and Share in Office 36

Using the Help Pane and ScreenTips 36 Accessing the Share Pane 38 Changing Views 38 Printing a File 40 Exporting a PDF 41

Insert Office Add-ins 42

Concept Check 43 Key Terms 43

Visual Summary 44

Practice 1 45

Problem Solve 1 46

Perform47 Perform in Your Career 47 Perform in Your Life 47

ACCESS BUSINESS UNIT 1 49

CHAPTER 1: The Four Main Database Objects50

PREPARE CASE: Red Bluff Golf Course Putts for Paws Charity Tournament 50

Understanding the Basics of Databases and Tables 51

Understand the Purpose of Access 51

Understanding the Four Main Objects in a Database 52 Creating a New Database and Templates 53 Opening the Starting File 54

Maneuver in the Navigation Pane 55

Opening and Closing the Shutter Bar 55 Customizing the Navigation Pane 55 Using the Search Box 57 Understanding File Extensions in Access 57

Understand the Purpose of Tables 58

Importing a Table 59 Navigating through a Table 61 Navigating through a Table with the Navigation Bar 62 Understanding Differences between Access and Excel 64

Manually Navigate a Database 66

Using a Manual Query to Explore a Database 66

Understanding Queries, Forms, and Reports 68 Understand the Purpose of Queries 68

Using the Query Wizard 69 Switching to the Design View of a Query 71 Specifying Selection Criteria 72 Sorting Query Results 74 Printing Query Results 75

Understand the Purpose of Forms 76

Creating a Form 77 Entering Data Using a Form 78

Understand the Purpose of Reports 79

Creating a Report Using a Wizard 80 Printing a Report 82

vi

Contents

A01_KINS1969_01_SE_FM.indd 6

11/01/19 11:16 PM

Back Up a Database 83

Backing Up a Database 83

Compact and Repair a Database 84

Compacting Your Database 84

Concept Check 86

Key Terms 86

Visual Summary 87

Practice 1 89

Problem Solve 1 90

Perform91 Perform in Your Career 91

CHAPTER 2: Tables, Keys, and Relationships 93

PREPARE CASE: Red Bluff Golf Course Putts for Paws Charity Tournament Database 93

Inserting Data Into a Database 94 Understand Database Design 94

Opening the Starting File 95 Viewing the Design View of a Table 96

Import Data from Other Sources 97

Copying and Pasting Data from Excel 97 Importing a Worksheet 98 Importing from a Named Range 100 Importing from a Text File 102

Enter Data Manually 103

Entering Data Using Datasheet View 103 Deleting Data from a Table 105 Deleting a Field from a Table 106

Understanding Tables and Keys 107 Create a Table in Design View 107

Defining Data Types 107 Determining Field Size 109 Creating a Table in Design View 109 Changing a Data Type 111

Understand Masks and Formatting 111

Defining Input Masks 111 Formatting a Field 113

Understand and Designate Keys 115

Understanding Primary Keys 115 Understanding Foreign Keys 115 Identifying a Composite Key 116 Defining a Primary Key 117

Understanding Relational Databases 119 Understand Basic Principles of Normalization 120

Representing Entities and Attributes 120 Minimizing Redundancy 121

Understand Relationships between Tables 121

Viewing the Relationships Window 122 Determining Relationship Types 123

Create a One-to-Many Relationship 124

Forming a Relationship 124

Create a Many-to-Many Relationship 127

Forming a New Many-to-Many Relationship 128 Creating a Junction Table 129

Forming Two Relationships to a Junction Table 130 Populating the Junction Table 132 Defining One-to-One Relationships 132

Understand Referential Integrity 132

Selecting Cascade Update 132 Selecting Cascade Delete 133 Testing Referential Integrity 133 Creating a Report Using Two Related Tables 135

Concept Check 137

Key Terms 137

Visual Summary 138

Practice 1 140

Problem Solve 1 143

Perform144 Perform in Your Career 144

ACCESS BUSINESS UNIT 1 CAPSTONE 146

More Practice 1 146

Problem Solve 1 148

Problem Solve 2 151

Perform154 Perform in Your Life 154 Perform in Your Career 155 Perform in Your Team 157 How Others Perform 158

ACCESS BUSINESS UNIT 2 161

CHAPTER 3: Queries and Data Access 162

PREPARE CASE: Turquoise Oasis Spa Data Management162

Working with Tables 163 Find and Replace Records in a Table 163

Opening the Starting File 163 Finding Records in a Table 163 Finding and Replacing Records in a Table 165 Using a Wildcard Character 165 Applying a Filter to a Table 167 Using a Text Filter 168

Modify Table Appearance 169

Changing the Look of a Table 169

Querying the Database 170 Run Query Wizards 170

Creating a Find Duplicates Query 170 Creating a Find Unmatched Query 172

Create Queries in Design View 173

Creating a Single-Table Query 175 Viewing Table Relationships 176 Creating a Query Using Multiple Tables 178 Removing a Table from a Query to Fix the Multiplier Effect 179

Sort Table and Query Results 181

Sorting a Table by a Single Field 181 Sorting by More Than One Field 181

Contents vii

A01_KINS1969_01_SE_FM.indd 7

12/01/19 1:32 AM

Define Selection Criteria for Queries 183

Using a Comparison Operator 183 Hiding Fields That Are Used in a Query 184 Sorting on a Hidden Field 184 Using Is Null Criteria 185 Using the Criteria Row with Multiple Criteria 187 Using the Or Criteria Row 188 Using Both the Criteria Row and the Or Criteria Rows 189 Using AND and OR Logical Operators 190 Combining Operators and Multiple Criteria 192 Using Special Operators and Date Criteria 194 Combining Special Operators and Logical Operators 196

Create Aggregate Functions 197

Adding a Total Row 197 Using Aggregate Functions 198 Changing Field Names 200 Creating Calculations for Groups of Records 201 Troubleshooting an Aggregate Query 202 Formatting an Aggregated Field 203

Create Calculated Fields 204

Building a Calculated Field Using Expression Builder 204

Concept Check 206

Key Terms 206

Visual Summary 207

Practice 1 208

Problem Solve 1 211

Perform212 Perform in Your Career 212

CHAPTER 4: Using Forms and Reports in Access 214

PREPARE CASE: Turquoise Oasis Spa's New Database214

Creating Customized Forms 215 Navigate and Edit Records in Datasheets 215

Opening the Starting File 215 Editing a Record in Datasheet View of a Table 216

Navigate Forms and Subforms 217

Navigating a Main Form 217 Navigating a Form with a Subform 219 Navigating a Split Form 220 Using the Find Command in a Form 221

Update Table Records Using Forms 222

Adding a New Record Using a Form 223 Editing Records Using a Form 223 Deleting a Record Using a Form 224

Create a Form Using the Form Wizard 225

Creating a Single-Table Form 225 Creating a Subform (Multiple-Table Form) 227 Creating a Split Form 230

Modify a Form's Design 230

Changing the Form Theme 231 Resizing and Changing Controls on a Form 232 Adding a Picture to the Form 234 Previewing and Printing a Record from a Form 235

Creating Customized Reports 236 Create a Report Using the Report Wizard 236

Creating a Single-Table Report Using the Report Wizard237 Creating a Multiple-Table Report Using the Report Wizard 238 Exploring Report Views 240 Creating Report Totals Using the Report Wizard 242

Customize a Report 245

Moving, Resizing, and Formatting Report Controls 245 Enhancing a Report with Conditional Formatting 247 Applying Grouping and Sorting 249 Adding Subtotals 250

Save a Report as a PDF File 252

Saving a Report as a PDF File 252

Concept Check 253

Key Terms 253

Visual Summary 254

Practice 1 256

Problem Solve 1 258

Perform260 Perform in Your Career 260

ACCESS BUSINESS UNIT 2 CAPSTONE 262

More Practice 1 262

Problem Solve 1 266

Problem Solve 2 268

Perform270 Perform in Your Life 270 Perform in Your Career 272 Perform in Your Team 273 How Others Perform 275

ACCESS BUSINESS UNIT 3 277

CHAPTER 5: Advanced Tables 278

PREPARE CASE: The Red Bluff Golf Course & Pro Shop: Modifying Database Tables 278

Controlling the Way Data Is Entered: Advanced Field Options 279

Understand the Purpose and Benefits of Input Masks279

Opening the Starting File 279 Using the Input Mask Wizard 280 Creating a Custom Input Mask 282

Apply Custom Formatting 287

Creating Custom Formatting 288

Define Data Validation Rules 293

Creating Validation Rules and Validation Text 294

Define Caption Names 296

Creating Captions for Existing Fields 297

Create Indexes to Improve Performance 298

Defining a Single-Field Index 299 Defining Multiple-Field Indexes 300

viii Contents

A01_KINS1969_01_SE_FM.indd 8

11/01/19 9:47 PM

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download