As discussed in a previous blog post about Database Terms, a field in a database table is like a column in a spreadsheet. But one of the main differences between a spreadsheet and a database is that fields must have a specific data type in a database. The purpose of this post is to list and define the data types that exist in an Access database.
As we review each data type it may be helpful to refer to the following table:
Text fields are very common. As their name suggests, Text fields hold text. This text can be just letters, or it can be a combination of letters and numbers. Text fields can hold any type of text provided it is 255 characters or less. In the example above, the Store Name and City would be text fields.
Numeric data should also be stored in text fields provided that the numbers are not used in numeric (math) calculations. So the Phone Number should be stored in a text field.
A memo field is like a text field for lots of text. The Comments field should be a text field.
Number fields, as you may have guessed, are used to store numeric data that is used in numeric calculations. The Quantity should be stored in a number field.
Auto Number Field
Auto Number fields store sequential numbers. Unlike other fields, the user does not and cannot enter a value into an Auto Number field. These fields are great for ID fields.
Whether it is for US Dollars, English Pounds, Japanese Yen or other currencies, monetary values should be stored in currency fields. Therefore, the Unit Price field should be in a currency field.
Date fields are used for dates and times but not used for partial dates. The Purchase Date would be stored in a date field. Values like March 1, 2013, 9:00 AM, and March 1, 2013 at 9:00 AM would all be held in a date/time field. But you can’t store just the year or the month number in a date field. Instead, it must be a complete date or a complete time of day or both.
Date fields are also not used to store durations. For example, in a recipe database you would not store the “time to cook” in a date field, instead that would go into a number field.
A yes/no field stores a value that is either Yes or No, True or False, On or Off. The Club Member field would be a yes/no field.
The Website Field above would be in a hyperlink field because hyperlink fields are used to store links to an e-mail address, a web site, or a link to a folder on your computer.
Attachment Field (new with Access 2007)
Just as you can attach files to an email message, Access lets you attach one or more files to a record using the attachment field.
OLE Object Field
OLE Objects are used to store information like pictures and sounds as well as other objects like a Microsoft Word document or an Excel Spreadsheet. However, because OLE Objects can be very large (up to a gigabyte in size), it is best to avoid using them. Instead, try linking to the file with a hyperlink or attaching it with an attachment field.
Calculation Field (new with Access 2007)
A calculated field is used to store the results of a calculation. In our example, the Extended Price (Quantity * Unit Price) could be stored in a calculated field.