Read an Excerpt

Grover Park George on Access

Holy Macro! Books

What This Book Will Do for You

On more than one occasion, I've worked with programmers and database developers who dismissed Access as a "toy database". I don't hold it against them and I certainly can't argue against the advantages of Oracle or SQL Server for managing corporate Data Warehouses and Customer Relationship Management systems. The truth is many, many Fortune 100 corporations, small mom-and-pop businesses, schools and other nonprofit groups, and ordinary individuals use Access to store and report on a huge variety of data in an amazing number of environments. Not one of them considers Access a toy because it does the job they need it to do.

That's basically what this book is all about; through a combination of examples, explanations and do-it-yourself exercises, I will help you learn how to use Access to control and manage the information that matters to you.

Why You Need this Book

I assume most people who have bought this book or are reading it for the first time have a specific project in front of them. Therefore, I wrote the book with that in mind. It follows the steps I would follow in evaluating a new database project, designing, building, implementing, and evaluating the database for a client or myself. If you're looking for specific tips on creating queries, for example, you won't find a query chapter; instead you'll find discussions of queries in several places: creating interfaces for entering new data, providing for lookup of existing data, and creating reports. Use the Index at the back of this book to pursue specific topics.

A Professional Approach

In leading you through the development process, I will share with you my insights into the most productive and professional ways to accomplish those tasks. In other words, if you just want to hack out a quick database and you don't really care much about how you go about it, this book is not for you.

I tried to select examples for the book that would be familiar to you, maybe even useful if you complete them yourself. I also tried to select examples simple enough to complete in a reasonably short period of time. This doesn't mean, however, that what you will learn is simplistic in any way. On the contrary, keeping the subject matter simple allows us to concentrate on the underlying principles so that you can really understand both what you need to do to create an Access database and why you need to do it.

What's in this Book

This book concentrates on Access versions 2000, 2002/XP, and 2003; the examples in it were created in Access 2002. I have seen estimates that Access '97 still makes up somewhere between one-fourth and one-third of existing installations. I also suspect it probably accounts for an even larger percentage of working Access databases. Still, after some deliberation, I elected to focus on the newer versions of Access in this book. For one thing, this book is primarily intended for Access users just starting out, so I assume most of you will be using one of the newer versions (2000/2002/2003). For another, this book is mostly about using Access to create reliable, flexible, and scalable relational databases; the concepts underlying that goal generally do not depend on which version of Access you use. As you incorporate more advanced features into your databases, of course, the differences between versions become more important. Therefore, as we get further into the book, we'll make clear any differences between versions where appropriate.

Finally, I've been working with Access 2003 for some months. Although there are some interesting new features, I haven't found anything compelling enough to suggest you run out and upgrade if you haven't already. Smart Tags, for example, which have been available in other members of the MS Office family for some time, are an interesting addition to Access. Still, they tend to fall into the "so what" category in my opinion. On the other hand, there are some enhancements of interest to more advanced developers, but frankly, most of them are beyond the scope of this book anyway. So if you have Access '97, 2000, or 2002, you'll do just fine with this book.

Get Normal

Officially, this is a book on using Access. Much of the book is devoted to defining, building, and using the objects in an Access database. However, I feel quite strongly that it would be entirely unprofessional for me not to make a concerted effort to help you understand some fundamental concepts about relational databases to prepare you for that effort. The first and most important, concept is that the tables in your database must be normalized. You may not know what that means yet, but you will after you finish reading this book! I can't make this point strongly enough.

To be useful, your database has to be properly normalized.

No combination of clever interface design, sophisticated SQL queries or complex VBA code can ever compensate for an un-normalized database. If you learn nothing else from this book, learn what it means to normalize your databases and implement that knowledge in your databases.

How to Use this Book

If you wish, you can use this book like a self-study course, working your way through the sections and chapters, one at a time, building your skills as you go. If you prefer to skip around, looking for tips on dealing with issues such as naming conventions, table design, subqueries, VBA code or whatever, please feel free to do so. In any case, the purpose of this book is to help you accomplish one thing: building Access tools that work for you.

You are welcome, even encouraged, to email me your comments or suggestions about this book. I will do my best to answer each and every email. And if I really like your suggestion, I'll see that it gets "recycled" into the next edition.

ghepworth@gpcdata.com

Why Do You Want to Use Access Anyway?

You may be saying to yourself, "That's a very strange question to ask at the beginning of a book on Access." I assure you, though; it's a very important question to ask before you launch into a database project, especially if you have never built one. Actually, there are two parts to the question. Let's address them one at a time.

Do I Really Need a Database?

Building an Access database is not a trivial task; before you invest your time and effort in doing so, you should be quite sure you're solving the right problem with the right tool.

Understanding the Problem

Take your time and answer the following questions thoughtfully about the project you have in mind. We'll discuss the alternative answers in the next section.

True or False: My project requires calculating or updating a few values on a regular basis and I need to use formulas to do that. Neither the formulas I use nor the items on my list change much and, frankly, I'm not worried about keeping track of what happened in the past. Example: I have to calculate and publish a weekly report summarizing sales for my department's salespeople so their manager can decide who has earned their weekly performance bonus and who hasn't.

True or False: It's important to the success of my project that I can easily locate and report data from several weeks, months, or years in the past. Moreover,I need to filter and sort the data in several different ways, depending on who will see the reports. Example: I have to create and publish a monthly report showing Employee Counts for each of the preceding 24 months, by Company, by Operating Division, by Department, and by Team.

True or False: I need a database of critical company information so our employees can look things up quickly and easily. Example: Our phone staff continually misdirects incoming calls to Customer Service Reps in the wrong Department or Team. I need a database of call types and the departments that handle each type so CSR's can look them up and direct calls properly.

What Do My Answers Mean?

Let's review the alternative answers to these three questions and what they might tell you about your project.

Frequent, One-Of Reporting

True or False: My project is mostly about calculating or updating a few values on a regular basis and I need to use formulas to do that. Neither the formulas I use nor the items on my list change much and, frankly, I'm not worried about keeping track of what happened in the past.

If you said "True"

The most appropriate tool for this task might be a spreadsheet — or possibly even a table in a Word document — rather than a database. As you probably know, in a spreadsheet, you embed formulas — sometimes very complicated formulas — into the cells on the worksheet right next to the cells containing the values used in those formulas. You can quickly scroll down a column or across a row, changing values in cells as appropriate. When you enter or change the value in a cell or alter a formula, the calculated results display immediately. Moreover, an advanced Word user could use a table with formulas in the appropriate cells to accomplish a lot of the same functions. The Word document also has the advantage of being easier to format for print.

There's really no hard and fast rule about when you need to create a database versus a spreadsheet or other type of file, but one useful way to look at it is to balance the time and effort required to set it up and maintain it against the results it provides. If you don't need functions specific to a database, it doesn't make much sense to incur the extra effort and expense of creating one when a spreadsheet or Word table will do the job just fine.

If you said "False"

A database probably is the right tool for your job. While you can keep historical data, even very large amounts of historical data, by saving generations of a Word table or spreadsheet, keeping track of that ever expanding directory full of files quickly becomes complicated and, more importantly, risky.

Historical and Multi-Level Reporting

True or False: It's important to the success of my project that I can easily locate and report data from several weeks, months, or years in the past. Moreover, I need to filter and sort the data in several different ways, depending on who will see the reports.

If you said "True"

You're definitely going to be building a database. One key characteristic of all databases is the ability to keep large amounts of data over extended periods of time. The only real limit is the amount of storage space available. A second key characteristic of a database is the ability to filter and sort data into many different combinations. One source of data can feed an almost infinite variety of reports.

If you said "False"

You probably can use a spreadsheet or even a Word document to create a one time only snapshot of data. Again, the key is that the data is needed one time, in one format, and you don't need to re-use that same data over time in other formats.

Job Support Tools

True or False: I need a database of critical company information so our employees can look things up quickly and easily.

If you said "True" or "False"

I'm sorry; this one is sort of a trick question. I threw it in to make an important point about one of the occupational hazards of database development. On more than one occasion, I've been approached by a manager with a request like this one. And on more than one occasion, we've decided that they really didn't need a new database at all.

In this example, the business problem is that employees are misdirecting calls. That wastes everyone's time and causes frustration for customers and for employees who get the misdirected calls. However, the problem doesn't necessarily arise out of lack of access to the appropriate information. There are at least three other possible causes that should be evaluated and, if possible, eliminated before starting a database development project.

First, it may simply be a result of incomplete or inappropriate training provided to the call screeners. Perhaps those employees answering phones are simply directing calls to the first available Customer Service Representative. In fact, to them, getting calls to a CSR as quickly as possible makes sense in the event that they don't realize that it also makes a difference to whom they direct the calls.

Second, it may be a result of inappropriate or ineffective management. If managers have failed to communicate that the current behavior is not appropriate, or if they have failed to offer direction and provide rewards for appropriate behavior or impose consequences for inappropriate behavior, the employees answering phones have no incentive to change the way they do it.

Third, inappropriate or poorly thought-out workflows or limitations in tools often prevent people from doing their jobs the way they know they should. In this case, for example, it may be a limitation of the phone system that it automatically assigns all transferred calls to the next available CSR by default. Call screeners have no choice about who will get that call. They know there is a problem, but can't do anything about it.

What does all of this have to do with building a database? Well, to repeat a point I made a little earlier, creating a database is not a trivial task, even with Access. If you want to be a competent, professional database developer, the last thing you want to do is invest your time, effort and other resources solving the wrong problem!

At best, the database will go unused because it doesn't meet a real business need. At worst, the managers who paid you for that time and effort are going to ask you some very unpleasant questions when they don't see any return on that investment.

The Right Tool for the Job

Okay, then. At this point you're sure a database is the right tool for the job ahead of you and you're ready dive in and start building Access tables.

Stop, take a deep breath, step away from your PC, and read the next section first.

Why do you want to use Access?

If you're a new Access user, you probably bought this book for a reason not too different from one of the following:

* The person for whom you work has asked you to "do something about ____".

Fill in the blank with the appropriate crisis in your organization: misplaced phone messages and letters from customers, hassles getting timely attendance reports from classroom teachers, keeping track of scheduled vacation days for everybody in our department, and so forth.

* Someone who used to work for your organization built the Access database you all use every day, but that person left months ago. Now it doesn't work anymore, your predecessor didn't bother with documentation of any kind, and it's up to you to fix it — now!

* You're tired of using an Excel spreadsheet (or a series of spreadsheets) to track dozens (or hundreds) of pieces of equipment used by your department. It's confusing and time -consuming to keep them all updated. You've been told an Access database would be a better choice, but you've never used Access before.

* The organization you work for has a formal (and seemingly unending) process for evaluating all proposed IT projects. Your friends in IT have told you Access is just a toy database and what you really need is SQL Server with a web -based front end, but, frankly, you can't wait six months just to get your project on the Resource Allocation Committee's agenda for discussion. With Access, you can build yourself something that works right now, even if it isn't perfect.

* You just upgraded your old PC, including a copy of MS Office. Now, to keep peace in your family, you need to justify that expensive software by building a database to track your spouse's collection of ____________. Fill in the blank with the appropriate hobby: exotic coffee mugs, vacations photos from the south of France, bird sightings in all fifty states, race walking results for their Race Walking club, etc.

These projects share four common characteristics. First, according to the criteria we discussed in the preceding section, they all appear to call for a database, even if it's only by management fiat! In addition, you started them because you

• need to solve a specific, more or less well-defined problem,

• have little or no experience or training with relational databases in general and Microsoft Access in particular and

• have little or no time or money to invest in training or professional solutions.

You can take some comfort from the fact you are by no means the first person in this position, you won't be the last, and, above all, you are not alone. You'll get a lot of help from this book, and from some of the other resources described in it. As my mentor, Red Green, likes to say, "We're all in this together."

---

Excerpted from Grover Park George on Access by George Hepworth, Linda H. DeLonais. Copyright © 2003 George Hepworth. Excerpted by permission of Holy Macro! Books.
All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.
Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.

---