Access Field Properties and Primary Key

When working with data in tables, you may encounter situations that require the data be restricted or adhere to some default specifications in particular columns. You can define these requirements by using the field properties.

The field properties affect how the data is stored and presented, among other things. The list of field properties that are available to you is dependent on the data type chosen for that field. Some field properties are specific to Text fields, and others are specific to Number fields. The field properties can be found in the Design view. As you click each field, you will see the field properties for that field.

Some of the most important field properties to note are:

Field Size:
You encountered the Field Size property before, when working with the Number data type. This property also exists for the common Text data type. It enables you to set a maximum size limit on data entered in that column. For the Text data type, size refers to the length (number of characters and spaces) of the Text data in that column. For example, looking at the Employees table, you see a field for State. Your firm tells you that the names of states should be recorded using their two-letter designation. If you set the field size to 2 for the State column, the user will be unable to type any text that is longer than two characters. So with Access, you are not only able to force a certain data type in a particular column, you can also customize that individual column to accept data only in the rigid format that you specify.

Format:
This property enables you to set the precise manner in which Access displays or prints the data that is located in its Tables. As with Field Size, the format available to select depends on the data type of that column. For example, with a Currency field, you can display the data in a form that uses a dollar sign, a Euro sign, or no sign at all. The data itself will not be changed with these settings, just how the data is displayed. Another very useful function of Format is with Date/Time data types. Whether you want to display data in the long format or short format, this property enables you to set that option.

Input Mask:
This feature can be useful in data entry situations. Where Format controls how data is displayed, Input Mask controls how data is entered into a particular field. Input Mask is available for the following data types: Text, Number, Date/Time, and Currency. For example, if a user needs to enter a telephone number, Input Mask can create the characters and structure with which you are all familiar. As the user types, the number automatically assumes a phone number format: (###) ###-####.

Decimal Places:
In number fields, you can set the number of decimal places to the right of the decimal point that will be recorded. There is an Auto setting, which defers to the Format setting to determine the correct number of places. Apart from Auto, you are able to select 0 to 15 for the number of decimal places.

Default Value:
An important database concept, the default value can help save time in the data entry process. The default value is automatically placed in that column every time a new record is added. Defaults can be overridden, so it is not forcing your column to have only that particular value.

Required:
Another important property, Required simply forces a user to enter some value, using the proper data type, in the designated field. A new record will not be added if the Required field is not properly filled. As with Input Mask, this property is an excellent mechanism for asserting more control over the data entry process.

Primary Key

Earlier, when designing a table, you asked three questions to determine just how your new table was to be created. The third question specified that you need a way to identify or reference every record. There needs to be some unique text or number column that will have no duplicate values. One example of this is a list of Social Security numbers. Each person has one and only one unique Social Security number. By definition, you cannot have a Social Security number that represents two people. This unique column is what you call a primary key, and it is the mechanism by which you relate different tables to each other.

To set the primary key, right-click on the chosen field and select Primary Key. When you set it, Access automatically determines whether that particular field has any null (or blank) values or duplicate data (data duplicating in multiple records for a single field). If there are blanks or duplicates, Access informs you with an error message. You must fill in the blanks with unique values and remove any duplicates if that column is indeed to become the primary key for the table.

Access provides its own automatic primary key with the AutoNumber data type. The AutoNumber simply increments one for each record added, so there will be no duplicates. However, it is preferable to use actual data for a primary key and not just some number that indicates the records position in a table.

If every Employee has a unique Employee number, that is ideal for a primary key. If you have a situation where there is no unique single column, consider using a combination of columns that together make up a unique record. You can set multiple columns to be the primary key; this is called a compound key. This has the effect of combining separate columns to represent a single, unique value.