When using Microsoft Access we need to store the data somewhere. In traditional PC based database systems the data would be stored in flat files. In MS Access however, any data entered will be stored in what are known as Microsoft Access tables. A table will have a series of columns known as fields or attributes. For example, in a contacts database system we would have fields for first name, surname, address, date of birth etc. Each field will have a data type which defines the type of data it can accept. For example if we are storing a price we do not want to use a text data type. We would use a numeric data type in this case. If we are storing a lot of text it is best to use a memo data type. A memo data type is ideal for storing long descriptions or even notes.
Probably the most important field of a Microsoft Access table will be the primary key field. This is used as the identifying field of the table. The best data type to use here is the auto number. The auto number will increment by one each time a new record is added to the table. Changing the auto number is not allowed. The identifying field can be and should be made into what is known as a primary key field. Microsoft Access makes this easy. When a field becomes a primary key, no duplicates will be allowed in that field and it only accepts unique values. In design view a small key symbol will be present next to the field to show that it is a primary key.
We may want to store a date of birth. In this case the best data type to use is Date/Time. When using this data type we can set something called an input mask to help us enter the date in the correct format. Microsoft Access 2007 introduces the attachment field. This is useful for storing related documents to the record. For example, we may wish to store Excel spreadsheets, images or even Word documents. The attachment field should be used with caution though as too many attachments can bloat your database. Sometimes it may be better to simply provide a link path to the documents.
Tables can be related together to form relationships. An invoice and customers table will have a relationship in that one customer can have many orders. This relationship is modeled by linking the primary key to what is known as the foreign key. There are other relationships available in Microsoft Access.
When screen forms are created they are normally based on tables. This means the data entered into a form will be saved into a table and the data displayed on the form will be loaded from a table.
Microsoft Access tables are probably the most important components of your database system and as such need to be designed correctly. If the design and relationships are correct from the outset it will save a lot of heartache later.
Do you want to get a Microsoft Access tutorial series that’s jam packed with hints and tips