Transcription
Microsoft Office 365 Access 2019 ComprehensiveSeries Editor AMY KINSERMORIARITY KINSER NIGHTINGALEA01 KINS1969 01 SE FM.indd 111/01/19 9:47 PM
Vice President of Courseware Portfolio Management: Andrew GilfillanExecutive Portfolio Manager: Samantha LewisTeam Lead, Content Production: Laura BurgessContent Producer: Shannon StantonDevelopment Editor: Nancy LammPortfolio Management Assistant: Bridget DalyDirector of Product Marketing: Brad ParkinsDirector of Field Marketing: Jonathan CottrellProduct Marketing Manager: Heather TaylorField Marketing Manager: Bob NisbetProduct Marketing Assistant: Liz BennettField Marketing Assistant: Derrica MoserSenior Operations Specialist: Maura GarciaSenior Art Director: Mary SeinerInterior and Cover Design: Pearson CSCCover Photo: Courtesy of Shutterstock ImagesSenior Product Model Manager: Eric HakansonManager, Digital Studio: Heather DarbyDigital Content Producer, MyLab IT: Becca GoldenCourse Producer, MyLab IT: Amanda LosonskyDigital Studio Producer: Tanika HendersonFull-Service Project Management: Pearson CSC (Amy Kopperude)Composition: Pearson CSCCredits 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 publishedas 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 respectivesuppliers 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 othertortious 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 oraffiliated 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 byCopyright and permission should be obtained from the publisher prior to any prohibited reproduction, storage in a retrieval system, or transmission in any form orby any means, electronic, mechanical, photocopying, recording, or likewise. For information regarding permission(s), write to: Rights and Permissions Department.Pearson Prentice Hall is a trademark of Pearson Education, Inc.Pearson is a registered trademark of Pearson plcPrentice Hall is a registered trademark of Pearson Education, Inc.Pearson Education Ltd., LondonPearson Education Singapore, Pte. LtdPearson Education, Canada, Inc.Pearson Education–JapanPearson Education Australia PTY, LimitedPearson Education North Asia Ltd., Hong KongPearson Educación de Mexico, S.A. de C.V.Pearson Education Malaysia, Pte. Ltd.Library of Congress Cataloging-in-Publication Data available upon request119ISBN-10: 0-13-539196-2ISBN-13: 978-0-13-539196-9A01 KINS1969 01 SE FM.indd 211/01/19 9:47 PM
DedicationsI dedicate this series to my Kinser Boyz for their unwavering love, support, andpatience; to my parents and sister for their love; to my students for inspiring me; toSam for believing in me; and to the instructors I hope this series will inspire!Amy S. KinserI dedicate this book to my beautiful and amazing wife, April. Without her supportand 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 tocontinue to do my best work.Brant MoriarityFor my wife, Amy, and our two boys, Matt and Aidan. I cannot thank them enoughfor their support, love, and endless inspiration.J. Eric KinserTo my parents, who always believed in and encouraged me and have given meunconditional support, patience, and love. To my brother and my hero—may yoube watching from Heaven with joy in your heart.Jennifer Paige NightingaleDedicationsA01 KINS1969 01 SE FM.indd 3iii11/01/19 9:47 PM
About the AuthorsAmy S. Kinser, Esq., Series EditorAmy 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 workingas an environmental chemist, starting her own technology consulting company, andpracticing intellectual property law, she has spent the past 18 years teaching technology at the Kelley School of Business in Bloomington, Indiana. Currently, she serves asthe Director of Computer Skills and Senior Lecturer at the Kelley School of Businessat Indiana University. She also loves spending time with her two sons, Aidan andJ. Matthew, and her husband J. Eric.Brant MoriarityBrant P. Moriarity earned a B.A. in Religious Studies/Philosophy and a M.S. inInformation Systems at Indiana University. He is a Senior Lecturer at the IndianaUniversity’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 ofbusiness analytics and custom database application development to small businessesand non-profit organizations.J. Eric KinserEric Kinser received his B.S. degree in Biology from Indiana University and his M.S. inCounseling and Education from the Indiana School of Education. He has worked in themedical field and in higher education as a technology and decision support specialist.He is currently a senior lecturer in the Operations and Decision Technology Departmentat the Kelley School of Business at Indiana University. When not teaching he enjoysexperimenting with new technologies, traveling, and hiking with his family.Jennifer Paige NightingaleJennifer Nightingale, associate professor at Slippery Rock University of Pennsylvania,has taught Information Systems Management since 2000. Before joining Slippery RockUniversity, she spent 15 years in industry with a focus in management and training.Her research expertise is in instructional technology, using technology as a teachingtool, and the impact of instructional technologies on student learning. She has earnednumerous teaching and research honors and awards, holds an Ed.D. (instructionaltechnology) 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 AuthorsA01 KINS1969 01 SE FM.indd 4 11/01/19 9:47 PM
Brief ContentsCOMMON FEATURESCHAPTER 1: Understanding the Common Features of Microsoft Office1ACCESSACCESS BUSINESS UNIT 1: Understanding and Using a Relational DatabaseCHAPTER 1: The Four Main Database ObjectsCHAPTER 2: Tables, Keys, and RelationshipsACCESS BUSINESS UNIT 1 CAPSTONE495093146ACCESS BUSINESS UNIT 2: Accessing, Maintaining, and Presenting InformationCHAPTER 3: Queries and Data Access162CHAPTER 4: Using Forms and Reports in AccessACCESS BUSINESS UNIT 2 CAPSTONE214262ACCESS BUSINESS UNIT 3: Ensuring Consistent Data and Advanced QueryingCHAPTER 5: Advanced Tables161277278CHAPTER 6: Pattern Matching and Functions in QueriesACCESS BUSINESS UNIT 3 CAPSTONE335375ACCESS BUSINESS UNIT 4: Leveraging Queries for Business Information and IntelligenceCHAPTER 7: Aggregated Calculations, Subquerying, and SQLCHAPTER 8: Action Queries and Advanced RelationshipsACCESS BUSINESS UNIT 4 CAPSTONE388451486ACCESS BUSINESS UNIT 5: Producing Professional Forms and ReportsCHAPTER 9: Advanced Form Settings and Form TypesCHAPTER 10: Advanced Reports and Mailing LabelsACCESS BUSINESS UNIT 5 CAPSTONE497498564607ACCESS BUSINESS UNIT 6: Developing a Polished User Interface and ExperienceCHAPTER 11: Develop Navigation Forms and the User InterfaceCHAPTER 12: Create a Refined User Experience with MacrosACCESS BUSINESS UNIT 6 CAPSTONE653694705706CHAPTER 14: Implement Your DatabaseACCESS BUSINESS UNIT 7 CAPSTONE621622ACCESS BUSINESS UNIT 7: Implementing and Automating a Database with VBACHAPTER 13: Use VBA in Access387747776APPENDIX A: Normalize a Database for Effective Design 787APPENDIX B: Access Microsoft Certification Reference 811GLOSSARY 815INDEX 824Brief ContentsA01 KINS1969 01 SE FM.indd 5v11/01/19 9:47 PM
ContentsWelcome to the Team LetterxxviiCOMMON FEATURES 1CHAPTER 1: Understanding the Common Featuresof Microsoft Office1PREPARE CASE: Painted Paradise Resort & SpaEmployee Training Preparation1Working with the Office Interface 2Understand the Office Suite and Applications 2Start, Save, and Manipulate Office Applicationsand Use the Office Ribbon 3Opening Microsoft Word and the Start Screen 3Using the Ribbon and Ribbon Display Options 5Using Office Backstage, Your Account,and Document Properties 8Saving a New Document to the Local OneDrive ThatSyncs to the Cloud 10Closing a File, Reopening from the Recent DocumentsList, and Exiting an Application 15Opening an Existing File in Microsoft Excel andThen Saving as Another Name 16Zooming, Scrolling, and Navigating withKeyboard Shortcuts 18Using the Quick Access Toolbar to Save a CurrentlyOpen File 20Manipulate, Correct, and FormatContent in Excel 20Checking Spelling 20Entering, Copying, and Pasting Text 22Using Undo to Correct a Mistake 23Finding and Replacing Text 24Using the Font Group and the Font Dialog Box 25Using the Style Gallery with Live Preview 26Inserting a Note and Total Using the Tell mewhat you want to do Box 27Formatting, Finding Help, and Printing in Office 29Format Using Various Office Methods 29Using Excel to Enter Content, Apply Bold, andApply a Fill Color 29Opening an Excel Dialog Box 30Inserting Images and Using ContextualTools to Resize 31Formatting Using the Mini Toolbar 33Opening Shortcut Menus and Format Painter 35Find Help, Print, and Share in OfficeUsing the Help Pane and ScreenTips 36Accessing the Share Pane 38Changing Views 38Printing a File 40Exporting a PDF 41Insert Office Add-insvi4236Concept Check 43Key Terms 43Visual SummaryPractice 14445Problem Solve 146Perform 47Perform in Your Career 47Perform in Your Life 47ACCESS BUSINESS UNIT 149CHAPTER 1: The Four Main DatabaseObjects 50PREPARE CASE: Red Bluff Golf Course Puttsfor Paws Charity Tournament 50Understanding the Basics of Databasesand Tables 51Understand the Purpose of Access 51Understanding the Four Main Objects ina Database 52Creating a New Database and TemplatesOpening the Starting File 54Maneuver in the Navigation Pane5355Opening and Closing the Shutter Bar 55Customizing the Navigation Pane 55Using the Search Box 57Understanding File Extensions in Access 57Understand the Purpose of Tables58Importing a Table 59Navigating through a Table 61Navigating through a Table with the Navigation Bar 62Understanding Differences between Accessand Excel 64Manually Navigate a Database66Using a Manual Query to Explore a Database66Understanding Queries, Forms, and Reports 68Understand the Purpose of Queries 68Using the Query Wizard 69Switching to the Design View of a Query 71Specifying Selection Criteria 72Sorting Query Results 74Printing Query Results 75Understand the Purpose of Forms76Creating a Form 77Entering Data Using a Form 78Understand the Purpose of Reports79Creating a Report Using a Wizard 80Printing a Report 82ContentsA01 KINS1969 01 SE FM.indd 611/01/19 11:16 PM
Back Up a Database 83Forming Two Relationships to a Junction TablePopulating the Junction Table 132Defining One-to-One Relationships 132Backing Up a Database 83Compact and Repair a Database 84Understand Referential IntegrityCompacting Your Database 84Key Terms86Visual Summary87Practice 1 89Concept Check 137Problem Solve 1 90Key TermsPerform 91Perform in Your CareerVisual Summary91Practice 1CHAPTER 2: Tables, Keys, and Relationships93PREPARE CASE: Red Bluff Golf Course Putts forPaws Charity Tournament Database93137138140Problem Solve 1143Perform 144Perform in Your Career144More Practice 1146Problem Solve 1148151Opening the Starting File 95Viewing the Design View of a Table96Problem Solve 2Import Data from Other Sources97Perform 154Perform in Your Life 154Perform in Your Career 155Perform in Your Team 157How Others Perform 158Copying and Pasting Data from Excel 97Importing a Worksheet 98Importing from a Named Range 100Importing from a Text File 102Enter Data Manually 103Entering Data Using Datasheet View 103Deleting Data from a Table 105Deleting a Field from a Table 106ACCESS BUSINESS UNIT 2Working with Tables 163Find and Replace Records in a TableOpening the Starting File 163Finding Records in a Table 163Finding and Replacing Records in a TableUsing a Wildcard Character 165Applying a Filter to a Table 167Using a Text Filter 168111Defining Input Masks 111Formatting a Field 113115Understanding Primary Keys 115Understanding Foreign Keys 115Identifying a Composite Key 116Defining a Primary Key 117Modify Table Appearance163165169Changing the Look of a Table 169Understanding Relational Databases 119Understand Basic Principles of Normalization 120Representing Entities and AttributesMinimizing Redundancy 121162PREPARE CASE: Turquoise Oasis SpaData Management 162Defining Data Types 107Determining Field Size 109Creating a Table in Design View 109Changing a Data Type 111Understand and Designate Keys161CHAPTER 3: Queries and Data AccessUnderstanding Tables and Keys 107Create a Table in Design View 107Understand Masks and Formatting135ACCESS BUSINESS UNIT 1 CAPSTONE 146Inserting Data Into a Database 94Understand Database Design 94120Understand Relationships between Tables121Viewing the Relationships Window 122Determining Relationship Types 123Create a One-to-Many RelationshipForming a Relationship132Selecting Cascade Update 132Selecting Cascade Delete 133Testing Referential Integrity 133Creating a Report Using Two Related TablesConcept Check 86130124124Create a Many-to-Many Relationship127Forming a New Many-to-Many Relationship 128Creating a Junction Table 129Querying the Database 170Run Query Wizards 170Creating a Find Duplicates Query 170Creating a Find Unmatched Query 172Create Queries in Design View173Creating a Single-Table Query 175Viewing Table Relationships 176Creating a Query Using Multiple Tables 178Removing a Table from a Query to Fix theMultiplier Effect 179Sort Table and Query Results181Sorting a Table by a Single Field 181Sorting by More Than One Field 181Contents A01 KINS1969 01 SE FM.indd 7vii12/01/19 1:32 AM
Define Selection Criteria for Queries183Using a Comparison Operator 183Hiding Fields That Are Used in a Query 184Sorting on a Hidden Field 184Using Is Null Criteria 185Using the Criteria Row with Multiple Criteria 187Using the Or Criteria Row 188Using Both the Criteria Row and the Or Criteria Rows 189Using AND and OR Logical Operators 190Combining Operators and Multiple Criteria 192Using Special Operators and Date Criteria 194Combining Special Operators and Logical Operators 196Create Aggregate Functions197Adding a Total Row 197Using Aggregate Functions 198Changing Field Names 200Creating Calculations for Groups of RecordsTroubleshooting an Aggregate Query 202Formatting an Aggregated Field 203Create Calculated FieldsKey TermsPractice 1Visual SummaryPractice 1 256Problem Solve 1 258260ACCESS BUSINESS UNIT 2 CAPSTONE 262208212CHAPTER 4: Using Forms and Reports214PREPARE CASE: Turquoise Oasis Spa’s NewDatabase 214Creating Customized Forms 215Navigate and Edit Records in Datasheets215Opening the Starting File 215Editing a Record in Datasheet View of a Table 216Navigate Forms and Subforms217Navigating a Main Form 217Navigating a Form with a Subform 219Navigating a Split Form 220Using the Find Command in a Form 221Update Table Records Using Forms222Create a Form Using the Form WizardCreating a Single-Table Form 225Creating a Subform (Multiple-Table Form)Creating a Split Form 230More Practice 1262Problem Solve 1266Problem Solve 2268Perform 270Perform in Your Life 270Perform in Your Career 272Perform in Your Team 273How Others Perform 275ACCESS BUSINESS UNIT 3CHAPTER 5: Advanced Tables277278PREPARE CASE: The Red Bluff Golf Course & ProShop: Modifying Database Tables 278Controlling the Way Data Is Entered: AdvancedField Options 279Understand the Purpose and Benefits of InputMasks 279Opening the Starting File 279Using the Input Mask Wizard 280Creating a Custom Input Mask 282Adding a New Record Using a Form 223Editing Records Using a Form 223Deleting a Record Using a Form 224225Apply Custom Formatting287Creating Custom Formatting 288227230Changing the Form Theme 231Resizing and Changing Controls on a Form 232Adding a Picture to the Form 234Previewing and Printing a Record from a Form 235viii204254Perform 260Perform in Your Career207Modify a Form’s Design252Key Terms 253204Problem Solve 1 211in Access245Moving, Resizing, and Formatting Report Controls 245Enhancing a Report with Conditional Formatting 247Applying Grouping and Sorting 249Adding Subtotals 250Concept Check 253201206Perform 212Perform in Your CareerCustomize a ReportSaving a Report as a PDF File 252206Visual Summary236Creating a Single-Table Report Using the ReportWizard 237Creating a Multiple-Table Report Using theReport Wizard 238Exploring Report Views 240Creating Report Totals Using the Report Wizard 242Save a Report as a PDF FileBuilding a Calculated Field Using Expression BuilderConcept CheckCreating Customized Reports 236Create a Report Using the Report WizardDefine Data Validation Rules293Creating Validation Rules and Validation Text294Define Caption Names 296Creating Captions for Existing Fields 297Create Indexes to Improve Performance298Defining a Single-Field Index 299Defining Multiple-Field Indexes 300ContentsA01 KINS1969 01 SE FM.indd 811/01/19 9:47 PM
Require Data in Fields 301Making Fields RequiredUsing DateAdd Function to Subtract a Time Interval 361Using the DateSerial Function to Return a Date (Year,Month, and Day) 363Using the DatePart Function to Evaluate a Date 365302Define Default Values302Setting a Default Value 303Use the Round FunctionAdvanced Data Types, Data Filtering,and the Table Analyzer 304Create Lookup Fields 304Configure Fields Using the Calculated, Yes/No,AutoNumber, Attachment, Hyperlink, and OLEObject Data Types 312Configuring Fields Using the Calculated Data Type 312Configuring Fields Using the Yes/No Data Type 314Configuring Fields Using the AutoNumber Data Type 316Configuring Fields Using the Attachment, Hyperlink, andOLE Object Data Types 318321Creating Filters 321Use the Table Analyzer Wizard to ReduceRedundancy 324Using the Table Analyzer Wizard325Concept Check 327Key Terms327Visual Summary328369Practice 1370Problem Solve 1372Perform 374Perform in Your Life374ACCESS BUSINESS UNIT 3 CAPSTONE 375More Practice 1375Problem Solve 1378Problem Solve 2380Perform 382Perform in Your Life 382Perform in Your Career 383Perform in Your Team 383How Others Perform 385387CHAPTER 7: Aggregated Calculations,Problem Solve 1 331Subquerying, and SQL333Spa Database: Querying with AdvancedCalculations 388in Queries 335PREPARE CASE: The Red Bluff Golf ClubDatabase 335Working with Advanced Criteria andCalculations 336Use Wildcard Characters in String Comparisons 336Opening the Starting File 336Working with Wildcard Characters and the Like Operator 337Find Records with the “Most” or “Least” Values 341Retrieving Top Values 341Create Parameter QueriesUsing Parameters in a Query343344Concatenate Strings Using the & Operator 347Use Advanced Query Operators 349Using Advanced Functions in Queries 352Create IIf Functions 352Creating Basic IIf Functions for Individual Conditions 352Creating Nested IIf Functions for Various Conditions 354Use the IsNull Function388PREPARE CASE: The Turquoise OasisCHAPTER 6: Pattern Matching and Functions356Working with Fields That Contain No Valid Data 356Use Date FunctionsKey TermsACCESS BUSINESS UNIT 4Practice 1 329Perform 333How Others Perform358Using the DateDiff Function to Determine a TimeInterval 359Understanding the GROUP BY Clause inAggregated Calculations 389Opening the Spa Decisions Database389Use the GROUP BY Clause in AggregatedCalculations 389Calculating Revenue and Sales Volume 390Calculating the Number of Transactionsby Category 392Summarizing Duplicate Data Using the GROUPBY Clause 393Understanding the WHERE Clause inAggregated Calculations 394Use the WHERE Clause in AggregatedCalculations 395Calculating Revenue from Specific Spa Services 395Calculating Net Revenue from Specific Spa Services 396Business Calculations Using Subquerying 400Create Subqueries Using BusinessCalculations 400Creating a Query on a Query 400Calculating the Percentage of Sales Revenue 402Calculating the Percentage of Sales Volume 406Calculating Physical Volume 409Calculating the Percentage of Physical Volume 411ContentsA01 KINS1969 01 SE FM.indd 9367Concept Check 369Creating Lookup Fields and Modifying LookupProperties 306Create Filters to View Specific Records367Rounding to a Specific Number of Decimal Placesix11/01/19 11:46 PM
Understanding Structured Query Language 413Create Basic Structured Query Language (SQL)Queries 414Viewing SQL Statements in Access 414Understanding SELECT Statement Basics 415Creating a Basic Query with SQL 415Using the WHERE Clause 416Using the ORDER BY Clause 420Using the HAVING Clause with AS andGROUP BY 422Incorporate Joins into Structured Query Language(SQL) Queries 424Creating Queries Using the INNER JOIN Clause 424Creating Queries Using OUTER JOINS 428Creating a Union Query 435Creating a SQL Subquery 436Create a Two-Dimensional Query Using theCrosstab Query Wizard 438Using the Crosstab Query WizardEditing a Crosstab Query 441Concept CheckKey Terms444Problem Solve 1 448Relationships 451PREPARE CASE: The Turquoise Oasis Sap:Understanding Action Queries 451Understanding Action Queries 452Create a New Table Using a MakeTable Query 452Problem Solve 1 483Perform 484Perform in Your Life484ACCESS BUSINESS UNIT 4 CAPSTONE 486More Practice 1486Problem Solve 1489Problem Solve 2491Perform 493Perform in Your Life 493Perform in Your Career 494Perform in Your Team 495How Others Perform 496497Append Data to a Table457504Using the Expression Builder to Refer to FormFields as Criterion 508Incorporating Wildcards into Criteria with Form Fields 511459Finalize and Test the Query by FormWorking with Update Queries 459Creating Complex Update Queries 461Create, Test, and Run Delete Queries465Creating Simple Delete Queries 465Building Complex Delete Queries 466Advanced Relationships Using Multiple Tables 470Create Inner Joins 470470471Creating an Outer Join QueryDefine Parameter Field PropertiesModify the Query to Use the Form Fields asCriteria 508Creating an Append Query 457Running an Append Query 458Creating an Inner Join QueryOpening the Starting File 499Examining the QBF Query 499Creating an Unbound Form 500Adding Text Boxes for Criteria 501Adding a Combo Box for Criteria 503Editing the Name Property 504Editing the Width Property 506Incorporating a Default Value 506Using an Input Mask to Control Data Entry 507Opening the Starting Files 453Creating a Make Table Query 453Running a Make Table Query 454472Use the Find Unmatched Query WizardCreating a Find Unmatched Data Query 474xPractice 1 480Using the Query by Form (QBF) Technique 499Develop the Query by Form Technique 499449CHAPTER 8: Action Queries and AdvancedCreate Outer Joins479Form Types 498PREPARE CASE: Enhancing at the Red Bluff GolfCourse & Pro Shop Database 498446Edit and Delete DataVisual SummaryCHAPTER 9: Advanced Form Settings and445Perform 449Perform in Your LifeKey Terms 478ACCESS BUSINESS UNIT 5444Visual SummaryPractice 1438Concept Check 478474512Adding a Button to the Form to Run the QueryTesting the Query by Form Technique 514513Enhancing the Usability of Bound Forms 515Enhance a Bound Form 515Creating a Bound Form 515Making Changes in Layout View 516Modify the Form Property Sheet517Disabling a Field in a Form 519Changing Default Values for a Field 520Using Dates and Calculations as Default ValuesChanging Properties of the Form 524Displaying the Date Picker 525521ContentsA01 KINS1969 01 SE FM.indd 1011/01/19 9:47 PM
Using Advanced Form Modification 526Modify the Form Header 526Adding a Logo to the Form Header 527Adding the Date and Time to the Form HeaderModify the Form in Design ViewUse the Group, Sort, and Total Pane528529Adding and Stacking Fields 530Changing Tab Order 531Adding a COUNT Calculated Field 532Adding a SUM Calculated Field 534Adding Fields from a Subform to the Main Form 535Adding a Combo Box to Find a Record 537Anchoring Controls 538Adding Shapes to a Form 539Adding Record Navigation Buttons 540Adding Form Operations Buttons 542Editing Actions of Form Operations Buttons 543Aligning Buttons 544Turning Off the Navigation Buttons 544Creating Advanced Forms 546Create Specialized Forms 546Creating a Multiple Items Form 546Create a Multipage Form Using Tab ControlsAdding a Tab Control 547Inserting Tab Pages and Changing Tab CaptionsInserting Existing Fields in a Tab Page 550Rearranging, Moving, and Resizing theTab Control 552547549Concept Check 554Key TermsAdd Labels and Shapes to a Report555580Adding Labels to a Report 580Adding a Horizontal Line to a Report 581Modify Calculated Fields in a ReportModifying Existing Calculated Fields582582Creating Parameter Reports 583Create a Parameter Report 584Creating a Report from a Parameter Query 584Creating a Form for Entering Parameters 584Modifying a Query to Use New Form Fields 586Modifying a Report to Use New Form Fields 588Adding Report Buttons to a Form 590Use Conditional Formatting in a Report591Adding Conditional Formatting to a Report 591Creating Labels 593Create Mailing Labels 593Creating Mailing Labels 593Adding Mailing Label Buttons to a Form 595Concept Check 598Key Terms 598Visual Summary599Practice 1 600Problem Solve 1 604Perform 605Perform in Your Career554Visual Summary576Changing Group, Sort, and Total Options 576Hiding Details on a Summary Report 578605Practice 1 557ACCESS BUSINESS UNIT 5 CAPSTONE 607Problem Solve 1 560More Practice 1607Problem Solve 1610Problem Solve 2613Perform 562Perform in Your Career562CHAPTER 10: Advanced Reports and MailingLabels 564PREPARE CASE: Enhancing Database Reportsat the Red Bluff Golf Club 564Creating Customized Reports 565Use the Summary Options in ReportWizard 566Opening the Starting File 566Adding Subtotals and Totals Using the SummaryOptions 566Modify the Report in Design View568Selecting and Modifying Controls inDesign View 570Moving, Modifying, and Aligning Controls inDesign View 571Change Report Properties573Add and Remove Fields from a ReportRemoving Fields from a Report 574Adding Fields to a Report 574ACCESS BUSINESS UNIT 6621CHAPTER 11: Develop Navigation Formsand the User Interface 622PREPARE CASE: The Turquoise Oasis SpaDatabase: Creating a User Interface 622Creating Navigation Forms 623View a Navigation Form 623572Modifying the Hide Duplicates PropertyPerform 615Perform in Your Life 615Perform in Your Career 616Perform in Your Team 617How Others Perform 619574Opening the Starting File 623Opening and Using Navigation Forms 624Create a Navigation Form625Using a Predefined Layout to Create a NavigationForm 625ContentsA01 KINS1969 01 SE FM.indd 11xi11/01/19 9:47 PM
Modify a Navigation Form626Adding Objects to a Navigation Form 627Deleting an Object from a Navigation Form 627Moving an Object on a Navigation Form 628Modifying the Appearance of Buttons on aNavigation Form 629Refining the User Interface 630Create a Main Menu 632Add Command Buttons to the Navigation Form 635Creating Command Buttons on a Form for Navigation 636Removing Navigation Buttons and Record Selectorson a Form 639Set Start-Up Display Options and Test theApplication 640Making Changes to the Start-Up Options 640Viewing the User Interface as a User 644Key Terms645645Visual SummaryCreating an AutoExec Macro 676The Benefits of Embedded Macros 678Improve the Functionality of Forms andReports 680Embedding a Macro to Improve User Experience 680Embedding a Macro to Increase Functionality 682Creating a Main Menu Navigation Form 634Adding an Image to the Main Menu 635Concept CheckCreate Macros That Run When the DatabaseOpens 676Implement Complex Business Rules with DataMacros 684Creating a Data MacroConcept Check 688Key Terms688Visual SummaryPractice 1689690Problem Solve 1691Perform 693Perform in Your Career694Problem Solve 1 650Problem Solve 1697Perform 651Perform in Your LifeProblem Solve 2698647651CHAPTER 12: Create a Refined User Experiencewith Macros 653PREPARE CASE: The Turquoise Oasis SpaDatabase: Automating Tasks and IncreasingFunctionality 653Understanding the Purpose of Macros 654Modify Database Settings for Protectionfrom Macro Viruses 654Opening the Starting File 654Creating a Trusted Location 655Understand the Macro Designer657Opening and Viewing the Macro Designer 657Editing a Macro 659Printing a Macro 661Understand How to Test and TroubleshootMacros 662Single Stepping through a Macro 662Increase Efficiency and Usability of a Databaseby Automating Tasks 665Improve Database Design and Function byAutomating Manual Processes 666Exporting Database Objects Using Macros666Reduce Processing Time by CombiningRoutine Tasks 668Automating an Import and Update ProcessCreate Macro Groups672Creating a Macro Group 672Creating a Key-Assignment
Brief Contents Brief Contents v CHAPTER 1: Understanding the Common Features of Microsoft Office1 COMMON FEATURES 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
