Fill This Form To Receive Instant Help
Fill This Form To Receive Instant Help
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.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.
