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 can I 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 objectstables, forms, queries, and reportsand each of those has two or more views. For new users, just figuring out what they're looking atis 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.