Office 2010: The Missing Manual

Chapter 26. Building Smarter Tables

In the previous chapter, you learned how to dish out databases and pop tables into them without breaking a sweat. However, there’s bad news. The tables you’ve been creating so far aren’t up to snuff.

Most significantly, you haven’t explicitly told Access what type of information you intend to store in each field of your table. A database treats text, numbers, dates, and other types of information differently. If you store numeric information in a field that expects text, then you can’t do calculations later on (like find the average value of your bobblehead dolls), and you can’t catch mistakes (like a bobblehead with a price value of “fourscore and twenty”).

To prevent problems like these, you need to define the data type of each field in your table. You’ll tackle that task in this chapter. Once you’ve mastered data types, you’re ready to consider some of the finer points of database design. Finally, you’ll also spend a few pages learning about a favorite pastime of seasoned database pros: keeping bad data out of your tables.

Understanding Data Types

All data is not created equal. Consider the Dolls table you created in the previous chapter. Its fields actually contain several different types of information:

• Text. The Character and Manufacturer fields.

• Numbers. The ID and PurchasePrice fields.

• Dates. The DateAcquired field.

You may naturally assume that the PurchasePrice field always includes numeric content and that the DateAcquired field always includes ...