Microsoft Office Tutorials and References
In Depth Information
Similarly, examining the Recruiter Address Information in Figure 1–2c on page AC 7
adds the last name, ﬁ rst name, street, city, state, and postal code ﬁ elds to the Recruiter table. In
addition to the recruiter number, last name, and ﬁ rst name, the Recruiter Financial
Information in Figure 1–2d would add the rate and commission. Adding these ﬁ elds to the Recruiter
table and assigning reasonable names gives:
Recruiter ( Recruiter Number , Last Name, First Name, Street, City,
State, Postal Code, Rate, Commission)
Determining and Implementing Relationships Between
Determine relationships among the tables.
The most common type of relationship you will encounter between tables is the one-to-
many relationship . This means that each row in the ﬁ rst table may be associated with many
rows in the second table, but each row in the second table is associated with only one row
in the ﬁ rst. The ﬁ rst table is called the “one” table and the second is called the “many”
table. For example, there may be a relationship between departments and employees, in
which each department can have many employees, but each employee is assigned to only
one department. In this relationship, there would be two tables, Department and Employee.
The Department table would be the “one” table in the relationship. The Employee table
would be the “many” table.
To determine relationships among tables, you can follow these general guidelines:
1. Identify the “one” table.
2. Identify the “many” table.
3. Include the primary key from the “one” table as a ﬁ eld in the “many” table.
According to the requirements, each client has one recruiter, but each recruiter can
have many clients. Thus, the Recruiter table is the “one” table, and the Client table is the
“many” table. To implement this one-to-many relationship between recruiters and clients,
add the Recruiter Number ﬁ eld (the primary key of the Recruiter table) to the Client
table. This produces:
Client (Client Number, Client Name, Street, City, State, Postal Code, Amount Paid,
Current Due, Recruiter Number)
Recruiter ( Recruiter Number , Last Name, First Name, Street, City, State, Postal
Code, Rate, Commission)
Determining Data Types for the Fields
Each ﬁ eld has a data type . This indicates the type of data that can be stored in the
ﬁ eld. Three of the most commonly used data types are:
1. Text — The ﬁ eld can contain any characters. A maximum number of
255 characters is allowed in a ﬁ eld whose data type is Text.
2. Number — The ﬁ eld can contain only numbers. The numbers either can be
positive or negative. Fields are assigned this type so they can be used in arithmetic
operations. Fields that contain numbers but will not be used for arithmetic
operations usually are assigned a data type of Text.
3. Currency — The ﬁ eld can contain only monetary data. The values will appear
with currency symbols, such as dollar signs, commas, and decimal points, and with
To show the symbol for
the Euro ( € ) instead
of the dollar sign, change
the Format property for
the ﬁ eld whose data
type is currency. To
change the default
symbols for currency,
change the settings in
the operating system
using the Control Panel.