Chapter 6. Tapping the Power of Relationships

IN THIS CHAPTER

• Learn how to add lookup fields to your tables

• Run the Table Analyzer to get hints on making your design more efficient

• Create and modify relationships between tables

• Use referential integrity to protect your data

The average person might define the term relationship as a connection of blood or marriage . In the relational database world, the term relationship simply refers to an association between two records. That's the good newsyou don't have to send your Access database flowers when you mess up. The bad news is that you might find Access relationships just as frustrating as your blood relations. An irate mother-in-law has nothing on an Access relationship run amuck.

The truth is that relationships are like everything else in Access. If you learn the basics and apply them correctly, they will serve you, your data, and your users well. In fact, they're the foundation on which the entire database stands. After you have the hang of it, you'll see that creating the correct relationship between two tables is rather intuitive.

You can think of a relationship as a connection between fields in two related tables where the two fields share common values. By matching the values, Access can combine records from those related tables to display related data. That's the real power behind relationshipsthe capability to display just the data you need when you need it. For example, if you're storing catalogs in one table and plants in another, you can use a relationship to tell Access how to figure out which plants came from which catalog.

Note

You can download the Chapter 6.mdb sample file for this chapter from http://www.quepublishing.com/. If you want to follow along with our examples, download Chapter 5.mdb .

You've already completed the hardest section of this bookyou learned about primary and foreign keys (in Chapter 4, "Planning a Database") and created the tables (in Chapter 5, "Building Your First Tables"). In this chapter, we'll show you how to create relationships between those tables. The result will be that you can quickly learn from which catalog you ordered your last batch of cosmos seeds . Or, you can list all the edible plants you're currently growing and so on.