Homework answers / question archive / New Perspectives Access 2019 | Module 5: SAM Project 1a Global Human Resources Consultants MODIFYING TABLES AND QUERIES GETTING STARTED ? Open the file NP_AC19_5a_ FirstLastName _1

New Perspectives Access 2019 | Module 5: SAM Project 1a Global Human Resources Consultants MODIFYING TABLES AND QUERIES GETTING STARTED ? Open the file NP_AC19_5a_ FirstLastName _1

MS Access

Share With

---

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

Global Human Resources Consultants

MODIFYING TABLES AND QUERIES

GETTING STARTED

?

Open the file

NP_AC19_5a_

FirstLastName

_1.accdb

, available for download from the

SAM website.

?

Save the file as

NP_AC19_5a_

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 have created 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 improve the tables and queries of the database.

In Design View of the

Client

table, add three new fields with the following specifications:

a.

A field named

Website

with a Hyperlink data type.

b.

A field named

Logo

with an Attachment data type.

c.

A field named

Notes

with a Long Text data type.

d.

For the

Notes

field, use

Yes

as the value for the Append Only property value.

Save the

Client

table.

2.

While still in Design View of the

Client

table, modify the following field properties:

a.

Enter

Client

as

the Caption property for the

ClientName

field.

b.

Enter

30

as the Field Size property for the

ClientName

field.

c.

Choose

Medium Date

as the Format property for the

ContractSigned

and

ProjectStart

fields.

Save the

Client

table. If prompted that some data may be lost, click Yes to

continue. (

Hint

: No data is lost because no values exceed 30 characters in the

ClientName

field.)

3.

While still in Design View of the

Client

table, modify the following field properties:

a.

Choose

Yes

as the Required property for the

Employees

field.

b.

Enter

>=500

as the Validation Rule property for the

Employees

field.

c.

Enter

Minimum is 500

for the Validation Text property for the

Employees

field,

and then save the

Client

table. If prompted to test data integrity rules, click Yes.

4.

While still in Design View of the

Client

table, add a new field immediately below the

existing fields using the Lookup Wizard and the following information:

a.

Enter

CountryCode

as the field name.

b.

Get the values from the

Country

table.

c.

Select both the

CountryCode

and

CountryName

fields.

d.

Sort the records in

ascending

order by the

CountryName

field.

e.

Do not hide the key column.

f.

Store the

CountryCode

value, use the other default options of the Lookup Wizard,

and save the

Client

table as prompted.

g.

Use

Yes

for the Column Heads Lookup property for new

CountryCode

field.

Save the

Client

table.

5.

Switch to Datasheet View of the

Client

table, and select

DEU

(Germany) as the

CountryCode

value for the first record (

ClientID 1 Biolane Products

).

6.

Switch to Design View of the

Client

table and modify these table properties:

a.

Add

[ProjectStart]>=[ContractSigned]

as the Validation Rule for the table.

b.

Enter

Contract must be signed before the project start date

as the

Validation Text property for the table.

Save the

Client

table. Click Yes if prompted to test for data integrity.

7.

Switch to Datasheet View of the

Client

table, add a Total row to the datasheet, and sum

the value in the

Employees

field.

Save and close the

Client

table.

8.

In Design View of the

Revenue

query, complete the following modifications:

a.

Enter

Software Module

as the Caption property to the

ComponentName

field.

b.

Add the Total row to the query grid, count the

ClientName

field, and sum the

InstallationFee

and

MonthlyFee

fields.

c.

Save and view the query in Datasheet View, and then widen each of the four

columns to

22

.

d.

Add a Total row to the datasheet and sum the

CountofClientName

,

SumOfInstallationFee

, and

SumOfMonthlyFee

as shown in Figure 1.

Save and close the

Revenue

query.

Figure 1: Final Revenue Query in Datasheet View

9.

Create a new query in Query Design View based on the

Client

table with the following

details:

a.

Add the

ClientName

and

Employees

fields in that order.

b.

Sort the records in

descending

order on the

Employees

field.

c.

Return the top

5

records.

d.

Add

<[Enter maximum number of employees]

as a parameter prompt for the

Employees

field.

e.

Save the query and use

EmployeeParameter

as the query name.

f.

Run the query using

2000

as the parameter prompt as shown in Figure 2.

Close the

EmployeeParameter

query.

Figure 2: Final EmployeeParameter Query in Datasheet View

10.

Create a new parameter query in Design View based on the Consultant table with the

following details:

a.

Add the

ConsultantID

,

FirstName

,

LastName

, and

Reside

fields to the query grid in

that order.

b.

Right-click the LastName criteria box, and then open the zoom dialog box. Enter

the following in the zoom dialog box:

Like [Enter Consultant Last Name] & "*"

c.

Close the Zoom dialog box by clicking

OK

. Save the query using

ConsultantParameter

as the name and run the query to test it. Enter

Co

in the

parameter prompt and note that two records are returned. Close the query.

11.

Create a crosstab query, using the Query Wizard, based on the

SoftwareComponentsByClient

query with the following options:

a.

Add the

ClientName

field as the Row Heading field.

b.

Add the

SoftwareCode

as the Column Heading field.

c.

Count the

ComponentName

field as the Value field.

Save the query with the default name

SoftwareComponentsByClient_Crosstab

and close the query.

12.

Create a new query in Query Design View, based on the

Consultant

table with the

following details:

a.

Add the

Reside

and

Salary

fields in that order.

b.

Add the Total row to the query grid and average the

Salary

field.

c.

Use the 'Not' keyword to add criteria to the

Reside

field to return consultants

located outside of the USA.

d.

Change the format of the

Salary

field to the

Currency

format.

e.

Save the query and use

InternationalAvgSalaryByCountry

as the query name.

f.

Run the query.

Close the

InternationalAvgSalaryByCountry

query.

13.

Create a new query in Query Design View with the following details:

a.

Add the

Client

and

ClientSoftware

tables.

b.

Add the

ClientName

and

ProjectStart

fields from the

Client

table and the

SoftwareCode

field from the

ClientSoftware

table.

c.

Set the criteria for the

SoftwareCode

field (using the 'in' keyword) to only include

the software codes

ONBD

,

RECR

, and

SCRE.

d.

Using the expression builder, create a new field named

IsCorp.

e.

Within the expression builder, use the

iif

function to populate the

IsCorp

with 'True'

if the client name contains 'Corporation' and 'False' if not.

f.

Save the query as

CorporateNewHireClients

and run the query.

Close the

CorporateNewHireClients

query.

14.

Use the Find Unmatched Query Wizard to find records in the

Consultant

table that

contain no related records in the

Client

table using the following details:

a.

The

ConsultantID

field in each table contains matching data.

b.

Include the

ConsultantID

,

FirstName

, and

LastName

fields in the query results.

c.

Use

UnassignedConsultants

as the query name, and view the results in Query

Datasheet View as shown in Figure 3.

Figure 3: Final UnassignedConsultants Query in Datasheet View

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.