New Perspectives Access 2019 | Module 5: End of Module Project 1 Homestead Partners Modifying field properties and creating queries Open the file NP_AC19_EOM5-1_FirstLastName_1

Subject:MS AccessPrice:19.99 Bought3

Share With

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

Homestead Partners

Modifying field properties and creating queries

• Open the file NP_AC19_EOM5-1_FirstLastName_1.accdb, available for download from the SAM website.
• Save the file as NP_AC19_EOM5-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_EOM5-1_Sunset.jpg
• 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. In this project, you will improve an existing database by modifying field properties in tables and creating different types of queries.
   
   In Design View of the Managers table, below the ReviewDate field, add three new fields.  The first field should be named ManagerEmail with a Hyperlink data type. The second should be named Background with a Long Text data type, and the third should be named Resume with an Attachment data type. Change the Append Only property for the Background field to Yes, then save and close the Managers table.
2. In Design View of the Properties table, set the Caption property for the StreetAddress field to Address, change the Field Size property for the StreetAddress field to 30, and apply the Format property for the DepositDate field to Medium Date. Save the Properties table. (Hint: Select Yes in the Microsoft Access window that appears to let you know that the field size has changed.)
3. While still in Design View of the Properties table, change the Required property for the Deposit field to Yes, apply the Validation Rule property for the Deposit field to >=100, enter Deposit must be greater than or equal to $100 for the Validation Text property for the Deposit field, and then save the Properties table. If prompted to test data integrity rules, click Yes.
4. While still in Design View of the Properties table, use the Lookup Wizard to lookup field values for the State field from the StateAbbreviations table. Select both fields from the StateAbbreviations table, and accept the other default settings in the Lookup Wizard. Save the table if prompted, then modify the List Rows Lookup property for the State field to display 30 rows.
5. Switch to Datasheet View of the Properties table, and use the lookup list to change the State field value from Kansas to Illinois for the first record (PropertyNo 1). When prompted to save the table, click Yes and then close the Properties table.
6. In Design View of the Managers table, add [ReviewDate]>=[HireDate] as the Validation Rule for the table and enter Review date must be greater than or equal to the hire date as the Validation Text property for the table. Save and close the Managers table. Click Yes if prompted to test for data integrity.
7. In Datasheet View of the Surveys table, add the Support_AC19_EOM5-1_Sunset.jpg file in the default folder location to the Attachment field for the second record (SurveyID 2). Add a Total row to the datasheet and average the values in the Rating field. Save and close the Surveys table.
8. In Design View of the ManagerListing query, add Manager as the Caption property to the ManagerLastName field. Add Properties by Manager and State as the Description property for the query. Add the Total row to the query grid and Count the PropertyNo field.  Save and view the query in Datasheet View as shown in Figure 1, and then close it.

 Figure 1: Final ManagerListing Query

 

 

9. Create a new query in Query Design View based on the Properties table. Add the PropertyNo, PropertyType, StreetAddress, City, State, and YearBuilt fields in that order. Sort the records in descending order on the YearBuilt field and return the top 5 records. Add [Enter desired state] as a parameter prompt for the State field. (Hint: Make sure to include brackets when adding [Enter desired State].) Save the query with the name Top5ByState then run the query using the two-character abbreviation for Missouri of MO for the parameter prompt as shown in Figure 2. Close the Top5ByState query.

 Figure 2: Final Top5ByState Query

 

 

10. Create a new query using the Crosstab Query Wizard based on the Properties table. Use State as the row headings field, PropertyType as the column headings field, and Count the PropertyNo field for each column and row intersection. Accept the default name of Properties_Crosstab and view the query in Datasheet View. Save and close the Properties_Crosstab query.
11. Use the Find Unmatched Query Wizard to find records in the StateAbbreviations table that contain no related records in the Properties table. The Abbreviation field in the StateAbbreviations table and the State field in the Properties table contain matching data.  Include both the Abbreviation and StateName fields in the query results. Accept the default name of StateAbbreviations Without Matching Properties and view the results in Datasheet View as shown in Figure 3. Save and close the StateAbbreviations Without Matching Properties query.

 Figure 3: FInal StateAbbreviations Without Matching Properties Query

 

 

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.