Access 2016 Bible

Michael Alexander is a Microsoft Certified Application Developer. For more than 15 years, he has been consulting and developing Microsoft Office solutions. Michael is a Microsoft MVP and runs a free tutorial website at www.datapigtechnologies.com.Dick Kusleika has been named a Microsoft MVP for 12 consecutive years. He develops Access- and Excel-based solutions for clients, and has worked with Microsoft Office for more than 20 years. Dick writes a popular Excel-related blog at www.dailydoseofexcel.com.

• Introduction xxxixPart I: Access Building Blocks 1Chapter 1: An Introduction to Database Development 3The Database Terminology of Access 3Relational Databases 6Access Database Objects 7A Five-Step Design Method 10Chapter 2: Getting Started with Access 19The Access Welcome Screen 19How to Create a Blank Desktop Database 21The Access 2016 Interface 23The Ribbon 26The Quick Access toolbar 28Part II: Understanding Access Tables 31Chapter 3: Creating Access Tables 33Table Types 33Creating a New Table 34Creating tblCustomers 51Changing a Table Design 52Understanding tblCustomers Field Properties 70Setting the Primary Key 73Indexing Access Tables 75Printing a Table Design 81Saving the Completed Table 83Manipulating Tables 83Adding Records to a Database Table 85Understanding Attachment Fields 86Chapter 4: Understanding Table Relationships 89Building Bulletproof Databases 90Data Normalization and Denormalization 92Table Relationships 104Integrity Rules 113Keys 116Chapter 5: Working with Access Tables 131Understanding Datasheets 132Looking at the Datasheet Window 134Opening a Datasheet 138Entering New Data 138Navigating Records in a Datasheet 145Changing Values in a Datasheet 148Using the Undo Feature 150Copying and Pasting Values 151Replacing Values 152Adding New Records 153Deleting Records 153Displaying Records 154Sorting and Filtering Records in a Datasheet 163Aggregating Data 168Printing Records 169Previewing Records 170Chapter 6: Importing and Exporting Data 171How Access Works with External Data 171Options for Importing and Exporting 175Importing External Data 177Exporting to External Formats 200Chapter 7: Linking to External Data 209Linking External Data 210Working with Linked Tables 224Splitting a Database 228Part III: Working with Access Queries 235Chapter 8: Selecting Data with Queries 237Introducing Queries 238Creating a Query 240Working with Query Fields 246Adding Criteria to Your Queries 250Printing a Query’s Recordset 254Saving a Query 254Creating Multi-Table Queries 254Working with the Table Pane 259Creating and Working with Query Joins 261Chapter 9: Using Operators and Expressions in Access 267Introducing Operators 267Using Operators and Expressions in Queries 282Entering Single-Value Field Criteria 288Using Multiple Criteria in a Query 296Entering Criteria in Multiple Fields 302Chapter 10: Going Beyond Select Queries 309Aggregate Queries 309Action Queries 318Crosstab Queries 333Optimizing Query Performance 345Part IV: Analyzing Data in Access 349Chapter 11: Transforming Data in Access 351Finding and Removing Duplicate Records 351Common Transformation Tasks 359Chapter 12: Working with Calculations and Dates 375Using Calculations in Your Analyses 375Using Dates in Your Analyses 388Chapter 13: Performing Conditional Analyses 401Using Parameter Queries 401Using Conditional Functions 410Chapter 14: The Fundamentals of Using SQL 421Understanding Basic SQL 421Getting Fancy with Advanced SQL Statements 426Using SQL-Specific Queries 433Chapter 15: Subqueries and Domain Aggregate Functions 441Enhancing Your Analyses with Subqueries 442Domain Aggregate Functions 455Chapter 16: Running Descriptive Statistics in Access 467Basic Descriptive Statistics 468Advanced Descriptive Statistics 476Part V: Working with Access Forms and Reports 483Chapter 17: Creating Basic Access Forms 485Formulating Forms 486Working with Controls 498Introducing Properties 517Chapter 18: Working with Data on Access Forms 525Using Form View 526Changing Values in a Form 531Printing a Form 538Working with Form Properties 539Adding a Form Header or Footer 550Working with Section Properties 550Changing the Layout 552Converting a Form to a Report 556Chapter 19: Working with Form Controls 557Setting Control Properties 558Creating a Calculated Control 565Working with Subforms 565Form Design Tips 567Tackling Advanced Forms Techniques 570Using the Tab Control 577Using Dialog Boxes to Collect Information579Designing a Form from Scratch 582Chapter 20: Presenting Data with Access Reports 591Introducing Reports 592Creating a Report, from Beginning to End 596Banded Report Design Concepts 614Creating a Report from Scratch 620Improving the Report’s Appearance 642Chapter 21: Advanced Access Report Techniques 649Grouping and Sorting Data 650Formatting Data 662Adding Data 680Adding Even More Flexibility 682Part VI: Access Programming Fundamentals 695Chapter 22: Using Access Macros 697An Introduction to Macros 698Understanding Macro Security 702Multi-Action Macros 706Submacros 709Conditions 713Temporary Variables 716Error Handling and Macro Debugging 721Embedded Macros 727Macros versus VBA Statements 729Chapter 23: Using Access Data Macros 733Introducing Data Macros 734Understanding Table Events 735Using the Macro Designer for Data Macros 738Understanding the Action Catalog 740Creating Your First Data Macro 743Managing Macro Objects 747Recognizing the Limitations of Data Macros 749Chapter 24: Getting Started with Access VBA 751Introducing Visual Basic for Applications 752Understanding VBA Terminology 754Starting with VBA Code Basics 755Creating VBA Programs 755Understanding VBA Branching Constructs 770Working with Objects and Collections 780Exploring the Visual Basic Editor 785Chapter 25: Mastering VBA Data Types and Procedures 795Using Variables 796Working with Data Types 803Understanding Subs and Functions824Creating Functions 830Simplifying Code with Named Arguments 836Chapter 26: Understanding the Access Event Model 839Programming Events 840Identifying Common Events 843Paying Attention to Event Sequence 852Chapter 27: Debugging Your Access Applications 861Organizing VBA Code 862Testing Your Applications 863Traditional Debugging Techniques 870Using the Access Debugging Tools 876Trapping Errors in Your Code 889Part VII: Advanced Access Programming Techniques 895Chapter 28: Accessing Data with VBA Code 897Working with Data 897Understanding ADO Objects 901Understanding DAO Objects 914Writing VBA Code to Update a Table 924Chapter 29: Advanced Data Access with VBA 941Adding an Unbound Combo Box to a Form to Find Data 941Filtering a Form 949Chapter 30: Customizing the Ribbon 959The Ribbon Hierarchy 959Editing the Default Ribbon 965Working with the Quick Access Toolbar 968Developing Custom Ribbons 971Creating a Custom Ribbon 974The Basic Ribbon XML 983Adding Ribbon Controls 985Attaching Ribbons to Forms and Reports 994Removing the Ribbon Completely 996Chapter 31: Preparing Your Access Application for Distribution 999Defining the Current Database Options 1000Developing the Application 1009Polishing Your Application 1015Bulletproofing an Application 1029Securing the Environment 1040Part VIII: Access and Windows SharePoint Services 1045Chapter 32: Integrating Access with SharePoint 1047Introducing SharePoint 1047Understanding SharePoint Sites 1048Sharing Data between Access and SharePoint 1051Using SharePoint Templates 1060Chapter 33: Deploying Access Applications to SharePoint 1063Understanding Web Publishing with Access 1064Examining the Limitations of Access Web Applications 1067Publishing a Custom Access Application to SharePoint 1070Index 1091