Data Types and Field Properties of Tables MS Access

A field’s data type is the most important property because it determines what kind of data the field can store in MS Access tables.

Data types and field properties of tables

Every MS Access table is made up of fields. The field properties describe the characteristics and behavior of data added to that field. A field’s data type is the most important property as it determines the type of data the field can store.

A field’s data type determines other field qualities like the following:

  • What formats can be used with the field
  • The maximum size of a field value
  • How the field can be used in an expression
  • Can the field can be indexed?

The table below shows the formats available for each data type

Short TextShort, alphanumeric values, such as a first name or a house number
Number, Large NumberNumeric values such as marks in a subject
CurrencyMonetary values like course fees
Yes/NoYes and No or true and false
Date/Time, Date/Time ExtendedDate/Time: Date and time values for the years 100 through 9999.
Date/Time Extended: Date and time values for the years 1 through 9999
Calculated FieldResults of a calculation. The calculation always refers to other fields in the same table. We use the Expression Builder to create the calculation.
AttachmentAttached photographs, spreadsheet files, word documents, charts, and other types of supported files to the records in our database. This is similar to e-mail messages attachments.
HyperlinkText or combinations of text and numbers used as a hyperlink address for emails and websites
Long TextUsed to display Rich Text, which includes formatting like bold and underline and  it can store up to about a gigabyte of text.
LookupDisplays either a list of values that is retrieved from a table or query, or a set of values that we specify when we create the field as in course names. When the Lookup Wizard starts we can create a Lookup field. The data type of a Lookup field is either Text or Number, depending on the choices that we make as we use the wizard.

The other details connected with the data types are explained in the video.

The ‘Append Only’ property in a Hyperlink determines whether to track field value changes. There are two settings:

Yes: Tracks changes. To view the field value history, right-click the field in datasheet view, and then click Show column history.

No: Does not track changes. Setting this property to No deletes any existing field value history.

Append Only Property in Student’s Table

Further Reading:

Introduction to data types and field properties