Table: Data type

Text
If you specify that a field has the Text data type then
Access will permit any characters to be placed in that
field in a row of the table. This is a common choice
when the data will not be used in calculations. The Text
data type provides for values that have fewer than 256
characters. If you know that a maximum length less
than 255 would be appropriate then you could use the
Field Size property (discussed in the next section) to
limit the maximum length of a text string.
Memo
A designer selects Memo if the field will have character
data but the length might be longer than 255. Memo
allows for a maximum length of 63,999 characters.
Consider the description field of the Course table: could
these be longer than 255 characters?
 Table: Data type
Number
If a field is used for storing values that are used in
numerical calculations (e.g. quantity ordered)
then Number is appropriate. The Field Size
property (properties are discussed later) can be
usedto limit the number of storage locations used
per value.

Date/Time
If a field contains date and or time values, then the
Date/Time data type should be chosen. The
Format property (discussed later) allows you to
control how these values will appear to the user.
 Table: Data type
Currency
If a field will contain monetary values then the
Currency data type should be chosen. This data type
provides for numeric calculations that are accurate to 15
digits to the left of the decimal and 4 digits to the right
of the decimal.

AutoNumber
If you choose AutoNumber MS Access will generate a
value for you when a row is inserted into the table. You
can, via the New Values property, arrange the numbers
to be generated
sequentially or randomly. Often control numbers for
things like orders, invoices, registrations, etc. are
numeric and we can leave it to the system to generate a
next value for us .
 Table: Data type
Yes/No
This data type restricts possible values to yes or
no.

Lookup Wizard
Sometimes you need to restrict values to a list of
known values (e.g. a list of genders: Male,
Female), or to values appearing as primary key
values elsewhere in the database. Consider the
creditHours field – a suitable list could be (1, 2, 3,
6). The Lookup Wizard is a suitable data type for
these situations; when selected the system steps
you through a series of windows where you can
make the appropriate choices.
 Activity
These exercises refer to the Library database.

1. Consider the Member table. The id field was defined with the
AutoNumber datatype. Experiment by adding new members and you will
note that id values increase by 1. Now try deleting the last two members
that you added. If you add those members back in what id values do they
get? Are id values reused?
2. Consider the Book table. Add a field, paperback, that can be used to
indicate whether or not a book is a paperback. Choose the YES/NO
datatype. Save the design and switch to datasheet view. Now you will see
how to enter such values – MS Access provides a box that is to be checked,
or not. You can select (a ‘Yes’) using the mouse or by using the space bar.
You should experiment with this.
 Activity
These exercises refer to the Library database.
3. Consider the Member table. Previously you added a gender field. Open
the Member table in design view and change the datatype for gender to be
Lookup Wizard. The wizard will automatically present 3 successive popup
windows where you will:

i) specify that you are providing the lookup values;

ii) enter the values (Male and Female);
iii) specify that values are to be limited to your list.

Save the table and enter datasheet view so you can test out the datatype
you have just created. You will notice the user sees a drop down list
containing Male and Female, and so the user cannot enter/select an
inappropriate value.
 Table: Properties
Each field must have a data type as discussed above. According to the data
type, MS Access will present to you a set of field properties that you can tailor
for your table.
• Field Size : Data integrity is a serious issue for databases. Setting Field Size
for Text data and Number data is a common thing to do. Often organizations
will limit the data they collect for fields such as last name and first name
(for example, 30 characters). If the data type is Number then values selected
for Field Size are values such as Byte, Integer, Long Integer, etc. These
kinds of values are associated with increasing number of memory locations
used per value. A selection of Byte restricts storage to 1 byte of memory (8
bits), and since the largest positive integer that can be stored in a byte is
255, the values stored in the field are forced to be in the range from 0 to
255.
 Table: Properties
• Format: The Format property is used to customize the way text, number,
dates, and times are displayed to the end user. For instance, selecting
Medium Date causes values like January 14, 2023 to be displayed as 14-
Jan-23; selecting Long Date results in the display January-14-23. If you
have Text data such as department code then you could force the display to
be in capital letters by specifying > as the format code. An interesting
Format specification is @;None. If this is used and if there is no value at all
to display the word None will be displayed to the user.
 Table: Properties
• Input Mask: The Input Mask property is used to force the user to add data
according to some pattern. This is another nice feature to help improve the
overall quality of data added to a database. When the cursor is in the Input
Mask area a ‘builder button’ appears. When you click this button you will see
a list of popular controls. If you were to choose the mask for phone number you
will see the control !(999) 000-0000 appear. As a result of this choice, the user
must enter a 7-digit phone number with an optional 3-digit area code).

• Caption: If there is no caption, then the heading used in displays of data is the
field name. Sometimes the field name is not what you want your users to see.
For example, instead of the heading deptCode above a list of department codes,
you may prefer to use the words Department Code. To accomplish this just
enter such a heading in the caption property for the field.
 Table: Properties
• Default value: If some value for a field is very common then you should
consider setting a default value. For example, if most courses are 3-credit
hour courses then the value 3 can be set as the default for all new courses.

• Validation Rule & Validation Text: If a field has a validation rule then the
rule is tested whenever the user enters data. If the test fails the user is
prompted with a message containing the validation text. A simple use of this
could enforce the credit hours to be less than 10 by entering the rule <10
and the validation text Please enter a value between 0 and 10. Again, this is
a nice feature to improve overall data quality.
 Table: Properties
• Required: Consider the deptName field of the Department table. If a user
enters data for a new department then it is unreasonable for the deptName
field to not have a value. To ensure there will be a value we make the field
required – i.e. we choose Yes for the Required property.

• Indexed: MS Access automatically creates an index (unique – no

duplicates) on a field that is the primary key. A unique index is a special
internal data structure that Access builds to facilitate two things: (1) to
ensure fast access to rows of data when the user specifies a value for such a
field in a query, and (2) to ensure in the case of no duplicates that no two
rows of the indexed table could have the same value for that field. You may
choose to have an index on any field. If a field could have duplicate values
then you must choose an index that allows duplicates.
 Table: Properties
• Show Date Picker: If the data type is Date/Time, then this selection enables
the user to select a date using a picker – a convenient tool for data entry.

• New Values: If the data type is AutoNumber you can use New Values to
specify whether the next value for the field will be the next highest integer,
or if it will be a random integer.
 Activity
In the next two exercises you are working with your University
database.
1. Consider the Department table. In design view, set the deptCode
field to have a length of 4 and use > as the display format. Set the
length of the deptPhone field to be 10, and choose the Phone
Number input mask. Save the table and switch to datasheet view.
Use the table below as a guide and enter data into the Department
table.
 Activity
In the next two exercises you are working with your University database.
2. Create a Course table with attributes for department code, course number, title, short
description, and credit hours. The credit hours field should be numeric with no decimal
places, and the other fields are Text fields. Set the deptCode field to be Text with a
length of 4 so that it matches the properties of deptCode in Department. Later it will be
important that the deptCode field in both Department and Course are defined the same.
Use the table below as a guide and enter data into the Course table.
 Activity
In the next two exercises you are working with your University database.
2. Create a Course table with attributes for department code, course number, title, short
description, and credit hours. The credit hours field should be numeric with no decimal
places, and the other fields are Text fields. Set the deptCode field to be Text with a
length of 4 so that it matches the properties of deptCode in Department. Later it will be
important that the deptCode field in both Department and Course are defined the same.
Use the table below as a guide and enter data into the Course table.