Master database creation and management Access 2019 Bible is your, comprehensive reference to the world's most popular database management tool. With clear guidance toward everything from the basics to the advanced, this go-to reference helps you take advantage of everything Access 2019 has to offer. Whether you're new to Access or getting started with Access 2019, you'll find everything you need to know to create the database solution perfectly tailored to your needs, with expert guidance every step of the way. The companion website features all examples and databases used in the book, plus trial software and a special offer from Database Creations. Start from the beginning for a complete tutorial, or dip in and grab what you need when you need it. Access enables database novices and programmers to store, organize, view, analyze, and share data, as well as build powerful, integrable, custom database solutions — but databases can be complex, and difficult to navigate. This book helps you harness the power of the database with a solid understanding of their purpose, construction, and application. Understand database objects and design systems objects Build forms, create tables, manipulate datasheets, and add data validation Use Visual Basic automation and XML Data Access Page design Exchange data with other Office applications, including Word, Excel, and more From database fundamentals and terminology to XML and Web services, this book has everything you need to maximize Access 2019 and build the database you need.
Introduction xxxix Part I: Access Building Blocks 1 Chapter 1: An Introduction to Database Development 3 The Database Terminology of Access 3 Relational Databases 6 Access Database Objects 7 A Five-Step Design Method 9 Chapter 2: Getting Started with Access 17 The Access Welcome Screen 17 How to Create a Blank Database 18 The Access 2019 Interface 20 The Ribbon 24 The Quick Access toolbar 25 Part II: Understanding Access Tables 29 Chapter 3: Creating Access Tables 31 Table Types 31 Creating a New Table 32 Creating tblCustomers 49 Changing a Table Design 50 Understanding tblCustomers Field Properties 68 Setting the Primary Key 71 Indexing Access Tables 73 Printing a Table Design 79 Saving the Completed Table 81 Manipulating Tables 81 Adding Records to a Database Table 84 Understanding Attachment Fields 84 Chapter 4: Understanding Table Relationships 87 Building Bulletproof Databases 88 Data Normalization and Denormalization 90 Table Relationships 102 Integrity Rules 110 Keys 113 Creating relationships and enforcing referential integrity 119 Viewing all relationships 125 Deleting relationships 126 Following application-speci?c integrity rules 126 Chapter 5: Working with Access Tables 129 Understanding Datasheets 130 Looking at the Datasheet Window 131 Opening a Datasheet 135 Entering New Data 135 Navigating Records in a Datasheet 142 Changing Values in a Datasheet 146 Using the Undo Feature 148 Copying and Pasting Values 148 Replacing Values 149 Adding New Records 150 Deleting Records 150 Displaying Records 152 Sorting and Filtering Records in a Datasheet 160 Aggregating Data 165 Printing Records 166 Previewing Records 167 Chapter 6: Importing and Exporting Data 169 How Access Works with External Data 169 Options for Importing and Exporting 173 Exporting to External Formats 202 Chapter 7: Linking to External Data 209 Linking External Data 210 Working with Linked Tables 225 Splitting a Database 229 Part III: Working with Access Queries 237 Chapter 8: Selecting Data with Queries 239 Introducing Queries 240 Creating a Query 241 Working with Query Fields 247 Adding Criteria to Your Queries 252 Printing a Query’s Recordset 255 Saving a Query 256 Creating Multi-table Queries 256 Working with the Table Pane 261 Creating and Working with Query Joins 262 Chapter 9: Using Operators and Expressions in Access 269 Introducing Operators 269 Using Operators and Expressions in Queries 283 Entering Single-Value Field Criteria 289 Using Multiple Criteria in a Query 297 Entering Criteria in Multiple Fields 304 Chapter 10: Going Beyond Select Queries 311 Aggregate Queries 311 Action Queries 320 Crosstab Queries 336 Optimizing Query Performance 348 Part IV: Analyzing Data in Microsoft Access 353 Chapter 11: Transforming Data in Access 355 Finding and Removing Duplicate Records 355 Common Transformation Tasks 363 Chapter 12: Working with Calculations and Dates 379 Using Calculations in Your Analyses 379 Using Dates in Your Analyses 392 Chapter 13: Performing Conditional Analyses 405 Using Parameter Queries 405 Using Conditional Functions 414 The Switch function 420 Comparing the IIf and Switch functions 421 Chapter 14: The Fundamentals of Using SQL 425 Understanding Basic SQL 425 Getting Fancy with Advanced SQL Statements 430 Showing only the SELECT TOP or SELECT TOP PERCENT 433 Performing action queries via SQL statements 436 Using SQL-Speci?c Queries 438 Chapter 15: Subqueries and Domain Aggregate Functions 445 Enhancing Your Analyses with Subqueries 446 Domain Aggregate Functions 459 Examining the syntax of domain aggregate functions 461 Using domain aggregate functions 463 Chapter 16: Running Descriptive Statistics in Access 469 Basic Descriptive Statistics 470 Advanced Descriptive Statistics 479 Part V: Working with Access Forms and Reports 487 Chapter 17: Creating Basic Access Forms 489 Working with Form Views 489 Understanding Different Types of Forms 490 Working with Controls 503 Introducing Properties 524 Chapter 18: Working with Data on Access Forms 531 Using Form View 532 Changing Values in a Form 537 Printing a Form 544 Working with Form Properties 545 Adding a Form Header or Footer 556 Working with Section Properties 556 Changing the Layout 558 Converting a Form to a Report 562 Chapter 19: Working with Form Controls 563 Setting Control Properties 563 Creating a Calculated Control 571 Working with Subforms 572 Form Design Tips 573 Tackling Advanced Forms Techniques 576 Using the Tab Control 583 Using Dialog Boxes to Collect Information 586 Designing a Form from Scratch 589 Chapter 20: Presenting Data with Access Reports 599 Introducing Reports 599 Creating a Report, from Beginning to End 604 Creating a report with the Report Wizard 605 Banded Report Design Concepts 622 Creating a Report from Scratch 628 Improving the Report’s Appearance 651 Chapter 21: Advanced Access Report Techniques 657 Grouping and Sorting Data 658 Formatting Data 670 Adding Data 688 Adding Even More Flexibility 690 Part VI: Microsoft Access Programming Fundamentals 703 Chapter 22: Using Access Macros 705 An Introduction to Macros 705 Understanding Macro Security 709 Multi-action Macros 713 Submacros 716 Conditions 720 Temporary Variables 723 Error Handling and Macro Debugging 728 Embedded Macros 734 Macros versus VBA Statements 736 Chapter 23: Using Access Data Macros 741 Introducing Data Macros 742 Understanding Table Events 742 Using the Macro Builder for Data Macros 745 Understanding the Action Catalog 747 Creating Your First Data Macro 751 Managing Macro Objects 755 Recognizing the Limitations of Data Macros 757 Chapter 24: Getting Started with Access VBA 759 Introducing Visual Basic for Applications 760 Understanding VBA Terminology 761 Starting with VBA Code Basics 762 Creating VBA Programs 763 Modules and procedures 763 Working in the code window 770 Understanding VBA Branching Constructs 777 Working with Objects and Collections 786 Exploring the Visual Basic Editor 792 Chapter 25: Mastering VBA Data Types and Procedures 801 Using Variables 802 Working with Data Types 809 Understanding Subs and Functions 829 Creating Functions 835 Simplifying Code with Named Arguments 840 Chapter 26: Understanding the Access Event Model 843 Programming Events 844 Identifying Common Events 847 Paying Attention to Event Sequence 856 Chapter 27: Debugging Your Access Applications 865 Organizing VBA Code 866 Testing Your Applications 867 Traditional Debugging Techniques 873 Using the Access Debugging Tools 879 Trapping Errors in Your Code 893 Part VII: Advanced Access Programming Techniques 899 Chapter 28: Accessing Data with VBA 901 Working with Data 901 Understanding DAO Objects 904 Understanding ADO Objects 917 Writing VBA Code to Update a Table 923 Chapter 29: Advanced Data Access with VBA 937 Adding an Unbound Combo Box to a Form to Find Data 937 Filtering a Form 945 Chapter 30: Customizing the Ribbon 955 The Ribbon Hierarchy 955 Controls for Access Ribbons 956 Editing the Default Ribbon 961 Working with the Quick Access Toolbar 964 Developing Custom Ribbons 966 Creating a Custom Ribbon 969 The Basic Ribbon XML 977 Adding Ribbon Controls 979 Attaching Ribbons to Forms and Reports 989 Removing the Ribbon Completely 990 Chapter 31: Preparing Your Access Application for Distribution 993 De?ning the Current Database Options 994 Polishing Your Application 1009 Bulletproo?ng an Application 1022 Chapter 32: Integrating Access with SharePoint 1039 Introducing SharePoint 1039 Understanding SharePoint Sites 1040 Sharing Data between Access and SharePoint 1043 Using SharePoint Templates 1052 Index 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.
The definitive guide to Access database creation and management Learn the essentials of using Microsoft Access to store data, analyze information, build reports, create applications and much more! With Microsoft®Access® 2019 Bible, you'll gain a solid understanding of how Access works from the ground up. Starting with basic database concepts, you'll work your way through detailed instruction on building effective tables, creating powerful analytical queries, creating robust applications with Access forms, and building attractive Access reports. With additional topics covering SharePoint integration and Visual Basic programming, Microsoft® Access® 2019 Bible is an invaluable resource for users of all skill levels. Get a solid grounding in database fundamentals Use Access to analyze large amounts of data Turn your data into attractive PDF-style reports and outputs Automate redundant reporting and analysis using Access Macros Build powerful departmental applications with Access Forms Save time and increase productivity with Access VBA Integrate or publish your data on SharePoint sites Follow along with downloadable examples and database files from the book Check out trial software, demos, and other bonus material on the book's site