Hands-On Microsoft Access: A Practical Guide to Improving Your Access Skills

PrefacePreface

This book teaches you basic relational database principles so you can run Microsoft Access successfully. It explains commonly used Access features that can be difficult to grasp, particularly those that present a theoretical or conceptual problem. It analyzes key Access wizards so you understand what they actually do and how you can modify their output. Overall, the book is aimed at readers who have run Access a little or a lot, but who still feel they don't "get" how the program works. It uses a relaxed, conversational style; imperfect but helpful analogies; loads of hands-on, step-by-step, annotated examples; and a little humor to teach you relational database theory, describe the program's essential features, and unravel Access mysteries that can cause you much grief. How Will This Book Help Me Use Access More Effectively?

My good friend Butler is a computer whiz. For hundreds of PC users in the San Francisco area, the solution to any computer problem is "Call Butler." Need more memory? Call Butler. Have a virus? Call Butler. Cauliflower growing in your hard drive? Butler can solve that, too.

Not long ago, he said to me, "I'd like to inventory my book collection. Could you show me that trick again in Access? You know, where you take fields from some tables and stick them in other tables, and somehow you get everything to work together?"

Now, there's very little I can tell Butler about Windows, or memory, or routers. But in this one area, I could help him. Because what Butler was really asking me was: How does a relational database management system (RDBMS) such as Access work, and how canI exploit that knowledge to use Access effectively?

Other programs in the Microsoft Office Professional suite make no such demands. Certainly, they can be baffling. Why did Word decide to change the font midsentence? Why is this column in Excel including calculations you don't want? Why can't you convert this PowerPoint slide to a web page?

But most of us somehow manage to get a letter written in Word, work with budgets in Excel, and get some sort of presentation out of PowerPoint. These tasks can be challenging, but they require far less extensive theoretical underpinning for you to be able to produce something (that's less true of Excel than Word or PowerPoint, but true nonetheless). What you might produce could flout every best practice and might have been designed far more effectively, yet you can get something out that will more or less serve your purposes.

You can't use Access successfully, however, unless you have a framework in your head of how a relational database works. Oh, sure, you can create a table and add a bunch of contact info to it. But other Office programs serve equally well, if not better, for simply putting data in little boxes. Only when you adopt the principles of relational database design to your data does Access fulfill its potential to manage information.

Many new Access users, if not most, don't come to the program armed with that crucial knowledge. That's not surprising. Unless you specifically take a course or read books in database design, you're unlikely to have gained the understanding of relational database principles necessary to use Access effectively.

Another challenge is exclusive to Access. Open Word, and you get a blank document; open Excel, and there's a blank spreadsheet; open PowerPoint for a blank slide. For the most part, these are the only interfaces you work in. In Access, however, you work in many interfaces. You'll frequently use at least four objects—tables, forms, queries, and reports—and each of those has two or more views. For new users, just figuring out what they're looking at—is this a form in Datasheet view? A query in Design view?—is a challenge.

This practical but relatively mundane problem begs a much larger question: Just how do all the objects and interfaces in Access interact and work together? When should you use a query instead of a table to build a report? If you add groups to a report, will it affect the values in the underlying table? If you delete a form, will it affect a report that uses the same records?

If you have a framework of how Access works embedded in your head, the answers to these questions are obvious (well, somewhat obvious). But learning the relational database model is a hurdle that many Access users never clear.Is This Book Meant for Me?

I wrote this book so that, equipped with a knowledge of relational database theory, you can successfully use all the key objects in Access—both individually and together—to manage your business and personal information needs.

This book is aimed at readers who have run Access before but don't feel confident using it. Perhaps you've read one of the "for-morons" books. You have created some databases and entered data into them. But you work in Access with hesitation, even trepidation. You don't have a good idea of how you should organize fields into tables. You have read about primary keys and relationships, but you really don't understand how they work or why they're so important. You have created a form or query or report, but you don't recognize how each interacts with its underlying table(s) and with each other. You've used wizards to create forms and reports, but when you want to modify them, you're stumped. You want to enter data easily, retrieve it freely, manipulate it successfully, and publish it with a little panache.

I don't want to oversell. This book should be sufficient if your Access needs extend to inventorying Butler's book collection, or perhaps even running a small, simple business or association. If you are using Access for a company or organization of some size and complexity, however, this book won't tell you all you need to know to design robust Access databases on your own. But it will give you the broad understanding of the major issues that need to be addressed. If you do require the services of database designers and Access programmers, you'll be much better able to understand what they are doing. You'll ask good questions and provide good answers to those posed to you. In short, you'll be a much better client, which means you're much more likely to get a more effective and productive database. Why Is Understanding Database Principles So Essential for Using Access?

Although this book includes a multitude of explanations and exercises for performing essential, everyday Access tasks, its overriding objective is to instill in you the relational database framework.

My sense of the importance of having the relational database model in your head parallels my experience of learning and teaching accounting. The basic accounting equation, as every accounting student soon finds out, is Assets = Liabilities + Owner's Equity. As I watched students struggle with the vagaries of accounting—such as why an increase in cash and an increase in insurance expense both require debits to their respective accounts—it occurred to me that their main problem was they didn't understand this basic equation. Oh, sure, they could repeat it back on a pop quiz. But they hadn't internalized it to the extent that it had become second nature to use with accounting problems.

But once you are able to see all accounting transactions as affecting assets, liabilities, or owner's equity, accounting's mysteries become solvable. Of course, accounting poses some very thorny theoretical and practical questions, and merely knowing a very simple equation won't solve most of your problems. Without having that model in your head, however, it's difficult to tackle them at all.

Relational database theory can't be reduced to a simple equation. But the need to have an underlying theoretical framework is, if anything, as important in using a relational database program such as Access as it is in accounting.

Because a table is the only object that you actually have to use in Access, it is also the object new users learn about first. So there is a tendency for novices to use a table for all sorts of tasks and functions—entering data, viewing it, printing it, and so on—when other objects are much more effective. Knowledgeable Access users will recognize the central importance of tables to a database, but they actually spend little time working in them. The relational database framework, as manifested in Access, requires that you know not only how to relate tables to one another, but also how the various objects interact with and change each other. How Is This Book Different from All Other Access Books?

Several Access books on the market accurately describe its many features. A few (not enough) books make database theory accessible to the average user. But no book I've seen spends the time or real estate required for you to gain a basic understanding of relational database theory and then builds on that knowledge to explain key Access objects and features.

I also think some Access books repeat much material that is available in the program's Help files. The motives are perfectly understandable, even laudatory: Access Help has lots of great charts that present information succinctly, and authors want their books to be comprehensive. It's impossible to know which user will need what information, and no author wants his readers running to the Help screen because they can't find what they need in his book.

But in my view, the true mission of a computer book is to explain difficult concepts in innovative ways so that readers learn and remember them. Thus, I have tried to focus on Access features and issues that pose some intellectual or theoretical problem. For topics that do not raise any thorny theoretical issues but are nonetheless still confusing, I try to give detailed explanations or hands-on examples that illustrate the problem and disclose the solution.

For the sake of completeness and convenience, I have included some essential topics that do not pose any great theoretical issues, nor are they bewildering. In these areas, I have attempted to add some value by providing a hands-on exercise, a neat tip, an interesting take, or simply a better presentation than that available in Access Help. I do not deny that you could have gleaned a certain amount of material in this book simply by pressing F1 in Access, but I think it represents a relatively small proportion of the total content.How Much Access Do I Need to Know to Use This Book?

This book assumes that you have a little—not a lot of—Access knowledge. You should know how to open an Access database and get to work in it. You should feel comfortable navigating the Database window. You should be able to do simple data entry in a form and a datasheet. You should recognize, in a general way, the difference between the Design view of objects (that is, the blueprint) and the object itself (most commonly, Datasheet view for tables and queries, Form view for forms, and Print Preview for reports). I have not been overly fastidious in providing screenshots with callouts (labels) for every toolbar button and Access element; I assume you are familiar with (or can easily find out about) the most common icons.

Let me emphasize, however, that I believe many beginners can profitably use this book. Importantly, in the exercises I've created, I've attempted to fully describe every step needed to complete it. For example, I have mostly avoided instructions such as "Create a new query in Design view and add the Products and Suppliers field lists," which would require you to know how to execute several commands (although I imagine such directions would cause little unhappiness for many readers and would be preferred by some). Instead, each command is clearly stated; for example, the first step here would be "In the Database window, click the Queries button," and you would proceed from there.What Topics Does (and Doesn't) This Book Cover?

This book emphasizes understanding how Access works. It does not include many topics (some of them important) that the more encyclopedic Access texts contain. I have mostly ignored subjects such as customized toolbars and changing fonts, not because these are not useful, but because they don't pose any conceptual or theoretical issues. There is little on topics that are mostly of use to specialized audiences, such as data access pages. And recognizing the admonishment that "a little knowledge is a dangerous thing," I have almost nothing to say about security issues, such as protecting your data from malevolent forces.

More questionably, the book also does not touch upon the computer language Visual Basic for Applications (VBA), despite its centrality to doing advanced work in Access. VBA gets very complicated very quickly. I wanted to avoid content that, in effect, says, "You don't have to understand this—just type this code here, and it will work." In every topic discussed, I want you to understand fully what you're doing and why you're doing it.

This commitment is particularly applicable to Access wizards. Creating objects in Access often involves many steps, and the program provides numerous wizards to produce them. The wizards are effective and efficient, and usually (although not always) easy to use; however, they only intermittently enhance the user's understanding of relational databases and Access. They can become a crutch, and users who rely too heavily on them will often be at a loss when they need to make even minor modifications to the wizard's output. I want you to use the wizards, but I also want you to know what the wizards are actually doing.

Here is a chapter-by-chapter breakdown of the book's contents:

Chapter 1, "Getting Started," introduces basic database terminology, such as table, field, and value. It describes the various data types and tackles the issue of null values and zero-length strings.

Chapter 2, "Database Design," is devoted to database theory. It explains the shortcomings of nonrelational database models for storing data of some size and consequence. It discusses basic relational database design principles. It takes you from the start of the database creation process (developing a mission statement for the database) to the initial distribution of fields into tables. The essential topic of primary keys and their use in resolving multivalue fields is discussed at length. To illustrate the issues posed in the chapter, I introduce the Classic TV database, which contains data about hit TV shows from the past. I also utilize a traditional order-entry model, like Northwind, to help make some of the points.

I continue to develop the Classic TV database in Chapter 3, "Understanding Relationships," where the focus is on relationships. One-to-many, many-to-many, and one-to-one relationships are analyzed, and numerous examples of each relationship type are offered. You learn how to use a linking table to resolve many-to-many relationships. At the end of the chapter, I describe and simplify the first three rules of the normalization process for relational databases.

Chapter 4, "Establishing Relationships," teaches you how to formally establish in Access the relationships you learned in Chapter 3. I discuss the system of relationship rules known as referential integrity, including two options that mitigate these restrictions: Cascade Update Related Fields and Cascade Delete Related Records.

Tables are the subject of Chapter 5, "Building Tables." It begins with a discussion of lookup fields, a confusing and minor topic that nonetheless serves as an excellent vehicle for reviewing the database design principles covered in Chapters 1–4 and gives you insight into the true nature of tables. The chapter then looks at the various methods of table creation. Most of the discussion, however, focuses on field properties—Field Size, Format, Validation Rule, and so on—which have an extraordinary impact on your database. Table properties are also considered.

Data entry is the focus of Chapter 6, "Entering, Editing, and Displaying Data." Forms are introduced and distinguished from tables. The tricky topic of how controls in forms inherit (and disinherit) field properties assigned in tables is considered. Data-entry tools and features such as input masks and the Undo command are also described.

Chapter 7, "Find and Filter," begins a section of three chapters devoted to finding and retrieving data from your database. In this initial chapter, the Find command is dissected, and the varying filter methods are discussed at length. AND and OR criteria, and related topics such as expressions and operators, are described and examples are offered.

Chapter 8, "Queries," introduces queries, perhaps the most interesting and enjoyable topic in relational databases. You'll learn about calculated fields and the Top Values property. The topic of multitable queries is particularly important because these queries reveal the true power and elegance of a relational database. Joins are introduced, and inner joins are distinguished from outer joins. Structured Query Language (SQL) is discussed, if only briefly; it's sufficient, however, to demystify the topic.

Chapter 9 has the unimaginative title of "Queries, Part II." It builds on the knowledge you acquired in Chapter 8, but the queries are not all that advanced, nor are they particularly difficult to use. These include parameter, total, crosstab, and action queries; you'll find them extremely useful.

Chapter 10, "Reports," is dedicated to giving you a more in-depth understanding of reports. Although you can create a fairly sophisticated report using a wizard in a few seconds, understanding what you created or how it was accomplished is another matter entirely. To fill the gap, I take you through the entire process of creating a report in Design view—manually, step by excruciating step, with no help from a wizard. At the end of the chapter, you create a report with the same specifications using a wizard and then compare the results, to demonstrate how each element of the wizard's output would be accomplished manually. Armed with this knowledge, you will be able to edit and add to wizard-created reports, which inevitably you will need to do.

What Chapter 10 did for reports, Chapter 11, "Forms/Subforms," accomplishes for forms. The various elements of forms—combo boxes, subforms, option groups, and so on—are discussed in detail, and you have the opportunity to add them to an existing form. At chapter's end, you'll use a wizard to create a form with the same specifications. As in Chapter 10, I compare the wizard's output with the object created by hand to note differences. Again, the aim is to help you understand what the wizard accomplishes automatically and how you can modify the results.

Chapter 12, "Form/Report Design Elements," covers a multitude of topics on report and form creation that were not covered in Chapters 10 and 11. The approach these chapters took was useful for deconstructing wizards, but there was a major drawback: Many useful report-/form- creation features were glossed over or simply were not covered. This chapter seeks to make amends and fill in the gaps. The result is a discussion that ranges from bound and unbound controls to linking and embedding, from conditional formatting to special effects, from macros to snaked column reports. It is a hodge-podge, but not an unruly one.

Chapter 13, "Importing and Exporting," is all about getting data into Access from external sources, and its opposite, putting Access data into external environments. I show various techniques that can accomplish these transfers: export and import wizards, but also cut/copy/paste as well as drag-and-drop. Because this is an Access book, the focus is on using Access features to move data. But I haven't been draconian in excluding non-Access features, especially when they accomplish the data transfers more successfully than Access tools. For example, I describe Microsoft Word's mail-merge feature at length because you are far more likely to begin and execute mail merges in Word.

Pivot tables are the topic of Chapter 14, "Pivot Tables and Pivot Charts." Although crosstab queries have their uses for slicing and splicing data, pivot tables are a far more powerful and effective tool for data analysis. In earlier versions of Access, pivot tables relied heavily on Microsoft Excel for their execution. But pivot tables are now fully and powerfully integrated in Access. You will have to endure some learning curve to use them effectively, but the investment should yield a high return.How Should I Read This Book?

This book is designed to be read chapter by chapter, in order, from cover to cover. But I know readers have varied needs and limited time, and many will probably dip in and out of its pages. If you know little about relational databases, I encourage you to you read Chapters 1–4 before tackling other areas. These pages will give you the underpinning in relational database design that is necessary to work with Access successfully.

If you do read the book page by page in sequential order, you might find it useful to know that the subject matter of the rest of the book breaks down into primarily three parts. If I had to give these sections names, Chapters 5–6 would be titled "Building Tables and Getting Data into Them"; Chapters 7–9, "Finding and Retrieving the Data You Need"; and Chapters 10–12, "Building Forms and Reports." Chapters 13 and 14, which cover exporting/importing and pivot tables/charts, stand individually.What Teaching Devices Does This Book Use?

It's trite, I know, but the best way to learn is by doing. I'd much prefer that all of this book give you hands-on practice in each topic area. Unfortunately, in the first three chapters on database design, I ask you to sit and read patiently as I develop the Classic TV relational database. There just didn't seem any way to usefully involve readers in the action.

In the rest of the book, however, and to the greatest extent possible, I use step-by-step exercises to illustrate key concepts. Most of the examples are based on the Nifty Lions database I built, which is in broadly accessible Access 2000 format. It closely resembles Access's sample database, Northwind.mdb, because I wanted you to use a database that is probably already somewhat familiar. But Nifty Lions is simpler than Northwind; it contains far fewer records, and (for Americans, at least) the supplier names are simple and familiar. In a few exercises, I have used Northwind where the example called for a database with more records. In some of the later chapters, you use other databases and files (Excel, Word, PowerPoint, picture files, text files, and so on) that I've created. All of these files are available online at http://www.awprofessional.com/title/0321245458. Under More Information, click Example(s).

Before the first exercise in each chapter, I ask you to copy the relevant database or file to your hard drive. At the end of the chapter, assuming you've done all the exercises in order, you can compare your results against the solution database or file, which is available from the same Addison-Wesley website. (For example, NiftyLionsChap7End.mdb is the Nifty Lions database at the end of Chapter 7.) For each exercise, you will also find one or more screenshots printed in the book against which you can compare your results at intermediate or final stages.

This book uses numerous analogies to help you understand important database principles. William Safire, language maven, says there is no such thing as a bad pun. Maybe that's true, but there is certainly such a thing as a bad analogy. Some database principles are so difficult to get your arms around, however, that I have erred on the side of incaution and included less-than-perfect analogies where I thought they might provide some illumination.Which Version(s) of Access Does This Book Cover?

The examples in this book were written and designed for readers using Microsoft Access 2003 within the Microsoft Office Professional Edition 2003. But the book reflects little bias toward any particular version: There is no "What's New in Access 2003?" chapter. In the few cases in which Access 2003 differs markedly from Access 2002, I have tried to provide special instructions for users of the earlier version. Nearly all chapters center on crucial issues that Access users have faced for years (well, at least since the introduction of Access 95 and 97).

I do recognize that several examples will not work exactly as advertised with Access versions earlier than 2002. But users with some knowledge of the program can scale this hurdle and still use the book profitably.What's So Great About an Access Database Anyway?

Some years ago, there was an article in The New Yorker about two brothers, both mathematicians, who were on a dubious mission. They had built a homemade supercomputer, lodged in one brother's apartment near Columbia University on Morningside Heights, to find the value of the constant pi to as many decimal places as possible. Pi was already known to millions of places (now it's known to billions), and many scholars questioned just what was being accomplished by generating millions more. One Columbia professor fumed, "This is about as interesting as going to the beach and counting sand.... Mathematics is mostly about giving pleasure."

I love that remark: It conjures up the image of an "adventuress" in an exotic seaport sidling up to a sailor and saying, "Hey, big boy, how about a Pythagorean theorem?" But the professor's comment is also startling: For most of us, pleasure is the last word we associate with our own experiences of computing the areas of rhomboids and resolving quadratic equations.

I find higher mathematics as baffling as most people, and I'm not exactly sure what the professor was getting at. But I think he was saying that real mathematicians find elegant solutions to problems, which cannot help but give pleasure to those who can contemplate such beauty.

I'm not willing to state that you'll ever find the creation and manipulation of an Access database mostly about giving pleasure. But I will say that a well-designed relational database can indeed be elegant, beautiful, lovely. Like the best mathematical solutions, an Access relational database is spare and minimal—data is never repeated twice when once will do. In a desirable contradiction, the better integrated its foundation, the more flexible and powerful an Access database becomes. And like all well-designed systems, an Access database is secure and stable: It vigorously denies entry to data that is unsuitable or even harmful.

First and foremost, I want this book to educate. But I also hope it will give you pleasure as you discover the elegance and beauty of a well-designed Access database. I know that if you experience just some of the joy in reading it that I had in writing it, you will have a grand time indeed.Acknowledgments

Although Bob Schneider might be slapped on the cover, the making of this book has been a team effort with many key players. I'd like to thank the members of the editorial group at Addison-Wesley. In the face of a brutal workload, my editor Joan Murray always made time to confer and console. She intelligently steered the book's creation and got it out the door. Acquisitions editor Stephane Thomas encouraged my proposal and commissioned the book. The difficult task of boosting my flagging spirits and confidence, as well as parrying my delaying tactics, fell to three assiduous editorial assistants: Jessica D'Amico, Elizabeth Zdunich, and Ebony Haight. Development editor Chris Zahn provided high-level guidance, project editor Lori Lyons, with the help of Rebecca Storbeck, shepherded the book through production, and copyeditor Krista Hansing edited my prose.

I greatly appreciate the thoughtful criticisms and excellent suggestions I received from my reviewers. Kel Good deserves special recognition for slogging through all the examples, step-by-step, and being generous with both his time and expertise. Doug Steele, Bob Reselman, John Viescas, and Richard Banks all made valuable, thoughtful remarks. I appreciate the warm encouragement of Ron Crumbaker and Arvin Meyer, as well as the comments of Paul Kimmel, David Hayden, and Christa Carp. Whatever the book's shortcomings, they would have been multiplied severalfold had it not been for the well-reasoned abuse of the review group.

© Copyright Pearson Education. All rights reserved.