Choosing The Right Data Type For Microsoft Access Table Fields
Go on any Microsoft Access training course and you will learn about the creation of database tables. When you are designing tables in an Access database, it is important to ensure that each field has the appropriate data type. If the field type does not reflect the type of data entered into the field, then whenever the database is sorted, and when searches and queries are performed, erroneous results will be produced. Thus, for example, if you have a field called “date_of_birth” and you define it as a text field, you will still be able to input data into it. However, when you sort on this field, you will get an alphabetical sort, whereby “11/4/1969″ will be placed before “12/7/1989″.
Access contains 9 different data types and each time you create a field, you need to pause for a second and think about the data it will contain and the range that that data will fall into. The Access data types are Auto Number, Number, Currency, Text, Memo, Date/Time, Yes/No, Hyperlink, Attachment and OLE Object.
The Auto Number data type is a specialised long integer which is used for primary key fields which need to contain a unique numerical value. As the name suggests, you cannot directly enter a value into a field defined with this data type; Access automatically assigns a new value each time a record is created.
The Number field type is used for all fields which contain values which are definitely numeric in nature such as a person’s salary. Having chosen this data type, you can then specify a Field Size value in the Field Properties section of the table design view. Thus, if you had a field called “width”, you might choose integer which allows you to input values up to approximately 32,000.
If the field you are defining relates to money, you can use the Currency data type rather than the more general Number type. Currency is designed to perform accurate calculations on money-related figures. It allows 15 figures to the left of the decimal, and 4 to the right of, the decimal point.
There are two fields which hold alphanumeric characters, such as “description”, “telephone” or “firstName”: Text and Memo. The Text data type will accept a maximum of 255 characters while the Memo type can accept up to approximately 65,000 characters.
The Access Date/Time data type is a special type of number field which is designed to accurately hold dates and times. Sorting, calculations and queries performed on this type of field will give the correct chronological results.
The Yes/No field type allows you to store Boolean (True or False) values. Examples where this field might be useful include “internal”, “approved”, “inStock”, “female”, etc.
With the advent of the web, database fields that contain URLs are fairly common. The Hyperlink data type is used to create a Microsoft style hyperlink, as found in Word and Excel. You can right-click on this field and choose Edit Hyperlink from the context menu to display the standard Microsoft dialog for editing hyperlink properties.
The Attachment and OLE Object data types are both used for storing binary data such as images and other documents. Attachment is the more efficient of the two. As the name suggests, it works in a similar way to the attachments you add to an email. Access embeds the attached file in the table so that it becomes part of your database, increasing the file size accordingly. The OLE Object data type can be used in much the same way but is less efficient and exists principally for backward compatibility.
categories: microsoft access training,databases,microsoft office,software,computers