As stated previously, a table in Access is a collection of records. Tables in Access come in two basic types representing the two basic types of databases: flat and relational. This really is a matter of the organizational style used in the database. The distinction between a flat-file database and a relational database was also previously discussed. Here we will describe how these two types of databases are organized in Access with respect to tables.
A flat file database can be viewed as a simple answer for a simple need. In an Access flat file database, all of the data is collected together into a single table. As an example, consider a database that stores all of the phone numbers and addresses of your friends and colleagues. The names, addresses and phone numbers would all be stored in a single table (aka database). If there were only three fields, say Name, Address, and Phone, then there would be some duplication if one person had more than one phone number such as a home phone, mobile phone, and fax line. For such a simple database, such duplication would not be much of a problem.
A relational database offers a complex solution to larger problems. Relational databases attempt to minimize storage space by eliminating redundant data. To do so, the data is separated into two or more tables.
Let's revisit the database described above. Given that many folks now have more than one phone number, there would be a lot of redundancy of records. Of course, that could be eliminated by having more than one phone field. However, what if your database were more complicated? What if you needed to store information about students? In this example, you would need information about the student's home (parent's names, address, phone, etc.), transcript information (classes taken and grades), and schedule information (courses by department and number). To do this relationally, you could have one table with all of the student's home information, another table with a list of all of the courses taught by each department, and a third table with transcript information.
The key to this approach is what Access calls a key field or linking field. All of the tables in a relational database contain this field and it is this field that links the matching records in all of the tables together, and eliminates redundancy. In the student information database the key field could be the student id number. If you needed to produce a transcript, entering the student id number would look up the student name information from one table, the classes taken with grades from another table, and so forth.
Yes, this sounds much more complicated than a flat-file approach because it is. Conceptually it is often difficult to imagine let alone try to plan. However, it does reduce storage space and the chance for errors at the same time.
| Return to |
| Access 2000 |
| Access XP |
Copyright © 2003
last updated
August 13, 2003