Illustrated Access 2019 | Module 2: SAM Project 1a

Global Human Resources Consultants

IMPORTING TABLES AND MODIFYING TABLES AND TABLE PROPERTIES

GETTING STARTED

?

Open the file

IL_AC19_2a_

FirstLastName

_1.accdb

, available for download from the

SAM website.

?

Save the file as

IL_AC19_2a_

FirstLastName

_2.accdb

by changing the “1” to a “2”.

?

If you do not see the .accdb file extension in the Save As dialog box, do not type it. The

program will add the file extension for you automatically.

?

To complete this SAM Project, you will also need to download and save the following data

files from the SAM website onto your computer:

?

Support_AC19_2a_Clients.xlsx

?

Support_AC19_2a_Projects.xlsx

?

Open the

_GradingInfoTable

table and ensure that your first and last name is displayed

as the first record in the table. If the table does not contain your name, delete the file

and download a new copy from the SAM website.

PROJECT STEPS

1.

Most of the data that you need for the Global Human Resources Consultants database is

currently stored in Excel workbooks. Use the Import Spreadsheet Wizard to import the

data from the

Support_AC19_2a_Clients.xlsx

support file and

append

it to the

Client

table. Do not analyze the table after importing the data and do not save the import

steps. Open the

Client

table in Datasheet View. It should look like Figure 1. Close the

Client

table.

Figure 1: Updated Client Table

2.

Consultants work on projects, such as creating websites for internal communication. Use

the Import Spreadsheet Wizard to import the data from the

Support_AC19_2a_Projects.xlsx

support file as a new table. The first row contains a

column heading, but no other change needs to be made to the field. Let Access add a

primary key to the table. Name the table

Project

and do not save the import steps.

Open the

Project

table in Datasheet View. It should look like Figure 2. Close the

Project

table.

Figure 2: New Project Table

3.

Open the

Consultant

table in Design View. Consultants can reside in any country, but

many of the consultants reside in the United States. Change the

Default Value

property

for the

Reside

field to

USA

. Save the change but do not close the table.

4.

With the

Consultant

table open in Design View, change the

Input Mask

property for the

StartDate

field to

Medium Date

. Save the change and close the table.

5.

Open the

Client

table in Design View. Change the

Required

property for the

ClientName

field to

Yes

. Save the change to the

Client

table and then close the table. (

Hint

: Because

you changed the Required property, the "Data Integrity rules have been changed"

warning message appears. The data is valid, so click No, and continue saving the table.)

6.

Open the

Consultant

table in Datasheet View. Apply the

Currency

format to the

Salary

field. Save the change but do not close the table.

7.

With the

Consultant

table still open in Datasheet View,

move

the

Salary

field so that it

appears immediately after the

StartDate

field. Save the change and close the table.

8.

Open the

Project

table in Datasheet View and make the following changes:

a.

Resize

the

Description

field so that all field values are completely visible.

b.

Add a

new

field following the

Description

field with the field name

Months

and the

Number

data type.

Save the changes and close the table.

9.

Open the

Consultant

table in Datasheet View.

Delete

the

Mobile

field and save the

change. The

Consultant

table should look like Figure 3.

Figure 3: Updated Consultant Table

10.

With the

Consultant

table still open in Datasheet View,

decrease

the number of decimal

places for the

Salary

field to

0

. Save the change and close the table.

11.

Open the

Project

table in Design View and make the following changes to the

Months

field:

a.

Change the

default

value to

3

.

b.

Increase

the number of decimal places to

2

.

Save the changes and close the table.

12.

In the Navigation Pane, rename the

Skill

table, using

StandardSkill

as the name.

13.

Open the

Project

table in Design View and add a new field below the

Months

field named

ConsultantID

with a

Number

data type. This field will serve as the foreign key field to

allow one record in the

Consultant

table to link to many records in the

Project

table.

Save

and close the

Project

table.

14.

Open the Relationships window, add the

Project

table and the

Consultant

table, and

create a one-to-many relationship between the

Consultant

and

Project

tables using the

common

ConsultantID

field. Enforce referential integrity on the relationship.

15.

With the Relationships window still open, add the

StandardSkill

table, and create a one-

to-many relationship between the

StandardSkill

table and

Consultant

table using the

common

SkillCode

field. Enforce referential integrity on the relationship. The

Relationships window should look like Figure 4. Save and close the Relationships window.

Figure 4: Final Relationships Window

Save and close any open objects in your database. Compact and repair your database, close it,

and then exit Access. Follow the directions on the SAM website to submit your completed project.

pur-new-sol

Related Questions