Tables
A table is the storage mechanism used by Microsoft Access to store information. Each table in Access resembles an Excel worksheet and consists of rows and columns. In a really simple database, only one table may be required but, more typically, a database will include several related tables. For databases to work efficiently, each table should store only one type of data. For example, in a typical database, you might have one table for storing client information, another for storing information about the company products, and so forth.
Fields (Columns)
Each column in a table is known as a field. When designing tables, it is important to define a field for each category of information you will need to store and not to lump separate pieces of information together in a single column. As a general rule, having more fields provides you with more flexibility in data retrieval. Thus, for example, if you define three fields called “Title”, “FirstName” and “LastName” rather than having a single field called “Name”, you will be able to combine them either to form a salutation or a full name.
Records (Rows)
Each row in a table is called a record. A record represents a single piece of information on a given topic. Thus, if the table holds student grades, each record will constitute the grade achieved by a particular student in a particular exam. In a client table, each record will contain details about an individual client, and so forth.
Primary Key Fields
In order for the information stored in a database to be useful and accurate, it is important to avoid duplication of information since this will tend to increase the occurrence of errors. Each record in a database should be uniquely identifiable. Thus, for example, if we have a table containing student information and there are two students with the name “John Smith” living at the same address, we need to be able to distinguish between them. The recommended technique is to define a field within each table which will always contain a unique value. This field is often called the primary key. So, in our student table example, we would create a “StudentID” field and assign each student a unique student ID. When designing tables in Microsoft Access, the unique value is usually achieved by setting the field type to “AutoNumber”, enabling Access to automatically generate a unique value for each new record created in the table.
Relationships
Although the information within a database is held in separate tables, the complete system which this information represents is a unified whole and there are associations between the information in all the various tables. Microsoft Access is called a relational database management system because it allows users to create relationships between the various tables. Relationships lend efficiency to databases by preventing the creation of redundant data. For example, in a client database, the table containing client information would be related to the table(s) holding client invoices. When creating the invoice, information from all the related tables can therefore be included.
The author is a training consultant with OnSiteTrainingCourses.Com, an independent computer training company offering Microsoft Access Classes at their central London training centre.