Microsoft Access - Things to Consider when Making Table Fields

A field in a table basically represents a piece of information. For example, if we wanted to make a table in our Access database that stored information about a person's name, their age, and the state they live in, there are several ways we can store this information in the database by using different fields.

One obvious approach is to put all, or a lot, of the data into one table field. It is bad to put too much information into one field. You want each field to be specific and have only one piece of information. Here are the 2 contrasting approaches:

1. All Data in One Field

One way would be to create a table in the database that has 1 field of type "Text" and store all the information. In this text field, we would store ALL of the information, for example "John Doe, 89, Florida" Here we have separated everything with a comma. You COULD store information in a database like this. However, this is not recommended. It is often much better to break up the information into separate fields.

2. Different Data in Different Fields

One good way to do it is to have one field for the person's first name, one for their last name, one for their age, and one for the state they live in. This would give us 4 fields in our database.

The second approach, store different data in different fields in the table, is the correct way to go.

The reason we give each piece of information its own field is because later when we have a LOT of data in our Access database table, it will be MUCH easier to perform operations on the data.

For example, we could sort by just the first name, or just the last name, or by age, or by state. With the former method where we store everything as one field separated by commas, this wouldn't be possible. It is better practice in general to try to have each field store one piece of information. We wouldn't want one field to store a person's name AND their age for example.

It is usually a good idea to have the field name be one word. You COULD make a field name into separate words with spaces, but later when you are performing a query, it will mean more work and will cause confusion, so this is why I suggest using all one word to represent the Field Name and also to try to break down the data to be as specific as possible for each field.