James's Database
~~James
La Borde
Access Data Types
A Penny Saved is a Penny Earned
This month’s topic is data types. Now that we have our database design planned out, we need to
concentrate on what types of data are going into our database.
Selecting the correct data type is critical at this stage so that
we can manipulate the data once we have it in the database.
It also limits the user to inputting the correct type of data.
Since Access has limits on size (1 GB for an Access97 .mdb and 2
GB for an Access2000 .mdb), selecting the correct data type can be even
more critical in a large application.
Bear in mind that when you start filling your database, every
field will be added when you add a new record, whether data is in the
field or not. This is why
field size as well as data type is addressed.
We shall now explore the various data types and how they handle
our data.
Text
We will start with the data type you will most
commonly use. The text data
type is for more than just text however. This is an area where most beginning database developers make
mistakes. The Text data
type is best used for text fields and for number fields containing data
that will not be part of a calculation. This may sound a little confusing but will help you in the long
run. If you have a number
that will not change and will not be used to calculate another value, it
is a prime candidate for the text data type. Social Security numbers or ZIP codes are prime examples of this
type of data. You will not
perform a calculation on someone’s ZIP code or Social Security number.
They contain a fixed number of positions and limiting your input
in these fields to that amount can save space. The number of characters in the field length determines the size
of each field.
Text fields also have a great feature available
called Input Masks. Input
Masks restrict the type of data that can be entered into the field and
how it is displayed. They can be set up to make a character be a
specific type, case, or make it a required or optional entry. Using
Input Masks is a good way to prevent your user from entering a
four-digit ZIP code. There are several default input masks and you can
create your own. The Social
Security number input mask, for example, only allows the user to input
nine characters, and they must all be numerals. No alpha characters are allowed.
I have created my own called Report Code. This restricts the user to inputting four uppercase letters
then three numerals.
The last consideration for text fields is the field
length. The default field
length for a text field is 50 characters. This is often far too many characters and will result in wasted
space in your database. A
Social Security number field could easily be limited to 9 characters.
A state field could be simply two characters for the
abbreviation. Other data
will require different lengths. It
is okay to be somewhat generous with your space in text fields but
don’t be wasteful. How
many last names can you think of that are 50 characters long? On the other hand, do not try to be too restricting.
Remember that the user will not be able to enter more than you
allow them to have in the field length
Number
There are several number data types.
-
The
first and smallest number data type is a Byte. A Byte only takes up 1 byte of storage space in your
database for each record. Byte
allows whole numbers from 0 to 255. This is very restricting and should only be used if those are
the only 256 numbers that you will need to enter.
-
Next
is Integer. Integer
takes up two bytes of space for each record and allows for whole
numbers between -32,768 and 32,767. Even though it is a somewhat limiting number, it may serve
your needs.
-
The
next step up is the Long Integer. It takes up four bytes of space for every record and allows
for whole numbers between –2,147,483,648 and 2,147,483,647. This is more than enough for most, but not all, whole numbers
that you may encounter.
All of those number types are for whole numbers.
But what if your number has decimals? There are additional number data types for you too.
-
The
smallest is Single. Single
data type takes up four bytes of space per field and is named for
single precision floating-point. It will handle most decimals that can be thrown at it.
-
The
next option is Double, short for Double precision floating point.
This data type supports almost all decimal numbers and takes
8 bytes of space per field.
As you
can see, the type of data to go into these fields determines the data
type. It is imperative that the correct data type is selected, as
the wrong one will prevent data from being entered.
Currency
Currency is a fairly obvious data type.
It allows the user to enter a value to be stored and treated like
currency. Currency, like
double, takes up eight bytes of space for each field. Currency data type should only be used for those fields
absolutely needing to be stored as currency. It will allow for larger numbers than some of the other data
types but your decimal positions are restricted.
Memo
The memo data type is highly useful and the only
data type that field length (and therefore size) is not set at the time
of design. Memo allows the
user to enter as much data as they possibly want to in the field. This is great for a comment field.
Memo does something that text does not: it grows and shrinks with
the data input. The only
restrictions are the size of your hard drive or the Microsoft Access
file size limit.
Date
The
Date data type is used specifically for dates. It will not hold
any other type of data. It will store dates from January 1, 100 to
December 31, 9999. Note that the BC years are not available as
valid dates in Microsoft Access. This can be important if you are
dealing with an historical database in which this type of date may be
needed. You may have to figure out an alternative way to store
those dates. The Date data type takes up eight bytes per field and
is the only other data type other than Text to allow for specific input
masks.
Boolean
Boolean
data type, also known as Yes/No, takes up a mere two bytes of space per
field and allows only the selection of Yes or No. This data type
is ideal for a check box type of field. Does this computer have a
CD-ROM drive? There are only two possible answers; therefore, a
checkbox or Yes/No field is ideal. The default value of a Yes/No
field is No, unless the developer sets it otherwise.
AutoNumber
The
AutoNumber field automatically inputs the next number so that no
duplicates are produced. This is a great field to make a Primary
Key for a support table. There are some precautions that should be
taken here. If you need the numbers to be sequential, you may want
to look elsewhere. Once Access assigns a number, it is assigned.
If the user escapes out of the form then that number is already used and
will not be reassigned. This can be taken care of by compacting
the database. The other word of warning about the AutoNumber field
is that it can not be changed. You do not have the option of
inputting a number or taking the number given. You must simply
take the number given.
Other Data Types Available
There
are a few other data types available that you may or may not use.
You may select the Hyperlink data type that will allow you to enter
hyperlinks. The Object data type allows the use of OLE objects;
for instance, a current photo of each of your employees with their
employment data.
An Ounce of Prevention is Worth a
Pound of Cure
The determination of data types is key.
Knowing what the different data types are and do helps in
assigning the appropriate one to each field. With the cost of hard drives falling as fast as the size is
rising the size restrictions are becoming a thing of the past, except in
very large databases. They
should still be accounted for as your database may grow. Changing data types once you have data in them can be
hazardous as the data can be truncated or even deleted. By designating the correct data types during the development of
your database you will save a lot of aggravation later.
James La Borde works
in the computer department at a Credit Union,
where he uses Access, SQL Server, VBA, and ODBC daily. He also
teaches online Access classes at
Eclectic
Academy.
|
,
