|
Access Tables Home 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:
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