Transcript data type

Data Types
and
Field Properties
5.01 Understand database tables used in
business.
It’s All in the Details
Let’s dig a little deeper
into the details of how
data is formatted and
represented in database
tables
5.01 Understand database tables used in business.
Side 2
Data Types and Field Properties

Data types and field properties are used to
format data so that it can be used and managed

The data type defines the value of a cell and
the field property defines the format of the data
within the cell

For example: the data type for a zip code field
is number and the field property is used to
limit the size of the entry to 5 characters
5.01 Understand database tables used in business.
Side 3
Why are data types important?


To control data.
In the example on
the left, the Age data
was sorted in
descending order,
but because the
data type was not
set as Number, the
field cannot be
sorted correctly.
5.01 Understand database tables used in business.

In the example on
the right, the
Adoption data was
correctly sorted in
ascending order
because the data
type for Date was
used.
Side 4
Example of a Data Type

The data
type for the
adoption
date field is
date/time
5.01 Understand database tables used in business.
Side 5
What data types are available?
 Text
 Currency
 Memo
 AutoNumber
 Number
 Yes/No
 Date/time
 Lookup
Wizard
Note: OLE Object and Hyperlink data types are not
addressed in this objective.
5.01 Understand database tables used in business.
Side 6
Data Types
Text


Used for data that
will be sorted in
alphabetical order
or listed randomly,
but will not be used
in mathematical
calculations.
This is the default
setting
Memo

Used for lengthy
entries that allows
combinations of
text and numbers
Data Types
Number

Used for data that
may be calculated,
sorted, or filtered.
Date/time

Used to arrange and sort
data chronologically,
often used with the input
mask field property
Data Types
Currency

Used to format
numbers as decimal
values with a dollar
sign. The default
format for currency is
two decimal places.
Auto Number

Used to automatically
assign a number to
each record and to
assist in preventing
duplication of data.
Data Types
Yes/No

A data type in toggle
format that allows a
user to select a yes or
no value in a
database cell. The
default setting is No.
Lookup Wizard

Used to define preset
values for database
entries. This feature
increases productivity
and accuracy by
reducing the amount
of data entry required
When to use data types:
 Think
of data types
and field properties
as tools that
increase the power
of the database.
 The default format
for all data types is
text.
5.01 Understand database tables used in business.
 Use
a different
data type if you
need to control
how the data in a
particular field will
be searched,
sorted, or used in
calculations.
Side 11
Why are field properties
important?
 To
control how data is
entered in a field
 Requiring the same
format for all of the
phone numbers is a
way to ensure
accuracy
In this example, an Input Mask field property was used to
force the user to enter the telephone number in a specific
format.
5.01 Understand database tables used in business.
Side 12
Example of a Field Property
Actual
Data
5.01 Understand database tables used in business.

An input mask
field property is
used for the
Adoption Date
field to make sure
the date is
entered correctly
Side 13
Field Properties:




Field Size
Format
Input mask
Caption
Note: Only 4 field properties are mentioned and
addressed in this objective.
5.01 Understand database tables used in business.
Side 14
When to set field properties:
 Use
a field property if you want to
specify how the data is entered, what
data can be entered, and how the data
will appear.
5.01 Understand database tables used in business.
Side 15
Field Properties
Field Size

to control the size of
the field, measured in
characters
Format

Used to control the
layout of the field,
such as one or two
decimal places
Field Properties
Input Mask

a template-like field
property that regulates
how data is entered in
a cell. For example, if
the phone number input
mask is used, the user
will be prompted to
input data in a specific
format, such as (919)
555-5555.
Caption
Used for column naming
that allows a more userfriendly and properly
formatted field name than
the field name stored in
the database design
Steps in Creating a Database
1.
Plan the structure of the database (sketch on paper)
a)
b)
c)
How will the data be grouped?
What will the field names be?
What will you name the database?
Open and save a new database
3. Create a table
4. Enter the field names
2.
a)
b)
5.
Set the data types
Set the field properties
Enter the data
5.01 Understand database tables used in business.
Side 18