Access® 2019 For Dummies®
Published by: John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030-5774, www.wiley.com
Copyright © 2019 by John Wiley & Sons, Inc., Hoboken, New Jersey
Published simultaneously in Canada
No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without the prior written permission of the Publisher. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, (201) 748-6011, fax (201) 748-6008, or online at http://www.wiley.com/go/permissions.
Trademarks: Wiley, For Dummies, the Dummies Man logo, Dummies.com, Making Everything Easier, and related trade dress are trademarks or registered trademarks of John Wiley & Sons, Inc. and may not be used without written permission. Microsoft and Access are registered trademarks of Microsoft Corporation. All other trademarks are the property of their respective owners. John Wiley & Sons, Inc. is not associated with any product or vendor mentioned in this book.
LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: THE PUBLISHER AND THE AUTHOR MAKE NO REPRESENTATIONS OR WARRANTIES WITH RESPECT TO THE ACCURACY OR COMPLETENESS OF THE CONTENTS OF THIS WORK AND SPECIFICALLY DISCLAIM ALL WARRANTIES, INCLUDING WITHOUT LIMITATION WARRANTIES OF FITNESS FOR A PARTICULAR PURPOSE. NO WARRANTY MAY BE CREATED OR EXTENDED BY SALES OR PROMOTIONAL MATERIALS. THE ADVICE AND STRATEGIES CONTAINED HEREIN MAY NOT BE SUITABLE FOR EVERY SITUATION. THIS WORK IS SOLD WITH THE UNDERSTANDING THAT THE PUBLISHER IS NOT ENGAGED IN RENDERING LEGAL, ACCOUNTING, OR OTHER PROFESSIONAL SERVICES. IF PROFESSIONAL ASSISTANCE IS REQUIRED, THE SERVICES OF A COMPETENT PROFESSIONAL PERSON SHOULD BE SOUGHT. NEITHER THE PUBLISHER NOR THE AUTHOR SHALL BE LIABLE FOR DAMAGES ARISING HEREFROM. THE FACT THAT AN ORGANIZATION OR WEBSITE IS REFERRED TO IN THIS WORK AS A CITATION AND/OR A POTENTIAL SOURCE OF FURTHER INFORMATION DOES NOT MEAN THAT THE AUTHOR OR THE PUBLISHER ENDORSES THE INFORMATION THE ORGANIZATION OR WEBSITE MAY PROVIDE OR RECOMMENDATIONS IT MAY MAKE. FURTHER, READERS SHOULD BE AWARE THAT INTERNET WEBSITES LISTED IN THIS WORK MAY HAVE CHANGED OR DISAPPEARED BETWEEN WHEN THIS WORK WAS WRITTEN AND WHEN IT IS READ.
For general information on our other products and services, please contact our Customer Care Department within the U.S. at 877-762-2974, outside the U.S. at 317-572-3993, or fax 317-572-4002. For technical support, please visit https://hub.wiley.com/community/support/dummies.
Wiley publishes in a variety of print and electronic formats and by print-on-demand. Some material included with standard print versions of this book may not be included in e-books or in print-on-demand. If this book refers to media such as a CD or DVD that is not included in the version you purchased, you may download this material at http://booksupport.wiley.com. For more information about Wiley products, visit www.wiley.com.
Library of Congress Control Number: 2018954128
ISBN: 978-1-119-51326-1
ISBN: 978-1-119-51325-4 (ePDF); ISBN: 978-1-119-51328-5 (ePub)
Welcome! Thank you for selecting this book. We assume you’ve done so because you’re hoping it will explain how to use Microsoft Access 2019, and of course, as the authors, we believe this was a wise decision. We, the authors, base this belief on the fact that both of us have been teaching and using Access for a very long time, and we know how to share what we know with our students.
So what was it that made you seek out a book on Access? It might be that you’ve been asked to use it at work, or perhaps you run your own business or are managing a nonprofit organization. If any of these is the case — or if you’re just a regular human with a lot of personal contacts and irons in the fire, you need Access to organize your data. You need it so you can find a name or a transaction in seconds after a few keystrokes, not after minutes spent leafing through your files or swiping apps this way and that on your smartphone. You need it so you can produce reports that make you look like the genius you are. You need it so you can create cool forms that will help your staff enter all the data you’ve got stacked on their desks — and in a way that lets you know the data was entered properly so that it’s accurate and useful. You need Access so you can find little bits of data out of the huge pool of information you need to store. So that’s it. You just need it.
With all the power that Access has (and that it therefore gives you), there comes a small price: complexity. Access isn’t one of those applications you can just sit down and use “right out of the box.” It’s not scarily difficult or anything, but there’s a lot going on — and you need some guidance, some help, and some direction to really use it and make it bend to your will. And that’s where this book — a “reference for the rest of us” — comes in.
So you’ve picked up this book. Hang on to it. Clutch it to your chest and run gleefully from the store or click the Add to Shopping Cart button and sit back with an expression of satisfaction and accomplishment on your face, because you’ve done a smart thing (if we don’t say so ourselves). When you get home, or when the book arrives in person (or when you download it to your handheld device), start reading — whether you begin with Chapter 1 or whether you dive in and start with a particular feature or area of interest that’s been giving you fits. Just read, and then go put Access to work for you.
You need to know only a few things about your computer and Windows to get the most out of Access 2019 For Dummies. In the following pages, we presume that you:
want to build your own databases
and/or
Have Windows 10
If your computer uses a version of Windows prior to Windows 10, you can’t run Access 2019.
When something in this book is particularly valuable, we go out of our way to make sure that it stands out. We use these cool icons to mark text that (for one reason or another) really needs your attention. Here’s a quick preview of the ones waiting for you in this book and what they mean.
Tips are incredibly helpful words of wisdom that promise to save you time, energy, and the embarrassment of being caught swearing out loud while you think you’re alone. Whenever you see a Tip, take a second to check it out.
Some things are too important to forget, so the Remember icon points them out. These items are critical steps in a process — points that you don’t want to miss.
Sometimes we give in to the techno-geek lurking inside us and slip some technical babble into the book. The Technical Stuff icon protects you from obscure details by making them easy to avoid. On the other hand, you may find them interesting. (Your inner techno-geek will rejoice.)
The Warning icon says it all: Skipping this information may be hazardous to your data’s health. Pay attention to these icons and follow their instructions to keep your databases happy and intact.
In addition to the content in this book, you’ll find some extra content available at the www.dummies.com website:
www.dummies.com/cheatsheet/access2019.www.dummies.com/go/access2019. Here you will find sample files used in Chapters 9 and 17 as well as the Lancaster Food Pantry Access database used throughout the book.www.dummies.com/go/access2019fd.Now nothing’s left to hold you back from the thrills, chills, and power of Access. Hold on tight to your copy of Access 2019 For Dummies and leap into Access. Not sure where to start? See if you spot yourself in these scenarios:
Part 1
Chapter 1
IN THIS CHAPTER
Deciding when to use Access
Discovering what’s new in Access 2019
Unlocking the basics of working with Access
Figuring out how to get started
Access 2019, the latest version of the Microsoft Office database application, has always been a powerful program, and this version is no different. Chances are, you’re reading this book because all that power makes Access an application that’s not so easy to learn on your own. If you’re hoping to unleash that power for your data, you’ll need us. So, good decision to buy this book!
Now, all that power and the need for our book aside, with the very basic parts of Access, the basic functionality that you’ll discover in this book, you’ll be able to put Access through many of its most important paces, yet you’ll be working with wizards and other onscreen tools that keep you at a comfortable arm’s distance from the software’s inner workings, the things that programmers and serious developers play with. There. Don’t you feel better now?
You don’t have to use every feature and tool and push the edges of the Access envelope. In fact, you can use very little of everything Access has to offer and still create quite a significant solution to your needs for storing and accessing data — all because Access can really “do it all” — enabling you to set up a database quickly, build records into that database, and then use that data in several useful ways. Later on, who knows? You may become an Access guru.
In this chapter, you’ll discover what Access does best (and when you might want to use another tool instead), and you’ll get a look at what’s new and improved in Access 2019 (compared to Access 2016). You’ll see how it does what it does, and hopefully you’ll begin to understand and absorb some basic terminology.
Now, don’t panic; nobody’s expecting you to memorize tons of complex vocabulary or anything scary like that. The goal here (and in the next two chapters) with regard to terms is to introduce you to some basic words and general concepts intended to help you make better use of Access — as well as better understand later chapters in this book, if you choose to follow us all the way to its stunning, life-altering conclusion.
What is Access good for? That’s a good question. Well, the list of what you can do with it is a lot longer than the list of what you can’t do with it — of course, especially if you leave things like “wash your car” and “put away the dishes” off the “can’t do” list. When it comes to data organization, storage, and retrieval, Access is at the head of the class.
Okay, what do I mean by big database? Any database with a lot of records — and by a lot, I mean hundreds. At least. And certainly if you have thousands of records, you need a tool like Access to manage them. Although you can use Microsoft Excel to store lists of records, it limits how many you can store (no more than the number of rows in a single worksheet). In addition, you can’t use Excel to set up anything beyond a simple list that can be sorted and filtered. So anything with a lot of records and complex data is best done in Access.
Some reasons why Access handles big databases well are:
Whether your database holds 100 records or 100,000 records (or more), if you need to keep separate tables and relate them for maximum use of the information, you need a relational database — and that’s Access. How do you know whether your data needs to be in separate tables? Think about your data — is it very compartmentalized? Does it go off on tangents? Consider the following example and apply the concepts to your data and see if you need multiple tables for your database.
Imagine you work for a very large company, and the company has data pertaining to their customers and their orders, the products the company sells, its suppliers, and its employees. For a complex database like this one, you need multiple tables, as follows:
Other tables exist, too — to keep a list of shipping companies and their contact information (for shipping customer orders), an expense table (for the expenses incurred in running the business), and other tables that are used with the main four tables. The need for and ways to use the main tables and these additional tables are covered later in this book, as you find out how to set up tools for data entry, look up records, and create reports that provide varying levels of detail on all the data you’ve stored.
Because you don’t have to fill in every field for each record — in any table in the database — if you don’t have a phone number or don’t know an email address, for example, it’s okay to leave those fields blank until you’ve obtained that information.
If you think carefully about your database, how you use your data, and what you need to know about your employees, customers, volunteers, donors, products, or projects — whatever you’re storing information about — you can plan:
Of course, everyone forgets something, and plans change after a system has already been implemented. But don’t worry — Access isn’t so rigid that chaos will ensue if you begin building your tables and forget something (a field or two, an entire table). You can always add a field that you forgot (or that some bright spark just told you is needed) or add a new table after the fact. But planning ahead as thoroughly as possible is still essential.
As part of thorough planning, sketch your planned database on paper, drawing a kind of flowchart with boxes for each table and lists of fields that you’ll have in each one. Draw arrows to show how they might be related — it’s sort of like drawing a simple family tree — and you’re well on your way to a well-planned, useful database.
Here’s a handy procedure to follow if you’re new to the process of planning a database:
Now, based on that information, create a new list of the actual details you could store:
List every piece of information you can possibly think of about your customers, products, ideas, cases, books, works of art, students — whatever your database pertains to. Don’t be afraid to go overboard — you can always skip some of the items in the list if they don’t turn out to be things you really need to know (or can possibly find out) about each item in your database.
Take the list of fields — that’s what all those pieces of information are — and start breaking them up into logical groups.
How? Think about the fields and how they work together:
With a big list of fields and some tentative groupings of those fields at the ready, and with an idea of which field is unique for each record, you can begin figuring out how to use the data.
With this planning done, you’re ready to start building your database. The particulars of that process come later in this chapter and in subsequent chapters, so don’t jump in yet. Do pat yourself on the back, though, because if you’ve read this procedure and applied even some of it to your potential database, you’re way ahead of the game, and we’re confident you’ll make good use of all that Access has to offer.
When you’re planning your database, consider how the data will be entered:
If you’ll be doing the data entry yourself, perhaps you’re comfortable working in a spreadsheet-like environment (known in Access as Datasheet view), where the table is a big grid. You fill it in row by row, and each row is a record.
Figure 1-1 shows a table of volunteers in progress in Datasheet view. You decide: Is it easy to use, or can you picture yourself forgetting to move down a row and entering the wrong stuff in the wrong columns as you enter each record? As you can see, there are more fields than show in the window, so you’d be doing a lot of scrolling to the left and right to use this view.
FIGURE 1-1: Datasheet view can be an easy environment for data entry. Or not.
FIGURE 1-2: Here’s a simple form for entering new records or reviewing existing ones.
The mind-numbing effect (and inherent increased margin for error) is especially likely when you have lots of fields in a database, and the user, if working in Datasheet view, has to move horizontally through the fields. A form like the one in Figure 1-2 puts the fields in a more pleasing format, making it easier to enter data into the fields and to see all the fields simultaneously (or only those you want data entered into).
You find out all about forms in Chapter 8. If your database is large enough that you require help doing the data entry, or if it’s going to grow over time, making an ongoing data-entry process likely, Access is the tool for you. The fact that it offers simple forms of data entry/editing is reason enough to make it your database application of choice.
Yet another reason to use Access is the ability it gives you to create customized reports quickly and easily. Some database programs, especially those designed for single-table databases (known as flat-file databases), have some canned reports built in, and that’s all you can do — just select a report from the list and run the same report that every other user of that software runs.
If you’re an Excel user, your reporting capabilities are far from easy or simple, and they’re not designed for use with large databases — they’re meant for spreadsheets and small, one-table lists. Furthermore, you have to dig much deeper into Excel’s tools to get at these reports. Access, on the other hand, is a database application, so reporting is a major, up-front feature.
An example? In Excel, to get a report that groups your data by one or more of the fields in your list, you have to sort the rows in the worksheet first, using the field(s) to sort the data, and then you can create what’s known as a subtotal report. To create it, you use a dialog box that asks you about calculations you want to perform, where to place the results, and whether you’re basing a sort and/or a subtotal on more than one field. The resulting report is not designed for printing, and you have to tinker with your spreadsheet pagination (through a specialized view of the spreadsheet) to control how the report prints out.
In Access? Just fire up the Report Wizard, and you can sort your data, choose how to group it, decide which pieces of data to include in the report, and pick a visual layout and color scheme, all in one simple, streamlined process. Without you doing anything, the report is ready for printing. Access is built for reporting — after all, it is a database application — and reports are one of the most (if not the most) important ways you’ll use and share your data.
Because reports are such an important part of Access, you can not only create them with minimum fuss but also customize them to create powerful documentation of your most important data:
FIGURE 1-3: Ah, simplicity. A quick report is just one click away.
FIGURE 1-4: The Report Wizard creates more elaborate (but simple) reports, like this one.
FIGURE 1-5: Design view might look a little intimidating, but to really customize things, you’ll need it — and you might even enjoy it!
So, you can create any kind of custom report in Access, using any or all of your database tables and any of the fields from those tables, and you can group fields and place them in any order you want:
If all of this sounds exciting, or at least interesting, then you’re really on the right track with Access. The need to create custom reports is a major reason to use Access; you can find out about all these reporting options in Chapters 18 through 21. That’s right: This chapter you’re reading plus three more — that’s four whole chapters — are devoted to reporting. It must be a big feature in Access!
For users of Access 2007, 2010, 2013, or 2016, Access 2019 won’t seem like a big deal. Of course, if you’re coming from 2003, the biggest changes are found in the interface. Gone are the familiar menus and toolbars of 2003 and prior versions, now replaced by a Ribbon bar divided into tabs that take you to different versions of those old standbys. It’s a big change, and it takes some getting used to. In this book, however, we’re going to assume you already got your feet wet with 2007, 2010, 2013, or 2016, and aren’t thrown by the interface anymore.
What the heck is SharePoint? Even if your company isn’t using it yet, you’ve no doubt been seeing the product name and hearing how it provides the ability to see and use your Access data from anywhere — using desktop applications, a web browser, or even your phone. And in truth, it’s Microsoft’s software product that does all that and more, helping you manage your documents and collaborate with coworkers via the company network. Simply click the Save Database As command in the Access File tab’s panel (see Figure 1-6), and you’re on your way to publishing your database to SharePoint, which means you can access it from pretty much everywhere, including that beach in Maui. Of course, if you or your company doesn’t have a SharePoint server, you won’t be able to make use of this, and you don’t need to concern yourself with this section.
FIGURE 1-6: The Save As command offers choices for … you guessed it … saving your database.
As shown in Figure 1-6, the Save As options include regular old Save Database As, to save your existing database with a new name or in some format other than as an Access database; and Save Object As, to save a table, form, query, or report with a new name. You can also choose from several Advanced options to save the database as a package (to distribute your Access applications) or as an executable file (a single file that when run by the recipient, opens a database application), to back up the database, and to use the aforementioned SharePoint.
When you look at all the applications in Microsoft Office — Word, Excel, PowerPoint, Outlook, and of course, Access — you’ll see some features that are consistent throughout the suite. There are big differences, too, and that’s where books like this one come in handy, helping you deal with what’s different and not terribly obvious to a new user.
Access has several features in common with the rest of the applications in the Microsoft Office suite. You’ll find the same buttons on several of the tabs, and the Quick Access Toolbar (demonstrated in Chapter 2) appears in all the applications.
If you already know how to open, save, and print in, say, Word, you’re probably ready to do the same things in Access without any difficulty.
To make sure you’re totally Access-ready, here’s a look at the basic procedures that can give you a solid foundation on which to build.
Access opens in any one of several ways. So, like a restaurant with a very comprehensive menu, some people will love all the choices, and others will say, “I can’t decide! There are just too many options to choose from!”
Now, you’ll run into situations in which one of the ways is the glaringly best choice — hands down, and that one will be the way to go. But what if you’ve never heard of it? You’ll be trying to find my phone number (I’m unlisted — ha!) so you can give me a piece of your mind. So to acquaint you with all your choices (so you’ll be ready for any situation), here are all the ways you can open Access:
Windows 8.1 users can utilize any of several methods to start an application — click the lower left corner of the screen to display the Start icon, press the Windows key on the keyboard, or if you have a touchscreen, tap the Start button. Once the Start screen appears, tap the Access application tile.
If you’ve recently used Access, you’ll see it in the list on the left side of the Start menu. Just choose Start ⇒ Microsoft Access 2019, and Access opens.
Double-click any existing Access database file on your desktop or in a folder (as shown in Figure 1-7). Access opens automatically.
Good news: Access 2019 will open database files you created with previous versions of Access, and should support whatever features are employed within those database files. All your tables should open properly, and reports, forms, and queries should all work fine, too.
FIGURE 1-7: Double-click an Access database file, and Access opens right up.
So Access is open, and (assuming you opened it from the Start menu or from the Quick Launch portion of the taskbar) you’re staring at the Access interface. You may see features whose purposes elude you or that you don’t yet know how to use. Hey, don’t worry; that’s why you’re reading this book!
You can find out more about all the tabs and buttons, panels and menus, and all that fun stuff in Chapter 2. For now, just look at the ways Access offers you to get started with your database, be it an existing one that needs work or a new one you have all planned out and ready to go.
Well, this is the easy one. If a database already exists, you can open it by clicking the File tab (at the upper left of the workspace) and choosing Open from the list of commands that appears. As shown in Figure 1-8, a panel opens, displaying the types of files you can open (just to the right of the long red File panel) and the databases you’ve most recently used. Click the word Recent in the list to the near left and then click the database in the Recent list, and it opens, listing its current tables, queries, reports, and forms on the far left side of the window.
FIGURE 1-8: Pick your recently used database from the Recent list on the right.
When the database opens, you can open and view its various parts just by double-clicking them in that leftmost panel; whatever you open appears in the main, central part of the window. Figure 1-9 shows an example: a table, ready for editing.
FIGURE 1-9: An existing table, ready for more records.
After you open a table, you can begin entering or editing records. You can read more about how that’s done in Chapter 6, which demonstrates the different ways to edit your data and tweak your tables’ setups. If you want to tinker with any existing queries, you can open these, too, just by clicking them in the list on the left side of the workspace. (For more information on queries, check out Chapters 12 and 13. You can do simple sorting and look for particular records with the skills you pick up in Chapters 10 and 12.)
So you don’t have a database to open, eh? Well, don’t let that stop you. To start a new one, all you have to do is open Access, using any of the techniques listed earlier in this chapter (except the one that starts Access by opening an existing database file, which you don’t have yet).
A database file holds all your database components. Everything associated with the data is part of the database, including:
After Access is open, click the New command in the File tab (if that’s not already the active command). From the resulting display, you can click the Blank Desktop Database button (shown in Figure 1-10) to get started.
FIGURE 1-10: Click the Blank Desktop Database button in the New group.
Next, give your database a name (see the dialog box that appears in Figure 1-11), and click the Create button.
FIGURE 1-11: Name your database something that replaces the generic DatabaseX.accdb.
The X in Figure 1-11's caption represents a number — Access assigns consecutive numbers to the default names. Figure 1-11 shows a 1 added to the filename.
If this is your absolute first database in a fresh installation of Access, the filename offered in this panel will be Database1. Note that you don’t need to type a file extension here; Access will add the correct one for you.
What is that little yellow folder in the dialog box where you named your new database? It allows you to choose a folder (other than the default Documents folder in Windows 8/8.1) into which you can save your database. Click the folder icon after typing a name for your database, and then use the resulting File New Database dialog box to choose a location — an existing subfolder within Documents, the desktop, a network drive (if you’re on a network, say at your office), or your Office 365 OneDrive. The dialog box looks very familiar to anyone who’s used any Windows application, so this won’t be new territory for you.
At this point, with your new database open, you can begin entering records into your first table or begin naming your fields and setting them up. The field names go in the topmost row (the ID field is already created, by default in the new table), and the label Click to Add is atop the column with the active cell. If you choose to save your table now (right-click the Table1 tab and choose Save), you can name your table something more useful than Table1.
Access provides templates (prepared files that work sort of like database cookie cutters) for your new database needs. You’ll find a set of template icons in the same New panel where we just chose a Blank Desktop Database. As shown in Figure 1-12, you can choose a template category by clicking any of the words under the Search for Online Templates search box, and search online for templates in that category.
FIGURE 1-12: View the database templates for your chosen category.
Once the online search is complete (assuming you’re online at the time), a series of big buttons, one for each template found that matches your search, appears. Note that a larger list of categories — to be used if you want to search again for another category of templates — appears on the right.
So you’ve got a new database started. What do you do now? You can leaf on over to Chapter 2, where you can find out more about all the tools that Access offers — which tools are onscreen almost all the time and which ones are specific to the way you chose to dig in and start that database.
In Chapter 3, you actually begin building a database, setting up tables and the fields that give them structure. And you figure out which tables you need to set up, putting that great plan you built in this chapter to work!