Microsoft Office Tutorials and References
In Depth Information
The rows in the tables are called records . A record contains information about a
given person, product, or event. A row in the Client table, for example, contains information
about a speciﬁ c client.
The columns in the tables are called ﬁ elds. A ﬁ eld contains a speciﬁ c piece of
information within a record. In the Client table, for example, the fourth ﬁ eld, City,
contains the city where the client is located.
The ﬁ rst ﬁ eld in the Client table is the Client Number. JSP Recruiters assigns a
number to each client. As is common to the way in which many organizations format
client numbers, JSP Recruiters calls it a number , although it actually contains letters. The
JSP client numbers consist of two uppercase letters followed by a two-digit number.
These numbers are unique; that is, no two clients are assigned the same number.
Such a ﬁ eld can be used as a unique identiﬁ er . This simply means that a given client
number will appear only in a single record in the table. Only one record exists, for
example, in which the client number is BH72. A unique identiﬁ er also is called a primary key .
Thus, the Client Number ﬁ eld is the primary key for the Client table.
The next seven ﬁ elds in the Client table are Client Name, Street, City, State, Postal
Code, Amount Paid, and Current Due. Note that the default width of the columns cuts
off the names of some of the columns. The Amount Paid column contains the amount
that the client has paid JSP Recruiters year to date (YTD) prior to the current period.
The Current Due column contains the amount due to JSP for the current period. For
example, client BL12 is Benton Labs. The address is 12 Mountain in Denton, Colorado.
The postal code is 80412. The client has paid $16,500 for recruiting services so far this
year. The amount due for the current period is $38,225.
JSP assigns each client a single recruiter. The last column in the Client table,
Recruiter Number, gives the number of the client’s recruiter.
The ﬁ rst ﬁ eld in the Recruiter table, Recruiter Number, is the number JSP Recruiters
assigns to the recruiter. These numbers are unique, so Recruiter Number is the primary key
of the Recruiter table.
The other ﬁ elds in the Recruiter table are Last Name, First Name, Street, City,
State, Postal Code, Rate, and Commission. The Rate ﬁ eld contains the percentage of the
client fee that the recruiter earns, and the Commission ﬁ eld contains the total amount
that JSP has paid the recruiter so far this year. For example, Recruiter 27 is Jaime
Fernandez. His address is 265 Maxwell in Charleston, Colorado. The Postal Code is 80380. His
commission rate is .09 (9%), and his commission is $9,450.
The recruiter number appears in both the Client table and the Recruiter table. It
relates clients and recruiters. For example, in the Client table, you see that the recruiter
number for client BL12 is 24. To ﬁ nd the name of this recruiter, look for the row in the
Recruiter table that contains 24 in the Recruiter Number column. After you have found
it, you know the client is assigned to Camden Reeves. To ﬁ nd all the clients assigned to
Camden Reeves, you must look through the Client table for all the clients that contain 24
in the Recruiter Number column. His clients are BH72 (Berls Hospital), BL12 (Benton
Labs), FH22 (Family Health), MH56 (Maun Hospital), and WL56 (West Labs).
The last recruiter in the Recruiter table, Jan Lee, has not been assigned any clients
yet; therefore, her recruiter number, 34, does not appear on any row in the Client table.
As you read this chapter, you will learn how to create the database shown in Figure 1–1
on the previous page by performing these general tasks:
• Design the database.
• Create a new blank database.