Front Range Data Management Services, Inc.

Microsoft Access Tables

Access Tables

  Microsoft Excel
  Microsoft Access
  Custom Reports
  SQL Server
  Visual Basic
Tables are the basic structure that stores data in Microsoft Access. While they look like an Excel worksheet when you open them, users should study some basic concepts before starting to design tables. In some ways, Microsoft has done a disservice to Access users by over-simplifying the table design process, providing an initial interface where users just type data, and Access builds the table. While users don't generally want a long discussion before building their database, some of the concepts are important for a database to function.

While a table looks like an Excel worksheet, a significant difference is that the columns, which represent fields, must be set for a specific data type. In Excel, any value can be typed in any cell; with Access, columns (fields) should have a specific data type.

As users design a table, not only is the data type important, but following the rules of database normalization is important. To build a customer list, one might be tempted to put all the information into one table, using fields like Name, address, City/State/Zip, phone, e-mail, favorite product, and other fields. This will be problematic as the list grows. Elementary database theory would improve your table. While there are myriad books available for more in depth discussion, for beginning purposes, consider the first three rules of database normalization:

  1. Make all fields atomic.
  2. Capture or create a unique identifier for each record.
  3. Make all columns fully dependent on the unique identifier.

For the first normalization rule, it would be strongly suggested that the name field be separated into, at least, first name and last name (more complex databases will further divide this into salutation, first name, middle name, last name, and suffix). The field for City/State/Zip, while you will usually assemble them, would also be better separated. This will be invaluable for sorting and selecting data, as well.

For the second normalization rule, primary key be would be used. The primary key is something that uniquely identifies the record. To identify a person, there are two good candidates for primary keys, a Social Security Number and an E-mail address. Almost all people have both of these, and they are unique to that person. However, if you are running a small shop, you are likely to be highly unsuccessful getting people to give you their social security number, so unless you have a good reason to need it, that would not be recommended (even if you have a need to collect it, I would urge you not to use that as a primary key; you have a duty to protect that piece of information). An e-mail address is a candidate for a primary key, but e-mails tend to change (not to mention they can get very lengthy). For a record of a person, I almost always use an autonumber data type, where Access assigns the next whole number (integer) to each record. This might become your customer number.

To comply with the third rule, things like preferences, and possibly e-mails, should be removed from the first table, and stored in a related table (Access is a Relational database management system). If the customer had two favorite products, this would be difficult to capture; you'd have to (1) duplicate the record, (2) make a second (then third, then fourth . . .) preference field, or (3) add two or three items together in one field. Each of these is a very poor design choice; the better answer is to build a second table that relates to the customer table and can have more than one record relating to that customer.

As a final word on this very brief introduction to table design, FRDMS strongly recommends that you use field names without spaces. By using capitalization for the first letter of each word, the field names are easily readable. If you call a field "First Name" as Access allows, then when you refer to this field in queries, forms, reports, and code or macros, you will have to enclose the field names in brackets. While Access does most of this work for you, sooner or later you are bound to grow in your proficiency with Access to the point where you are typing extra keystrokes to enclose the field names in brackets. Instead of calling the field "First Name," remove the space and call the field "FirstName."

Next step: Create relationships between the tables in your database.

For more help with Access table design, or other Access questions, call (303) 403-0386, or E-mail us.

Next: create relationships.

Copyright 2012 Front Range Data Management Services