Homework answers / question archive / New Perspectives Access 2019 | Module 2: SAM Project 1a Global Human Resources Consultants IMPORTING TABLES AND MODIFYING TABLES AND TABLE PROPERTIES GETTING STARTED ? Open the file NP_AC19_2a_ FirstLastName _1

New Perspectives Access 2019 | Module 2: SAM Project 1a Global Human Resources Consultants IMPORTING TABLES AND MODIFYING TABLES AND TABLE PROPERTIES GETTING STARTED ? Open the file NP_AC19_2a_ FirstLastName _1

MS Access

Share With

---

New Perspectives Access 2019 | Module 2: SAM Project 1a

Global Human Resources Consultants

IMPORTING TABLES AND MODIFYING TABLES AND TABLE PROPERTIES

GETTING STARTED

?

Open the file

NP_AC19_2a_

FirstLastName

_1.accdb

, available for download from the

SAM website.

?

Save the file as

NP_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_Consultants.txt

?

Support_AC19_2a_Projects.accdb

?

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.

More consultants were recently hired to prepare for a projected increase in business. Use

the 'From File' option within the Import & Link group to import the data from the

Support_AC19_2a_Consultants.txt

support file and

append

it to the

Consultant

table.

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

3.

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

the 'From Database' option within the Import & Link group to import the table

Project

from the

Support_AC19_2a_Projects.accdb

support file into the database. 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

4.

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.

5.

With the

Consultant

table open in Design View, set the

Caption

property as

First Name

for the

FirstName

field and

Last Name

for the

LastName

field. Save the changes and

close the table.

6.

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.)

7.

Open the

Consultant

table in Datasheet View. Apply the

Currency

format to the

Salary

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

8.

With the

Consultant

table still open in Datasheet View,

move

the

Salary

field so that it

appears immediately after the

StartDate

field. Add a

new

field following the

Reside

field

with the field name

SkillCode

and the

Short Text

data type. Save the change and close

the table.

9.

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.

10.

Open the

Consultant

table in Datasheet View.

Delete

the

Mobile

field and save the

change. The

Consultant

table should look like Figure 3.

11.

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.

Figure 3: Updated Consultant Table

12.

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.

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

Skill

table, and create a one-to-many

relationship between the

Skill

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.