New Perspectives Access 2019 | Module 3: End of Module Project 1 Homestead Partners Creating and Improving Queries     GETTING STARTED Open the file NP_AC19_EOM3-1_FirstLastName_1

Subject:MS AccessPrice:20.01 Bought9

Share With

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

Homestead Partners

Creating and Improving Queries

• GETTING STARTED

• Open the file NP_AC19_EOM3-1_FirstLastName_1.accdb, available for download from the SAM website.
• Save the file as NP_AC19_EOM3-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.
• 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.
   
   Open the Properties table in Datasheet view and in the record with the PropertyNo value of 2 for Victor Hogg, insert Georgia for the FName value and Brickley for the LName value. Hide the Baths and Deposit columns that do not yet contain any data.
2. With the Properties table still open in Datasheet view, modify the expression in the calculated ApplicationFee field to multiply by 15 instead of 10. Apply the last filter (the State field is equal to GA). Sort the records in ascending order by the LName field and then save and close the Properties table.
3. Open the Surveys table in Datasheet view and sort the records in descending order by SurveyDate. Change the font size to 12 and apply Arial as the font. Save and close the Surveys table.
4. Open the ManagerListing query in Datasheet view and for PropertyNo 13, change the ManagerID value to 1. Note that the ManagerLastName value will automatically update to Washington due to the relationships between the Managers and Properties tables.
5. Switch the ManagerListing query to Design view and use a wildcard character to select all records where the ManagerLastName starts with the letter W. Save and display the ManagerListing query in Datasheet view as shown in Figure 1 and then close it.

Figure 1: ManagerListing Query in Datasheet View

6. Create a new query in Query Design view using the Properties and StateAbbreviations tables. Join the Properties and StateAbbreviations tables using the State field from the Properties table and the Abbreviation field from the StateAbbreviations table. Add the PropertyNo and StreetAddress fields from the Properties table, and the StateName field from the StateAbbreviations table to the query grid, and in that order. Add an ascending sort order to the PropertyNo field. Save the query with the name PropertyListing, display it in Datasheet view as shown in Figure 2, and then close it.

Figure 2: PropertyListing Query in Datasheet View

7. Open the CityListing query in Design view and add criteria to select all records that have a State field value of GA or FL. Add an ascending sort order to the State and City fields. Save and display the CityListing query in Datasheet view as shown in Figure 3 and then close it.

Figure 3: CityListing Query in Datasheet View

8. Open the HomeStylesListing query in Design view and add the YearBuilt field as the fourth column of the query design grid. Then add criteria to select all records that have a Description field value of Ranch, State field value of KS, and YearBuilt value greater than or equal to 2000. Save and display the HomeStylesListing query in Datasheet view as shown in Figure 4 and then close it.

Figure 4: HomeStylesListing query in Datasheet View

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.