Exploring Data Types

The concept of the data type is crucial not only to understanding Access, but also to unlocking the power of the programming language behind Access, VBA. Quite simply, computers process and store data, and that data is categorized by its type.

With the Design view of the CustomerMaster table open, select the Data type section of the first field and click the drop-down arrow. A list of predefined data type choices becomes visible. These data types are: Text, Memo, Number, Date/Time, Currency, AutoNumber, Yes/No, OLE Object, Hyperlink, and Attachment.

NOTE: When in Design View, you will also see a data type selection called Lookup Wizard. This selection is actually not a data type at all. It's actually a mechanism used to activate the Lookup Wizard in order to create lookup fields.

Text:
Any combination of letters, numbers, spaces, and characters is text. This is by far the most common data type. Although text can be a number, it should not be a number used in a calculation. Examples of common uses of the Text data type are customer names, customer numbers (using customer numbers in calculations would have no meaning), and addresses. The maximum number of characters allowed in a Text field is 255 characters.

Memo:
If you need to store text data that exceeds the 255-character limit of the Text field, the Memo field should be used. Long descriptions or notes about the record can be stored in fields of this type.

Number:
This type is for all numerical data that will be used in calculations, except currency (which has its own data type). Actually, Number is several data types under one heading. When you select Number as a data type in the Design view of the table, you go to the Field Size field at the top of the General tab. When you select the drop-down arrow, you get the following options: Byte, Integer, Long Integer, Single, Double, Replication ID, and Decimal. Probably the most commonly used field sizes of the Number data type are Long Integer and Double. Long Integer should be selected if the numbers are whole numbers that do not have any non-zeros to the right of the decimal point. Double should be selected if decimal numbers need to be stored in that field.

Date/Time:
Another data type often used in calculations is Date/Time. To record the time that certain events occur is among the more important uses of this data type. Recording dates and times enables you to compare data by time durations, be it months, years, or another unit. In the business world, the date field can be crucial to analysis, especially in identifying seasonal trends or year-over-year comparisons.

Currency:
A special calculation data type, Currency is ideal for storing all data that represents amounts of money.

AutoNumber:
This data type is actually a Long Integer that is automatically and sequentially created for each new record added to a table. The AutoNumber can be one mechanism by which you can uniquely identify each individual record in a table. You will not enter data into this field.

Yes/No:
There are situations where the data that needs to be represented is in a simple Yes/No format. Although you could use the Text data type for creating a True/False field, it is much more intuitive to use the Access native data type for this purpose.

OLE Object:
This data type is not encountered very often in data analysis. It is used when the field must store a binary file, such as a picture or sound file.

Hyperlink:
When you need to store an address to a web site, this is the preferred data type.

Attachment:
This data type is new to Access. When you set a field to the Attachment type, you can attach images, spreadsheet files, documents, charts, and other types of supported files to the records in your database. You can also configure the field to view and edit attached files.

Before Creating a Table

Before you begin creating a table, there are some questions that need answering:

• What is the name of the table?
• What is the entity for which you would like to collect and store data?
• What are the names and types of columns or fields?
• Which attributes of this particular entity do you need to record/store?
• What are the appropriate data types of these fields?
• How can you identify each instance of the entity uniquely?

Keep in mind that to take full advantage of Access, you may have to split data that was previously stored in one large dataset into separate tables. For example, think of a flat-file list of invoice details in Excel. Typically, this list would repeat Invoice Header information for each individual detail of that invoice. In order to eliminate as much of the duplicate data as possible, you would divide the single list into two logical parts; InvoiceHeaders and InvoiceDetails. Each unique Invoice will be listed only once in the Invoice- Header table. All of the details for that invoice will be in the InvoiceDetails table. Given this structure, Access will be able to recognize a relationship between the two tables.