Database design with Microsoft Access: Understanding Field Types

Understanding Field Properties

Field properties are an important and powerful part of any database design build. The integrity of your data hinges on setting field properties to the correct value. For example you may wish to display a full data item or just an abbreviation, you may set currencies to display with a specific number of decimal places, or a date and time stamp to appear in 8 or 10 character format. When you create fields Access allows you to display a list of properties based on the data type of that particular field. These field properties are different depending on the data type item chosen.

The following data type items are related to the corresponding Field Type properties.

Text and Memo data type field properties

Field Size – Used to vary field lengths, defaults to 50 characters.

Format – Allows you to create custom formats for your text string, i.e. ‘@’ = text required, ‘&’ = text is not require, ‘<‘ = forced lower case, ‘>’ = forced upper case.

Input Masks – Used to ensure that all data entered into a field was the correct data entry pattern. Forces the user to enter values in an exact format.

Caption – A naming convention given to a field to help the end user input the relevant details.

Default Value – A self explanatory field type, automatically populating the field with a pre-defined default string.

Validation Rule – Allows you to set limits to values to maintain data integrity.

Validation Text – Used in conjunction with the Validation Rule will display a warning message if the user has not met certain validation rule criteria when populating the field.

Required – Simply a value that denotes whether a field should be populated, i.e. is or is not mandatory.

Allow Zero Length – Allows you to control the way that blank fields are handled.

Indexed – The crucial field property for expedient queries, sorting and field manipulation. Indexed fields hold all tables and relationships together.

Number data type field properties

Field Size – Allows you to limit the range of digits allowed within the field. The default setting being Long Integer and for decimal places set this should be set to Single or Double.

Format – Enables numeric and date/time fields to display the contents of a field in a specific format.

Decimal Places – Simply enough, sets the number and currency field to a chosen set of decimal places.

Yes/No – Can contain one option from two choices. Yes/No, True/False, On/Off.

In the following part we will discuss the importance of Lookup Fields and how they can be used by multiple tables to populate highly valuable data, quickly via a simple set of masterfiles (Lookup Tables).

Related Reading:

>>> Microsoft Access: Designing a Relational Database (Part 1)

>>> Microsoft Access: Designing a Relational Database (Part 2)

>>> Microsoft Access: Understanding Data Types

>>> Microsoft Access: Understanding Lookup Fields

This post is part of the series: Designing a Database with Microsoft Access

Need some help navigating Microsoft Access? This tutorial shows you how to design a database that works smoothly for all your projects.
  1. Microsoft Access: Designing a Relational Database (Part 1)
  2. Microsoft Access: Understanding Data Types
  3. Microsoft Access: Understanding Field Properties
  4. Microsoft Access: Understanding Lookup Fields
  5. Microsoft Access: Join Properties and Join Types
  6. Microsoft Access: Defining Relationship Types
  7. Microsoft Access: Setting Up Relationships Between Tables
  8. Microsoft Access: Designing a Relational Database (Part 2)