Access 2013: 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, 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 this important task 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 is not created equal. Consider the Dolls table you created in Chapter 1 (Building Your First Table). 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 something that can be interpreted as a date. But if you haven’t set the data types correctly, Access doesn’t share ...