Fixing Access Annoyances

Chapter 3. Data, Tables, and Database Design

GIGO—Garbage In, Garbage Out—means that all the fancy forms and reports in the world don’t mean a thing if the data in your base ain’t got that swing. In this chapter, we tackle the annoyances that prevent you from getting and keeping good data.

Chief among these annoyances is the fundamental issue of good database design. The average Access user quakes at the thought—mostly because so-called “normalization” rules are usually explained in terms so arcane that transmuting lead into gold seems simple by comparison. If you cut through the techno-speak, though, database design is simple—and we’ll show you how to do it right. Then we’ll help you ensure that the data that goes into your lovely design is valid. Finally, we’ll address the myriad migraines that Access causes when you try to move data around using import, export, and linking.

DATABASE DESIGN AND DATA INTEGRITY

Table Design 101

THE ANNOYANCE: I’ve heard that I’m supposed to “normalize” my tables, but the books that discuss this are really confusing. “Primary keys,” “foreign keys"…what are they talking about? And “Boyce-Codd normal form” sounds like a disease. Why do I need an advanced degree to create a simple contacts database?

THE FIX: Normalizing basically means organizing data to reduce redundancy. As the esteemed Webopedia (http://www.pcwebopedia.com) pithily puts it, it involves “dividing a database into two or more tables and defining relationships between the tables. The objective ...