Access 2007: The Missing Manual

Chapter 2. 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. This is the central task you’ll tackle in this chapter. Once you’ve mastered data types, you’re ready to consider some of the finer points of database design.

Understanding Data Types

All data’s not created equal. Consider the Dolls table you created in Chapter 1 (Section 1.2.3). 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 the DateAcquired field always includes something that can be interpreted as a date. But if you haven’t set the data types correctly, ...