New Perspectives Access 2019 | Module 2: End of Module Project 1    New Perspectives Access 2019 | Module 2: End of Module Project 1 Homestead Partners   Improving an Access Database     GETTING STARTED Open the file NP_AC19_EOM2-1_FirstLastName_1

Subject:MS AccessPrice:20.01 Bought13

Share With

 New Perspectives Access 2019 | Module 2: End of Module Project 1

 

New Perspectives Access 2019 | Module 2: End of Module Project 1

Homestead Partners

 

Improving an Access Database

• GETTING STARTED

• Open the file NP_AC19_EOM2-1_FirstLastName_1.accdb, available for download from the SAM website.
• Save the file as NP_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.