Homework answers / question archive / Shelly Cashman Access 2016 | Module 3: SAM Project 1b All Around Outside Maintenance CREATING AND MODIFYING TABLES AND OTHER DATABASE OBJECTS GETTING STARTED ? Open the file SC_AC16_3b_ FirstLastName _1

Shelly Cashman Access 2016 | Module 3: SAM Project 1b All Around Outside Maintenance CREATING AND MODIFYING TABLES AND OTHER DATABASE OBJECTS GETTING STARTED ? Open the file SC_AC16_3b_ FirstLastName _1

MS Access

Share With

---

Shelly Cashman

Access 2016 | Module 3: SAM Project 1b

All Around Outside Maintenance

CREATING AND MODIFYING TABLES AND OTHER DATABASE OBJECTS

GETTING STARTED

?

Open the file

SC_AC16_3b_

FirstLastName

_1.accdb

, available for download

from the SAM website.

?

Save the file as

SC_AC16_3b_

FirstLastName

_2.accdb

by changing the “1” to

a “2”.

o

Hint

: If you do not see the

.accdb

file extension

in the Save As dialog box,

do not type it. Access 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.

Open the Relationships window and add the

Property

table to it. Create a one-

to-many relationship between the

ClientNumber

field in the

Client

table and the

ClientNumber

field in the

Property

table. Make the relationship enforce

referential integrity and cascade update related fields. Do

not

make the

relationship cascade delete related records. Save the relationship.

2.

With the Relationships window still open, modify the relationship between the

Manager

and

Client

tables to cascade update related fields. Do

not

make the

relationship cascade delete related records. Save the relationships and close the

window.

3.

Open the

Client

table in Design View. Use the Lookup Wizard to change the

ClientType

field to a Lookup field. Type in the following three values (in the order

listed) as the list of possible values for the field:

Commercial

,

Corporate

, and

Residential

. Limit the field values to only the items in the list, and do not allow

multiple values for the field.

4.

With the

Client

table still open in Design View, delete the

Services

field.

5.

With the

Client

table still open in Design View, add a new field named

TotalAmount

after the

Balance

field. This field is a calculated field. Use the

expression

AmountPaid + Balance

to calculate the field values. Save and

close the

Client

table.

6.

Open the

Manager

table in Design View and move the

PhoneNumber

field so

that it appears after the

PostalCode

field. Save and close the

Manager

table.

7.

Open the

Property

table in Design View and make the following updates:

a.

Change the

Address

field to a required field.

b.

Change the Field Size property for the

City

field to

25

.

c.

Change the data type of the

CostEstimate

field to

Currency

, and then

change the Decimal Places property to

0

.

d.

Change the default value of the

State

field to

PA

.

Save the changes to the

Property

table. (

Hint

: Because there was a change to a

field size, the “Some data may be lost” warning message will appear. Continue

saving the table. The data fits within the valid ranges, so ignore this warning

and continue saving the table.) Close the table.

8.

Open the

Manager

table in Datasheet View, and then add the Total row to the

table. The Total row should average the values in the

Salary

field. Sort the

records in ascending order by the

LastName

field. Save the changes to the

table, and then close the datasheet.

9.

Create an Update query to update the value of the

CPRCert

field in the

Manager

table for all records to

No

. Run the query, and then save it as

CPR Query

.

(

Hint

: Eleven records will be updated by this query.) Close the query.

10.

Open the

Manager

table in Datasheet View, and then open the subdatasheet for

the Devyn Harlier record (which has the

ManagerID

field value DH01). In the

subdatasheet, update the Claymont Storage Facilities record (which has the

ClientNumber

field value CSS01) by changing the

Balance

field value to

2575.50

. Close the subdatasheet and then close the

Manager

table.

11.

Open the

Property

table in Datasheet View. Find the record for the property in

Chesapeake City (which has the

PropID

field value PCC001) and delete this

record. Close the

Property

table.

12.

Open the

Client

table in Datasheet View, and then apply a Filter by Form to find

all records where the

Balance

field value is equal to

0

and the

City

field value is

equal to

Springfield

. Toggle the filter and, for the record returned, change the

AmountPaid

field value to

2500.25

. Toggle the filter, and then save and close

the table.

13.

Create a Split Form based on the

Property

table. Save the form as

Property

Split Form

, and then close it.

14.

Create a Delete query for the

Client

table. Delete all clients where the

City

field

value is equal to

Malvern

and the

Balance

field value is equal to

0

. Save the

query as

Delete Clients Query

, run the query, and then close the query. (

Hint

:

Only one record will be deleted.)

15.

Create an Append query for the

More Client Types

table. Append the

TypeID

and

the

Description

field values in the

More Client Types

table to the

Client Types

table in the current database. Save the query as

Append Client Types Query

,

run the query, and then close the query. (

Hint

: Two records will be appended.)

16.

Open the

Services Query

in Design View, and then modify it by adding the

multivalued

Services

field to the Design grid so that each options appears on a

separate row. Run the query, save it, and then close the query.

17.

Open the

Client Update Form

in Form View, and then navigate to the last record

(with the

ClientNumber

field value WCM01). Change the

ManagerID

field value

to

RH01

. Navigate to the previous record in the form (with the

ClientNumber

field value WBT01). Change the

PostalCode

field value to

19806

.

18.

Use the

Client Update Form

to add a new record to the

Client

table with the

values shown in Figure 1 below.

Figure 1: Client Update Form

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.