Table Design   «Prev  Next»
Lesson 6 Taking advantage of data types
Objective Learn to use the correct data type for optimal performance.

Use Correct Data Type for Optimal Performance in MS Access

Learn to use the correct data type for optimal performance in MS Access

As you specify the correct data type for input purposes, remember always to assign the correct data when creating your database so that the data is easier to work with all around.
An example of using the wrong data type would be assigning a date-formatted piece of data to a text field. Through a text field, Access would not be able to handle the data as a Date/Time data type and would therefore not be able to perform the normal built-in validation of Date/Time values. Access would also take a long time to process the data, because the data would have to be converted from a Text value to a Date/Time value.
Drag your mouse over the various data types in the MouseOver below to get an idea of which data type you should use for which type of information.

Access Data Types
  1. Handles strings that contain less than 256 characters. Used for data containing alphanumeric characters such as names, addresses, Zip codes
  2. Used for text values greater than 256 characters. This would mainly be used for large strings such as comments and long descriptions.
  3. Used for numbers that require calculations.
  4. Used for date and time values.
  5. Used for currency values and calculated values with one to four decimal places
  6. Either a sequential or a random number generated by Access for the purpose of creating a unique value in a record.
  7. Contains one of two values from the following choices: Yes/No, True/False, or On/Off.
  8. An object linked or embedded in an Access table. This could be an Excel spreadsheet, Word document, or other type of binary data.
  9. Text or a combination of numbers and text used as a hyperlink address.
  10. This sets the data type, based on the data used when you create the list box or combo box used for looking up values in another table.


Designing tables

Designing a table is a multistep process. By following the steps in order, your table design can be created readily and with minimal effort:
  1. Create the new table.
  2. Enter field names, data types, properties, and (optionally) descriptions.
  3. Set the table’s primary key.
  4. Create indexes for appropriate fields.
  5. Save the table’s design.
Generally speaking, some tables are never really finished. As users’ needs change or the business rules governing the application change, you might find it necessary to open an existing table in Design view. This course describes the process of creating tables as if every table you ever work on is brand new. The truth is, however, that most of the work that you do on an Access application is performed on existing objects in the database. Some of those objects you have added yourself, while other objects may have been added by another developer at some time in the past. However, the process of maintaining an existing database component is exactly the same as creating the same object from scratch.
Data Types Available
When you have chosen the data type, you can further optimize your database by specifying the Field Length property.
In the next lesson, we will conclude this module.