Introduction

Welcome to Access 2019 Bible, your personal guide to the most powerful desktop database management system available today.

If you've picked up this book, you've probably already recognized that Microsoft Access can help you manage your data in ways that no other application can. Even the king of applications, Microsoft Excel, can't do what Access can. Now, it may seem silly to compare Access (a database management application) with Excel (a spreadsheet application), but there is no doubt that Excel is being used every day to manage and analyze large amounts of data in all kinds of organizations. Indeed, you may be opening this book because you need to get past the limitations of Excel.

Access is an excellent (many would say logical) next step for the analyst who faces an ever-increasing data pool. Access takes very few performance hits with larger data sets. It has no predetermined row limitations. And it can effectively manage the relationships between disparate data tables. In addition, Access comes with tools that help you build your own distributable applications.

Today, when we have more data than ever and more demands for complex data analysis, power analysts need to add some tools to their repertoire in order to get away from being simply "spreadsheet mechanics." That's why this book is such an important step in the evolution of your skillset. Throughout this book, not only will you get an introduction to Access, but you'll also learn various ways in which you can use Access to improve your daily data management and analysis.

Is This Book for You?

This book contains everything you need in order to learn Access 2019 to a mid-advanced level. The book starts off with database basics and builds, chapter by chapter.

This book is designed to enhance the skillset of users at all levels (beginning, intermediate, and even advanced users of Access). Start at the beginning if you're new to Access. If you're already familiar with Access and comfortable building Access applications, you may want to start with the later parts of this book.

If you're new to the world of database management, this book has everything you need to get started with Access 2019. It also offers advanced topics for reference and learning. Beginning developers should pay particular attention to Part I, where we cover the essential skills necessary for building successful and efficient databases. Your ability as a database designer is constantly judged by how well the applications you build perform and how well they handle data entrusted to them by their users.

If you want to learn the basics of Visual Basic for Applications (VBA) programming, you'll find what you need in this book. Although the topic of VBA is a rich one that deserves its own book, this book offers a robust set of chapters that will get you started leveraging VBA to enhance your Access databases. Part VI of this book explains the nuts and bolts-with a lot of gritty technical details-of writing VBA procedures and building Access applications around the code you add to your databases.

Conventions Used in This Book

We use the following conventions in this book:

• When you're instructed to press a key combination (press and hold down one key while pressing another key), the key combination is separated by a plus sign. For example, Ctrl+Esc indicates that you must hold down the Ctrl key and press the Esc key; then release both keys.
• Point the mouse refers to moving the mouse so that the mouse pointer is on a specific item. Click refers to pressing the left mouse button once and releasing it. Double-click refers to pressing the left mouse button twice in rapid succession and then releasing it. Right-click refers to pressing the right mouse button once and releasing it. Drag refers to pressing and holding down the left mouse button while moving the mouse.
• We use italics for new terms and for emphasis.
• We use bold for material that you need to type directly into a field in Access.

How This Book Is Organized

This book is divided into nine parts:

• Part I: Access Building Blocks: Part I provides a solid understanding of the basic elements of databases, introduces you to the keywords of database management, and teaches you how to plan tables and work with Access data types. In this part, you'll also get your first look into Access and the Access interface.
• Part II: Understanding Access Tables: In Part II, you get the skills you need to build Access tables, manage relationships between tables, and link to disparate data sources like Excel files, text files, SQL Server, and other Access databases.
• Part III: Working with Access Queries: Part III introduces you to some of the basic analytical tools available in Access. Here, you'll explore the Query Builder, as well as techniques to create both simple and advanced analytical outputs from your Access tables. We cover query basics, aggregate queries, action queries, and crosstab queries.
• Part IV: Analyzing Data in Microsoft Access: Part IV demonstrates many of the advanced techniques that truly bring data analysis to the next level. Here, you'll explore how to transform your data via queries, create custom calculations, perform conditional analysis, build powerful subqueries, and apply statistical analysis to your queries.
• Part V: Working with Access Forms and Reports: Part V focuses on building

  forms and reports using Access. Here, we cover the basics of turning data into slick-looking user interfaces and PDF-style Access reports. You'll also explore how to enhance the look and feel of your Access applications via advanced form controls.

• Part VI: Microsoft Access Programming Fundamentals: In Part VI, you'll take the next step and dive into programming. The chapters in this part start you with Access macros, take you into VBA fundamentals, and eventually work up to leveraging VBA to improve your Access database. This part helps you understand the complex object and event models that drive Access applications and how to construct the VBA code necessary to take advantage of this rich programming environment.
• Part VII: Advanced Access Programming Techniques: Part VII turns your attention to automation and integration, showing you how your reporting mechanisms can be enhanced by leveraging other programs and platforms. In these chapters, you'll not only learn the fundamental skills required to become more proficient in VBA, but you'll also discover many insider tricks to apply to your Access application development projects. You'll also discover the extended Microsoft SharePoint integration capabilities in Access that allow you to publish Access tables, forms, and reports on SharePoint sites.

How to Use This Book

Although each chapter is an integral part of the book as a whole, each chapter can also stand on its own and has its own example files, available on the book's website. This book is not necessarily meant to be read cover to cover. Instead, it's a reference book that you can consult when

• You're stuck while trying to do something.
• You need to do something that you've never done before.
• You have some time on your hands, and you're interested in learning something new about Access.

In short, you can read the book in any order you want, skipping from chapter to chapter and from topic to topic. This book's index is particularly thorough; you can refer to the index to find the location of a particular topic you're interested in.

What's on the Website

The examples demonstrated throughout this book can be found on this book's website. The URL is www.wiley.com/go/access2019bible.

Getting Additional Help with Access

As you experiment with the new functions and tools you learn here in this book, you may sometimes need an extra push in the right direction. The first place you should look is Access's Help system. The Help system in Access isn't perfect. To a new user, the Help system may seem like a clunky add-in that returns a perplexing list of topics that has nothing to do with the original topic being searched. The truth is, however, once you learn how to use the Access Help system effectively, it's often the fastest and easiest way to get extra help on a topic.

Following are some tips that will help you get the most out of Access's Help system:

• Location matters when asking for help. You may remember the Help system in older versions of Access being a lot more user-friendly and more effective than newer versions of Access. Well, rest assured that you aren't just imagining it. The fact is, Microsoft fundamentally changed the mechanics of the Access Help system.

  In Access 2019, there are actually two Help systems: one providing help on Access features and another providing help on VBA programming topics. Instead of doing a global search with your criteria, Access throws your search criteria only against the Help system that is relevant to your current location. This essentially means that the help you get is determined by the area of Access in which you're working. So, if you need...