Homework answers / question archive / New Perspectives Access 2019 | Module 9: SAM Project 1a Global Human Resources Consultants CREATING ACTION QUERIES AND MODIFYING THE NAVIGATION PANE GETTING STARTED ? Open the file NP_AC19_9a_ FirstLastName _1

New Perspectives Access 2019 | Module 9: SAM Project 1a Global Human Resources Consultants CREATING ACTION QUERIES AND MODIFYING THE NAVIGATION PANE GETTING STARTED ? Open the file NP_AC19_9a_ FirstLastName _1

MS Access

Share With

---

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

Global Human Resources Consultants

CREATING ACTION QUERIES AND MODIFYING THE NAVIGATION PANE

GETTING STARTED

?

Open the file

NP_AC19_9a_

FirstLastName

_1.accdb

, available for download from the

SAM website.

?

Save the file as

NP_AC19_9a_

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.

?

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.

You work in the software division of Global Human Resources Consultants (GHRC) which

sells modular Human Resource (HR) software to large international companies. For high

level planning purposes you havecreated an Access database to track new clients, the

HR software modules they have purchased, and the lead consultant for each installation.

In this project you will build action and advanced queries.

You will also modify the

options that affect the Navigation Pane.

Copy the

Client

table and paste both the structure and data as a table using the name

ClientBackup

into the database.

2.

Create a new Make Table query in Design View with the following instructions:

a.

Select all of the fields from the from the

Consultant

table.

b.

Add criteria to select only those records

greater than or equal to $80,000

in the

Salary

field.

c.

Use

HighSalaries

as the new table name.

d.

Run the query which should add three records to the

HighSalaries

table.

Save the

query using

MakeHighSalaries

as the query name and then close it.

3.

Create a new Append query in Design View with the following instructions:

a.

Select all of the fields from the from the

Consultant

table.

b.

Add criteria to select only those records

greater than or equal to $70,000 and

less than $80,000

in the

Salary

field.

c.

Append the records to the

HighSalaries

table.

d.

Run the query which should append 13 records into the

HighSalaries

table. Save

the query with the name

AppendHighSalaries

and then close it.

e.

Open the

HighSalaries

table to review all 16 records as shown in Figure 1. Close the

HighSalaries

table.

Figure 1: HighSalaries table in Datasheet View

4.

Create a new Delete query in Design View with the following instructions:

a.

Select all of the fields from the

Client

table.

b.

Add criteria to select only those records with a

ContractSigned

date

prior to

7/1/2021

.

c.

Run the query which should delete three records from the

Client

table.

Save the

query with the name

DeleteOldContracts

and then close it.

5.

Create a new Update query in Design view with the following instructions:

a.

Select the

ComponentName

,

InstallationFee,

and

MonthlyFee

fields from the

SoftwareComponent

table.

b.

Add criteria so only those records with an

InstallationFee

that are

greater than or

equal to 2000

are selected.

c.

Update both the

InstallationFee

and

MonthlyFee

fields to

increase them by 10%

more than their current values.

d.

Run the query to update eight records, save it with the name

UpdateFees

and

then close it.

6.

Open the

UnsoldComponents

query in Design View and complete the following:

a.

Delete the criteria row that contains criteria in the

InstallationFee

field.

b.

Add the

ClientSoftware

table to the query and add the

ClientID

field from the

ClientSoftware

table to the query grid in the fourth column.

c.

Change the join properties between the two tables to select all records from the

SoftwareComponent

table.

d.

Enter

Is Null

criteria for the

ClientID

field to select only those records from the

SoftwareComponent

table with no matching records in the

ClientSoftware

table.

Save the

UnsoldComponents

query, display it in Datasheet View

as shown in Figure

2, and then close it.

Figure 2: UnsoldComponents Query in Datasheet View

7.

Change the Navigation Options to show hidden objects and unhide the

Revenue

query.

8.

Hide the

ConsultantSalaries

query in the Navigation Pane and then change the

Navigation Options to hide hidden objects.

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.