Homework answers / question archive / Illustrated Access 2019 | Module 2: End of Module Project 1 Homestead Partners IMPROVING AN ACCESS DATABASE GETTING STARTED ? Open the file IL_AC19_EOM2-1_ FirstLastName _1

Illustrated Access 2019 | Module 2: End of Module Project 1 Homestead Partners IMPROVING AN ACCESS DATABASE GETTING STARTED ? Open the file IL_AC19_EOM2-1_ FirstLastName _1

MS Access

Share With

---

Illustrated Access 2019 | Module 2: End of Module Project 1

Homestead Partners

IMPROVING AN ACCESS DATABASE

GETTING STARTED

?

Open the file

IL_AC19_EOM2-1_

FirstLastName

_1.accdb

, available for download from

the SAM website.

?

Save the file as

IL_AC19_EOM2-1_

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.

?

To complete this SAM Project, you will also need to download and save the following data

files from the SAM website onto your computer:

?

Support_AC19_EOM2-1_StateAbbreviations.xlsx

?

Support_AC19_EOM2-1_SurveyData.xlsx

?

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.

Homestead Partners is a company that connects independent home owners with long-

term renters. You work in the Customer Service department of Homestead Partners as a

data analyst. You are developing an Access database application to help manage and

analyze several types of customer service data including property and property manager

effectiveness.

Import the Excel workbook named

Support_AC19_EOM2-1_SurveyData.xlsx

and

append the records from the

Surveys

worksheet into the existing

Surveys

table. Do not

save the import settings. Open the

Surveys

table in Datasheet view to review the data, a

portion of which is shown in Figure 1, and then close it.

Figure 1: Surveys Table in Datasheet View

2.

Import the Excel workbook named

Support_AC19_EOM2-1_StateAbbreviations.xlsx

as a new table. The first row of the spreadsheet contains column headings. Use the

default Field Options. Set the

Abbreviation

field as the primary key. Name the new table

StateAbbreviations

and do not save the import settings. Open the

StateAbbreviations

table in Datasheet view to review the data, a portion of which is shown in Figure 2, and

then close it.

Figure 2: StateAbbreviations table in Datasheet View

3.

Open the

Properties

table in Design view and change the Field Size of the

StreetAddress

field to

50

from 255. Change the Required property from No to Yes for the

StreetAddress

field. Delete 0 in the Default Value property of the

ManagerID

field. Save the

Properties

table, then use the Input Mask Wizard to set the Input Mask property for the

PrimaryPhone

field using the default values for the Phone Number input mask. (If a

popup appears warning of data integrity loss, click Yes.) Save the

Properties

table. Click

Yes if prompted to test data integrity.

4.

Switch the

Properties

table to Datasheet view, and to the right of the

PrimaryPhone

field,

add a new field with a Currency data type and use

Deposit

as the field name. Move the

PrimaryPhone

field to the second column between the

PropertyNo

and

PropertyType

fields. (Note that the

PrimaryPhone

field currently does not contain any data.) The final

Properties

Table Datasheet is shown in Figure 3. Save and close the

Properties

table.

Figure 3: Properties table in Datasheet View

5.

Open the

Managers

table in Datasheet view and delete the

DateOfBirth

field. Change the

data type for the

SignonBonus

field to Currency. Set the Default Value property to

1000

for the

SignonBonus

field. Change the data type for the

InterviewScore

field to Number.

Click Yes if prompted. Given all field values are numbers, no data will be lost.

6.

While the

Managers

table is open in Datasheet view, decrease the decimal places to

1

for the

RealEstateTestScore

field. Increase the decimal places to

1

for the

CustomerServiceTestScore

field. To the right of the

CustomerServiceTestScore

field, add

a new field named

OverallScore

with a Number data type. The final

Managers

Table

Datasheet view is shown in Figure 4. Save and close the

Managers

table.

Figure 4: Managers table in Datasheet View

7.

In the Navigation pane, use the name

HomeStyles

to rename the

PropertyTypes

table.

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.