New Perspectives Access 2019 | Module 3: SAM Project 1a Global Human Resources Consultants CREATING QUERIES, SORTING RECORDS, CHANGING THE APPEARANCE OF THE DATASHEET GETTING STARTED ? Open the file NP_AC19_3a_ FirstLastName _1

Subject:MS AccessPrice:19.99 Bought4

Share With

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

Global Human Resources Consultants

CREATING QUERIES, SORTING RECORDS, CHANGING THE APPEARANCE OF THE DATASHEET

GETTING STARTED

?

Open the file

NP_AC19_3a_

FirstLastName

_1.accdb

, available for download from the

SAM website.

?

Save the file as

NP_AC19_3a_

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.

As project manager, you need to be able to query the database to help make decisions

and answer questions from other managers. Create a query based on the

Consultant

table in Query Design View with the following options:

a.

Add the

ConsultantID

,

LastName

, and

FirstName

fields to the design grid in that

order.

b.

Sort

the records in

ascending

order by

LastName

.

c.

Save the query with the name

LastNameSorted

.

Open the query in Datasheet View and then close it.

2.

Open the

ResideCriteria

query in Design View and make the following changes to the

query:

a.

Delete

the

ConsultantID

column from the design grid.

b.

Add criteria to select only those records where the

Reside

field value equals

USA

.

c.

Save the changes to the

ResideCriteria

query.

Open the query in Datasheet View and then close it.

3.

Open the

PaidOrBalance

query in Design View and add criteria to select only those

records where the

Paid

field value

equals

0

or the

Balance

field value

equals

0

. Save

the changes to the query. Open the query in Datasheet View, confirm that four records

appear in the

PaidOrBalance

query results, and then close the query.

4.

Open the

ComparisonSalary

query in Design View and make the following changes to the

query:

a.

Add

the

FirstName

field to the query design grid. The

FirstName

field should

immediately follow the

ConsultantID

field.

b.

Add criteria to select only those records where the

Salary

field value is

greater

than 70,000

.

c.

Sort the records by

LastName

in

descending

order.

d.

Run the query.

e.

With the query open in Datasheet view, edit the first record by changing the

FirstName

value to

Melodie

from Melodee.

Confirm that the query results match Figure 1. Save the query again and close it.

Figure 1: ComparisonSalary Query Results

5.

Open the

ResideLastName

query in Design View and make the following changes:

a.

Move the

Reside

field to the beginning of the design grid so that the order of the

fields in the grid is

Reside

,

LastName

,

FirstName

,

StartDate

.

b.

Sort

the records in

descending

order by the

Reside

field and in

ascending

order

by

LastName

.

Save the changes to the query. Open the query in Datasheet View and confirm that

it matches Figure 2. Close the query.

Figure 2: ResideLastName Query Results

6.

Open the

BalanceAndPaid

query in Design View and add criteria to select only those

records where the

Paid

field value

equals

0

and the

Balance

field value

equals

0

. Save

the changes to the query. Open the

BalanceAndPaid

query in Datasheet View, confirm

that one record appears in the query results, and then close the query.

7.

Open the

DateTime

query in Design View and add criteria to select only those records

where the

StartDate

field value is

greater than 1/1/2019

. Save the changes to the

query. Open the query in Datasheet View, confirm that 15 records appear in the query

results, and then close the query.

8.

Many queries require data from more than one table. For example, you may want the full

country name to display rather than the country abbreviation. Create a query in Design

View based on the

Consultant

and

Country

tables with the following options:

a.

Add the

Consultant

table and the

Country

table to the design window.

b.

Add the

FirstName

and

LastName

fields from the

Consultant

table to the design

grid.

c.

Add the

CountryName

field from the

Country

table to the design grid.

d.

Join

the

Consultant

table and the

Country

table using the

Reside

field in the

Consultant

table and the

CountryCode

field in the

Country

table.

e.

Save the query, using

Country-Consultant

as the name.

Open the query in Datasheet View and then close it.

9.

Open the

ProjectMonths

query in Design View and add criteria to select only those

records where the

Months

field value equals

2

. Save the changes to the query. Open the

query in Datasheet View, confirm that two records appear in the query results, and then

close the query.

10.

Open the

EstMaxMonths

query in Design View. Modify the query by creating a calculated

field. Enter

MaxMonths: [Months] + 3

in the Zoom dialog box of the first empty

column in the design grid. Save the query. Open the query in Datasheet View and then

close the query.

11.

Open the

Consultant

table in Datasheet View. Use the

Find and Replace

feature to find

the consultant whose first name is

Georgeanna

and replace the name, using

Gina

as

the new value. Close the

Consultant

table.

12.

Open the

Country

table in Datasheet View and make the following changes:

a.

Change the font to

Arial

.

b.

Change the font size to

10

.

c.

Sort the records in the table in

ascending

order by

CountryName

.

Save the changes to the table and close it.

13.

Open the

Client

table in Datasheet View. Select the

Calculation

field and modify the

expression by

changing

the plus (+) sign in the calculation to a

minus (-) sign

. Save

the changes to the table and close it.

14.

Open the

Skill

table in Datasheet View.

Sort

the records in

descending

order by

SkillName

. Save the changes to the table and close it.

15.

Open the

Consultant

table in Datasheet View and

hide

the

Reside

and

Salary

columns.

Save the changes to the table and close it.

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.