Page 6 of 11PREVNEXT

Design tables for a new Access 2007 database

Fields and possible relationships

Some gritty details

The next step in your design is to list the fields for each table. In an Access table, columns are called fields and individual records are called rows. You plan your fields by deciding the specific information each of your groups should capture. Again, you can refer to your existing data — the spreadsheet, a ledger, or even your card file. You can also open an existing database, such as one of the Access database templates, and see which fields it uses.

For your asset database, you'll probably want to list each item and information about each item, such as purchase dates and costs. As part of this, try to reduce each field to its smallest logical component. In a good design, a field represents a single piece of data, and the name of the field clearly identifies that data. For example, your tables should have separate fields for first and last names because that makes it easier to find and sort your information.

Also, ask yourself if you need to record international data. If you do, you'll need fields for Country, plus Region instead of State, and Postal Code instead of Zip Code.

As you work, you may find yourself wanting to use data from one table in another. For example, the picture shows that the Assets group includes fields for suppliers and support. That's natural — you're seeing how you need to relate your tables, and we'll discuss those relationships in just a bit. For now, include all the fields you think each table should have.

Note     If a field name contains more than one word, you can separate each word with spaces, but that may not be a good idea. Spaces can make it harder to create other components, such as Visual Basic for Applications code and a type of formula called an expression. If you need to use multiple words in a field name, you can eliminate spaces altogether, or separate each word with an underscore. The Quick Reference Card at the end of this course provides more information about characters you can use in field names.

Finally, in case you're wondering, you don't plan rows. Those come naturally as you enter data in your fields.

Page 6 of 11PREVNEXT