Michael Alexander is a Microsoft Certified Application Developer (MCAD) and a Microsoft MVP with over 15 years of experience developing reporting solutions for a variety of industries. He is also the principal contributor at datapigtechnologies.com, where he shares video tutorials with the Microsoft Access and Excel communities. Mike has authored several books on advanced business analysis. Dick Kusleika is a 12-time Microsoft Excel MVP, and the principal contributor at the Daily Dose of Excel blog.

Select Guide Rating

• 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 9Chapter 2: Getting Started with Access 17The Access Welcome Screen 17How to Create a Blank Database 18The Access 2019 Interface 20The Ribbon 24The Quick Access toolbar 25Part II: Understanding Access Tables 29Chapter 3: Creating Access Tables 31Table Types 31Creating a New Table 32Creating tblCustomers 49Changing a Table Design 50Understanding tblCustomers Field Properties 68Setting the Primary Key 71Indexing Access Tables 73Printing a Table Design 79Saving the Completed Table 81Manipulating Tables 81Adding Records to a Database Table 84Understanding Attachment Fields 84Chapter 4: Understanding Table Relationships 87Building Bulletproof Databases 88Data Normalization and Denormalization 90Table Relationships 102Integrity Rules 110Keys 113Creating relationships and enforcing referential integrity 119Viewing all relationships 125Deleting relationships 126Following application-specific integrity rules 126Chapter 5: Working with Access Tables 129Understanding Datasheets 130Looking at the Datasheet Window 131Opening a Datasheet 135Entering New Data 135Navigating Records in a Datasheet 142Changing Values in a Datasheet 146Using the Undo Feature 148Copying and Pasting Values 148Replacing Values 149Adding New Records 150Deleting Records 150Displaying Records 152Sorting and Filtering Records in a Datasheet 160Aggregating Data 165Printing Records 166Previewing Records 167Chapter 6: Importing and Exporting Data 169How Access Works with External Data 169Options for Importing and Exporting 173Exporting to External Formats 202Chapter 7: Linking to External Data 209Linking External Data 210Working with Linked Tables 225Splitting a Database 229Part III: Working with Access Queries 237Chapter 8: Selecting Data with Queries 239Introducing Queries 240Creating a Query 241Working with Query Fields 247Adding Criteria to Your Queries 252Printing a Query’s Recordset 255Saving a Query 256Creating Multi-table Queries 256Working with the Table Pane 261Creating and Working with Query Joins 262Chapter 9: Using Operators and Expressions in Access 269Introducing Operators 269Using Operators and Expressions in Queries 283Entering Single-Value Field Criteria 289Using Multiple Criteria in a Query 297Entering Criteria in Multiple Fields 304Chapter 10: Going Beyond Select Queries 311Aggregate Queries 311Action Queries 320Crosstab Queries 336Optimizing Query Performance 348Part IV: Analyzing Data in Microsoft Access 353Chapter 11: Transforming Data in Access 355Finding and Removing Duplicate Records 355Common Transformation Tasks 363Chapter 12: Working with Calculations and Dates 379Using Calculations in Your Analyses 379Using Dates in Your Analyses 392Chapter 13: Performing Conditional Analyses 405Using Parameter Queries 405Using Conditional Functions 414The Switch function 420Comparing the IIf and Switch functions 421Chapter 14: The Fundamentals of Using SQL 425Understanding Basic SQL 425Getting Fancy with Advanced SQL Statements 430Showing only the SELECT TOP or SELECT TOP PERCENT 433Performing action queries via SQL statements 436Using SQL-Specific Queries 438Chapter 15: Subqueries and Domain Aggregate Functions 445Enhancing Your Analyses with Subqueries 446Domain Aggregate Functions 459Examining the syntax of domain aggregate functions 461Using domain aggregate functions 463Chapter 16: Running Descriptive Statistics in Access 469Basic Descriptive Statistics 470Advanced Descriptive Statistics 479Part V: Working with Access Forms and Reports 487Chapter 17: Creating Basic Access Forms 489Working with Form Views 489Understanding Different Types of Forms 490Working with Controls 503Introducing Properties 524Chapter 18: Working with Data on Access Forms 531Using Form View 532Changing Values in a Form 537Printing a Form 544Working with Form Properties 545Adding a Form Header or Footer 556Working with Section Properties 556Changing the Layout 558Converting a Form to a Report 562Chapter 19: Working with Form Controls 563Setting Control Properties 563Creating a Calculated Control 571Working with Subforms 572Form Design Tips 573Tackling Advanced Forms Techniques 576Using the Tab Control 583Using Dialog Boxes to Collect Information 586Designing a Form from Scratch 589Chapter 20: Presenting Data with Access Reports 599Introducing Reports 599Creating a Report, from Beginning to End 604Creating a report with the Report Wizard 605Banded Report Design Concepts 622Creating a Report from Scratch 628Improving the Report’s Appearance 651Chapter 21: Advanced Access Report Techniques 657Grouping and Sorting Data 658Formatting Data 670Adding Data 688Adding Even More Flexibility 690Part VI: Microsoft Access Programming Fundamentals 703Chapter 22: Using Access Macros 705An Introduction to Macros 705Understanding Macro Security 709Multi-action Macros 713Submacros 716Conditions 720Temporary Variables 723Error Handling and Macro Debugging 728Embedded Macros 734Macros versus VBA Statements 736Chapter 23: Using Access Data Macros 741Introducing Data Macros 742Understanding Table Events 742Using the Macro Builder for Data Macros 745Understanding the Action Catalog 747Creating Your First Data Macro 751Managing Macro Objects 755Recognizing the Limitations of Data Macros 757Chapter 24: Getting Started with Access VBA 759Introducing Visual Basic for Applications 760Understanding VBA Terminology 761Starting with VBA Code Basics 762Creating VBA Programs 763Modules and procedures 763Working in the code window 770Understanding VBA Branching Constructs 777Working with Objects and Collections 786Exploring the Visual Basic Editor 792Chapter 25: Mastering VBA Data Types and Procedures 801Using Variables 802Working with Data Types 809Understanding Subs and Functions 829Creating Functions 835Simplifying Code with Named Arguments 840Chapter 26: Understanding the Access Event Model 843Programming Events 844Identifying Common Events 847Paying Attention to Event Sequence 856Chapter 27: Debugging Your Access Applications 865Organizing VBA Code 866Testing Your Applications 867Traditional Debugging Techniques 873Using the Access Debugging Tools 879Trapping Errors in Your Code 893Part VII: Advanced Access Programming Techniques 899Chapter 28: Accessing Data with VBA 901Working with Data 901Understanding DAO Objects 904Understanding ADO Objects 917Writing VBA Code to Update a Table 923Chapter 29: Advanced Data Access with VBA 937Adding an Unbound Combo Box to a Form to Find Data 937Filtering a Form 945Chapter 30: Customizing the Ribbon 955The Ribbon Hierarchy 955Controls for Access Ribbons 956Editing the Default Ribbon 961Working with the Quick Access Toolbar 964Developing Custom Ribbons 966Creating a Custom Ribbon 969The Basic Ribbon XML 977Adding Ribbon Controls 979Attaching Ribbons to Forms and Reports 989Removing the Ribbon Completely 990Chapter 31: Preparing Your Access Application for Distribution 993Defining the Current Database Options 994Polishing Your Application 1009Bulletproofing an Application 1022Chapter 32: Integrating Access with SharePoint 1039Introducing SharePoint 1039Understanding SharePoint Sites 1040Sharing Data between Access and SharePoint 1043Using SharePoint Templates 1052Index 1055

Michael Alexander is a Microsoft Certified Application Developer (MCAD) and a Microsoft MVP with over 15 years of experience developing reporting solutions for a variety of industries. He is also the principal contributor at datapigtechnologies.com, where he shares video tutorials with the Microsoft Access and Excel communities. Mike has authored several books on advanced business analysis. Dick Kusleika is a 12-time Microsoft Excel MVP, and the principal contributor at the Daily Dose of Excel blog.

Select Guide Rating

• 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 9Chapter 2: Getting Started with Access 17The Access Welcome Screen 17How to Create a Blank Database 18The Access 2019 Interface 20The Ribbon 24The Quick Access toolbar 25Part II: Understanding Access Tables 29Chapter 3: Creating Access Tables 31Table Types 31Creating a New Table 32Creating tblCustomers 49Changing a Table Design 50Understanding tblCustomers Field Properties 68Setting the Primary Key 71Indexing Access Tables 73Printing a Table Design 79Saving the Completed Table 81Manipulating Tables 81Adding Records to a Database Table 84Understanding Attachment Fields 84Chapter 4: Understanding Table Relationships 87Building Bulletproof Databases 88Data Normalization and Denormalization 90Table Relationships 102Integrity Rules 110Keys 113Creating relationships and enforcing referential integrity 119Viewing all relationships 125Deleting relationships 126Following application-specific integrity rules 126Chapter 5: Working with Access Tables 129Understanding Datasheets 130Looking at the Datasheet Window 131Opening a Datasheet 135Entering New Data 135Navigating Records in a Datasheet 142Changing Values in a Datasheet 146Using the Undo Feature 148Copying and Pasting Values 148Replacing Values 149Adding New Records 150Deleting Records 150Displaying Records 152Sorting and Filtering Records in a Datasheet 160Aggregating Data 165Printing Records 166Previewing Records 167Chapter 6: Importing and Exporting Data 169How Access Works with External Data 169Options for Importing and Exporting 173Exporting to External Formats 202Chapter 7: Linking to External Data 209Linking External Data 210Working with Linked Tables 225Splitting a Database 229Part III: Working with Access Queries 237Chapter 8: Selecting Data with Queries 239Introducing Queries 240Creating a Query 241Working with Query Fields 247Adding Criteria to Your Queries 252Printing a Query’s Recordset 255Saving a Query 256Creating Multi-table Queries 256Working with the Table Pane 261Creating and Working with Query Joins 262Chapter 9: Using Operators and Expressions in Access 269Introducing Operators 269Using Operators and Expressions in Queries 283Entering Single-Value Field Criteria 289Using Multiple Criteria in a Query 297Entering Criteria in Multiple Fields 304Chapter 10: Going Beyond Select Queries 311Aggregate Queries 311Action Queries 320Crosstab Queries 336Optimizing Query Performance 348Part IV: Analyzing Data in Microsoft Access 353Chapter 11: Transforming Data in Access 355Finding and Removing Duplicate Records 355Common Transformation Tasks 363Chapter 12: Working with Calculations and Dates 379Using Calculations in Your Analyses 379Using Dates in Your Analyses 392Chapter 13: Performing Conditional Analyses 405Using Parameter Queries 405Using Conditional Functions 414The Switch function 420Comparing the IIf and Switch functions 421Chapter 14: The Fundamentals of Using SQL 425Understanding Basic SQL 425Getting Fancy with Advanced SQL Statements 430Showing only the SELECT TOP or SELECT TOP PERCENT 433Performing action queries via SQL statements 436Using SQL-Specific Queries 438Chapter 15: Subqueries and Domain Aggregate Functions 445Enhancing Your Analyses with Subqueries 446Domain Aggregate Functions 459Examining the syntax of domain aggregate functions 461Using domain aggregate functions 463Chapter 16: Running Descriptive Statistics in Access 469Basic Descriptive Statistics 470Advanced Descriptive Statistics 479Part V: Working with Access Forms and Reports 487Chapter 17: Creating Basic Access Forms 489Working with Form Views 489Understanding Different Types of Forms 490Working with Controls 503Introducing Properties 524Chapter 18: Working with Data on Access Forms 531Using Form View 532Changing Values in a Form 537Printing a Form 544Working with Form Properties 545Adding a Form Header or Footer 556Working with Section Properties 556Changing the Layout 558Converting a Form to a Report 562Chapter 19: Working with Form Controls 563Setting Control Properties 563Creating a Calculated Control 571Working with Subforms 572Form Design Tips 573Tackling Advanced Forms Techniques 576Using the Tab Control 583Using Dialog Boxes to Collect Information 586Designing a Form from Scratch 589Chapter 20: Presenting Data with Access Reports 599Introducing Reports 599Creating a Report, from Beginning to End 604Creating a report with the Report Wizard 605Banded Report Design Concepts 622Creating a Report from Scratch 628Improving the Report’s Appearance 651Chapter 21: Advanced Access Report Techniques 657Grouping and Sorting Data 658Formatting Data 670Adding Data 688Adding Even More Flexibility 690Part VI: Microsoft Access Programming Fundamentals 703Chapter 22: Using Access Macros 705An Introduction to Macros 705Understanding Macro Security 709Multi-action Macros 713Submacros 716Conditions 720Temporary Variables 723Error Handling and Macro Debugging 728Embedded Macros 734Macros versus VBA Statements 736Chapter 23: Using Access Data Macros 741Introducing Data Macros 742Understanding Table Events 742Using the Macro Builder for Data Macros 745Understanding the Action Catalog 747Creating Your First Data Macro 751Managing Macro Objects 755Recognizing the Limitations of Data Macros 757Chapter 24: Getting Started with Access VBA 759Introducing Visual Basic for Applications 760Understanding VBA Terminology 761Starting with VBA Code Basics 762Creating VBA Programs 763Modules and procedures 763Working in the code window 770Understanding VBA Branching Constructs 777Working with Objects and Collections 786Exploring the Visual Basic Editor 792Chapter 25: Mastering VBA Data Types and Procedures 801Using Variables 802Working with Data Types 809Understanding Subs and Functions 829Creating Functions 835Simplifying Code with Named Arguments 840Chapter 26: Understanding the Access Event Model 843Programming Events 844Identifying Common Events 847Paying Attention to Event Sequence 856Chapter 27: Debugging Your Access Applications 865Organizing VBA Code 866Testing Your Applications 867Traditional Debugging Techniques 873Using the Access Debugging Tools 879Trapping Errors in Your Code 893Part VII: Advanced Access Programming Techniques 899Chapter 28: Accessing Data with VBA 901Working with Data 901Understanding DAO Objects 904Understanding ADO Objects 917Writing VBA Code to Update a Table 923Chapter 29: Advanced Data Access with VBA 937Adding an Unbound Combo Box to a Form to Find Data 937Filtering a Form 945Chapter 30: Customizing the Ribbon 955The Ribbon Hierarchy 955Controls for Access Ribbons 956Editing the Default Ribbon 961Working with the Quick Access Toolbar 964Developing Custom Ribbons 966Creating a Custom Ribbon 969The Basic Ribbon XML 977Adding Ribbon Controls 979Attaching Ribbons to Forms and Reports 989Removing the Ribbon Completely 990Chapter 31: Preparing Your Access Application for Distribution 993Defining the Current Database Options 994Polishing Your Application 1009Bulletproofing an Application 1022Chapter 32: Integrating Access with SharePoint 1039Introducing SharePoint 1039Understanding SharePoint Sites 1040Sharing Data between Access and SharePoint 1043Using SharePoint Templates 1052Index 1055

Michael Alexander is a Microsoft Certified Application Developer (MCAD) and a Microsoft MVP with over 15 years of experience developing reporting solutions for a variety of industries. He is also the principal contributor at datapigtechnologies.com, where he shares video tutorials with the Microsoft Access and Excel communities. Mike has authored several books on advanced business analysis. Dick Kusleika is a 12-time Microsoft Excel MVP, and the principal contributor at the Daily Dose of Excel blog.

Select Guide Rating

• 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 9Chapter 2: Getting Started with Access 17The Access Welcome Screen 17How to Create a Blank Database 18The Access 2019 Interface 20The Ribbon 24The Quick Access toolbar 25Part II: Understanding Access Tables 29Chapter 3: Creating Access Tables 31Table Types 31Creating a New Table 32Creating tblCustomers 49Changing a Table Design 50Understanding tblCustomers Field Properties 68Setting the Primary Key 71Indexing Access Tables 73Printing a Table Design 79Saving the Completed Table 81Manipulating Tables 81Adding Records to a Database Table 84Understanding Attachment Fields 84Chapter 4: Understanding Table Relationships 87Building Bulletproof Databases 88Data Normalization and Denormalization 90Table Relationships 102Integrity Rules 110Keys 113Creating relationships and enforcing referential integrity 119Viewing all relationships 125Deleting relationships 126Following application-specific integrity rules 126Chapter 5: Working with Access Tables 129Understanding Datasheets 130Looking at the Datasheet Window 131Opening a Datasheet 135Entering New Data 135Navigating Records in a Datasheet 142Changing Values in a Datasheet 146Using the Undo Feature 148Copying and Pasting Values 148Replacing Values 149Adding New Records 150Deleting Records 150Displaying Records 152Sorting and Filtering Records in a Datasheet 160Aggregating Data 165Printing Records 166Previewing Records 167Chapter 6: Importing and Exporting Data 169How Access Works with External Data 169Options for Importing and Exporting 173Exporting to External Formats 202Chapter 7: Linking to External Data 209Linking External Data 210Working with Linked Tables 225Splitting a Database 229Part III: Working with Access Queries 237Chapter 8: Selecting Data with Queries 239Introducing Queries 240Creating a Query 241Working with Query Fields 247Adding Criteria to Your Queries 252Printing a Query’s Recordset 255Saving a Query 256Creating Multi-table Queries 256Working with the Table Pane 261Creating and Working with Query Joins 262Chapter 9: Using Operators and Expressions in Access 269Introducing Operators 269Using Operators and Expressions in Queries 283Entering Single-Value Field Criteria 289Using Multiple Criteria in a Query 297Entering Criteria in Multiple Fields 304Chapter 10: Going Beyond Select Queries 311Aggregate Queries 311Action Queries 320Crosstab Queries 336Optimizing Query Performance 348Part IV: Analyzing Data in Microsoft Access 353Chapter 11: Transforming Data in Access 355Finding and Removing Duplicate Records 355Common Transformation Tasks 363Chapter 12: Working with Calculations and Dates 379Using Calculations in Your Analyses 379Using Dates in Your Analyses 392Chapter 13: Performing Conditional Analyses 405Using Parameter Queries 405Using Conditional Functions 414The Switch function 420Comparing the IIf and Switch functions 421Chapter 14: The Fundamentals of Using SQL 425Understanding Basic SQL 425Getting Fancy with Advanced SQL Statements 430Showing only the SELECT TOP or SELECT TOP PERCENT 433Performing action queries via SQL statements 436Using SQL-Specific Queries 438Chapter 15: Subqueries and Domain Aggregate Functions 445Enhancing Your Analyses with Subqueries 446Domain Aggregate Functions 459Examining the syntax of domain aggregate functions 461Using domain aggregate functions 463Chapter 16: Running Descriptive Statistics in Access 469Basic Descriptive Statistics 470Advanced Descriptive Statistics 479Part V: Working with Access Forms and Reports 487Chapter 17: Creating Basic Access Forms 489Working with Form Views 489Understanding Different Types of Forms 490Working with Controls 503Introducing Properties 524Chapter 18: Working with Data on Access Forms 531Using Form View 532Changing Values in a Form 537Printing a Form 544Working with Form Properties 545Adding a Form Header or Footer 556Working with Section Properties 556Changing the Layout 558Converting a Form to a Report 562Chapter 19: Working with Form Controls 563Setting Control Properties 563Creating a Calculated Control 571Working with Subforms 572Form Design Tips 573Tackling Advanced Forms Techniques 576Using the Tab Control 583Using Dialog Boxes to Collect Information 586Designing a Form from Scratch 589Chapter 20: Presenting Data with Access Reports 599Introducing Reports 599Creating a Report, from Beginning to End 604Creating a report with the Report Wizard 605Banded Report Design Concepts 622Creating a Report from Scratch 628Improving the Report’s Appearance 651Chapter 21: Advanced Access Report Techniques 657Grouping and Sorting Data 658Formatting Data 670Adding Data 688Adding Even More Flexibility 690Part VI: Microsoft Access Programming Fundamentals 703Chapter 22: Using Access Macros 705An Introduction to Macros 705Understanding Macro Security 709Multi-action Macros 713Submacros 716Conditions 720Temporary Variables 723Error Handling and Macro Debugging 728Embedded Macros 734Macros versus VBA Statements 736Chapter 23: Using Access Data Macros 741Introducing Data Macros 742Understanding Table Events 742Using the Macro Builder for Data Macros 745Understanding the Action Catalog 747Creating Your First Data Macro 751Managing Macro Objects 755Recognizing the Limitations of Data Macros 757Chapter 24: Getting Started with Access VBA 759Introducing Visual Basic for Applications 760Understanding VBA Terminology 761Starting with VBA Code Basics 762Creating VBA Programs 763Modules and procedures 763Working in the code window 770Understanding VBA Branching Constructs 777Working with Objects and Collections 786Exploring the Visual Basic Editor 792Chapter 25: Mastering VBA Data Types and Procedures 801Using Variables 802Working with Data Types 809Understanding Subs and Functions 829Creating Functions 835Simplifying Code with Named Arguments 840Chapter 26: Understanding the Access Event Model 843Programming Events 844Identifying Common Events 847Paying Attention to Event Sequence 856Chapter 27: Debugging Your Access Applications 865Organizing VBA Code 866Testing Your Applications 867Traditional Debugging Techniques 873Using the Access Debugging Tools 879Trapping Errors in Your Code 893Part VII: Advanced Access Programming Techniques 899Chapter 28: Accessing Data with VBA 901Working with Data 901Understanding DAO Objects 904Understanding ADO Objects 917Writing VBA Code to Update a Table 923Chapter 29: Advanced Data Access with VBA 937Adding an Unbound Combo Box to a Form to Find Data 937Filtering a Form 945Chapter 30: Customizing the Ribbon 955The Ribbon Hierarchy 955Controls for Access Ribbons 956Editing the Default Ribbon 961Working with the Quick Access Toolbar 964Developing Custom Ribbons 966Creating a Custom Ribbon 969The Basic Ribbon XML 977Adding Ribbon Controls 979Attaching Ribbons to Forms and Reports 989Removing the Ribbon Completely 990Chapter 31: Preparing Your Access Application for Distribution 993Defining the Current Database Options 994Polishing Your Application 1009Bulletproofing an Application 1022Chapter 32: Integrating Access with SharePoint 1039Introducing SharePoint 1039Understanding SharePoint Sites 1040Sharing Data between Access and SharePoint 1043Using SharePoint Templates 1052Index 1055