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”. o • 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 longterm 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. New Perspectives Access 2019 | Module 3: End of Module Project 1 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. New Perspectives Access 2019 | Module 3: End of Module Project 1 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. New Perspectives Access 2019 | Module 3: End of Module Project 1 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. New Perspectives Access 2019 | Module 3: End of Module Project 1 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. New Perspectives Access 2019 | Module 4: End of Module Project 1 Homestead Partners CREATING AND MODIFYING REPORTS GETTING STARTED • Open the file NP_AC19_EOM4-1_FirstLastName_1.accdb, available for download from the SAM website. • Save the file as NP_AC19_EOM4-1_FirstLastName_2.accdb by changing the “1” to a “2”. o • 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 longterm 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. Create a new form using the Form Wizard based on all the fields in the Managers table. Use a Columnar layout, use ManagerEntry as the title for the form, open the form to view information, and then close it. 2. Open the PropertyEntry form in Form View and edit the Year Built value for the first record (Property No 1) to 1985 from 1975. 3. With the PropertyEntry form still open in Form View, enter a new record with the field values shown in Table 1. Table 1: Record for PropertyEntry Form Property No Property Type Street Address Owner First Owner Last City State Zip Manager Year Built 4. (AutoNumber will automatically number the record) 2 101 High St Javier Moran Kansas City MO 66220 4 2018 Switch the PropertyEntry form to Layout View, then move the Close command button from the left side to the right side of the Form Footer section. Position the button so that the right edge is aligned with the right edge of the Year Built textbox in the form detail section above. Save the PropertyEntry form. New Perspectives Access 2019 | Module 4: End of Module Project 1 5. With the PropertyEntry form still open in Layout View, select the Property Entry Form label in the Form Header section, and apply bold styling. 6. With the PropertyEntry form still open in Layout View, delete the Homestead Partners label in the Form Footer section. Edit the Zip label, using ZIP Code as the new value. 7. With the PropertyEntry form still open in Layout View, position the Baths label and text box just below the Year Built label and text box. Display the PropertyEntry form in Form View as shown in Figure 1 and then save and close the PropertyEntry form. New Perspectives Access 2019 | Module 4: End of Module Project 1 Figure 1: PropertyEntry form in Form View 8. Open the HomeStyles form in Layout View, select the TypeID label and Description label in the Detail section, and change the font color to Automatic (Black). (Hint: Be careful to select the labels and not the text boxes.) Save the HomeStyles form. Display the New Perspectives Access 2019 | Module 4: End of Module Project 1 HomeStyles form in Form View as shown in Figure 3, then close the HomeStyles form. Figure 2: HomeStyles Form in Form 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. New Perspectives Access 2019 | Module 2: End of Module Project 2 Cardiac Rehab Associates BUILDING AN ACCESS DATABASE GETTING STARTED • Open the file NP_AC19_EOM2-2_FirstLastName_1.accdb, available for download from the SAM website. • Save the file as NP_AC19_EOM2-2_FirstLastName_2.accdb by changing the “1” to a “2”. o • • 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: o Support_AC19_EOM2-2_InsuranceCompanies.xlsx o Support_AC19_EOM2-2_NewClients.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. Cardiac Rehab Associates is a company that helps people rehabilitate their health and strength after an illness or accident. You work as a research analyst to maintain records and to analyze the success of various activities and programs. At Cardiac Rehab Associates you are developing an Access database application to help manage this information. Open the Enrollments table in Design view and change the Default Value property for the ProgramID field to 1 from 0. Change the Field Size property for the ClientID field to 5 from 255. Save the table. Click Yes if prompted about lost data. Given all ClientID values are five characters or less, no data will be lost. 2. With the Enrollments table open in Design view use the Input Mask Wizard to set the Input Mask property for the EnrollmentDate field using the default values for the Short Date input mask. Change the Required property for the EnrollmentDate field to Yes from No and then save and close the Enrollments table. Click Yes if prompted to test data integrity. 3. In the Navigation pane, use the name Programs to rename the HealthPrograms table. 4. Open the Programs table in Datasheet view and to the right of the ProgramStartDate field, add a new field with a Currency data type and a field name of Fee. Move the ProgramStartDate field to the second column between the ID and ProgramName fields. The final Programs table is shown in Datasheet view in Figure 1. Save and close the Programs table. New Perspectives Access 2019 | Module 2: End of Module Project 2 Figure 1: Programs table in Datasheet View 5. Use the Import Spreadsheet Wizard to add data to the Clients table from Support_AC19_EOM2-2_NewClients.xlsx and select the option to append a copy of the records from the Clients worksheet into the existing Clients table. Do not save the import settings. Open the Clients table in Datasheet view to review the data, a portion of which is shown in Figure 2. Figure 2: Clients table in Datasheet View 6. With the Clients table still open in Datasheet view, delete the MiddleInitial field. Change the data type for the InitiationFee field to Currency. Apply 50 as the Default Value property for the InitiationFee field. Change the data type for the Veteran field to Yes/No. Click Yes if prompted. Given no data has been recorded in that field yet, no data will be lost. 7. With the Clients table in Datasheet view, decrease the decimal places to 1 for the FlexibilityScore field. Increase the decimal places to 1 for the StrengthScore field. To the right of the FlexibilityScore field, add a new field named CognitiveScore with a Number data type. Resize to best fit the column containing the newly created CognitiveScore field. A portion of the final Clients Table Datasheet is shown in Figure 3. Save and close the Clients table. New Perspectives Access 2019 | Module 2: End of Module Project 2 Figure 3: Clients table in Datasheet View 8. Import the Excel workbook named Support_AC19_EOM22_InsuranceCompanies.xlsx as a new table. The first row of the spreadsheet contains column headings. Use the default Field Options. Apply the InsuranceCode field as the primary key. Name the new table InsuranceCompanies and do not save the import settings. Open the InsuranceCompanies table in Datasheet view to review the data, a portion of which is shown in Figure 4, and then close it. Figure 4: InsuranceCompanies table 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. New Perspectives Access 2019 | Module 1: End of Module Project 1 Homestead Partners BUILDING AN ACCESS DATABASE GETTING STARTED • Open the file NP_AC19_EOM1-1_FirstLastName_1.accdb, available for download from the SAM website. • Save the file as NP_AC19_EOM1-1_FirstLastName_2.accdb by changing the “1” to a “2”. o • 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 firm 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 customer satisfaction survey data on the properties rented through your company. In this project you will improve an existing database by creating and modifying fields and tables, queries, forms, and reports. Open the Properties table in Datasheet view and use StreetAddress to rename the current Address field. Close the Properties table. 2. Create a new table in Datasheet view. Change the ID field name to ManagerID with the AutoNumber data type, add a field named ManagerFirstName with the Short Text data type, add a field named ManagerLastName with the Short Text data type, add a field named HireDate with the Date/Time data type, and add a field named Salary with the Currency data type. Save the table with the name Managers and close it. 3. Open the PropertyTypes table in Datasheet view and then add a new record with Log Cabin as the Description field value, noting that the ID field automatically increments to 7. Close the PropertyTypes table. 4. Open the Properties table in Datasheet view and delete the record for PropertyNo 4 (5817 Nall Ave, Sid, Ortiz, Des Moines, IA). Modify the FName value for the record with PropertyNo 2 (4106 W 67th Ter, Victorina, Hogg, Prairie Valley, KS), using Victor as the new FName value, and then close the Properties table. 5. Create a new query using the Simple Query Wizard. Choose the PropertyType, City, State, and YearBuilt fields from the Properties table. Title the new query with the name CityListing, open the query to view information, a portion of which is shown in Figure 1, and then close it. New Perspectives Access 2019 | Module 1: End of Module Project 1 Figure 1: CityListing Query 6. In the Navigation pane, rename the Property form using PropertyEntry as the new form name. 7. Create a new form using the Form Wizard. Choose the FName, LName, City, State, and Zip fields in the Properties table. Choose a Columnar layout. Title the form OwnerInfo, open the form to view information as shown in Figure 2, and then close it. Figure 2: OwnerInfo Form New Perspectives Access 2019 | Module 1: End of Module Project 1 8. Create a new report using the Report Wizard. Choose the PropertyNo, City, State, and YearBuilt fields from the Properties table. Do not add any grouping levels. Sort the records in ascending order by State and then in ascending order by City. Use a Tabular layout and a Portrait orientation. Title the report PropertiesByState, preview the report, a portion of which is shown in Figure 3, and then close it. Figure 3: PropertiesByState Report 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. InsuranceCodeCompanyName AET Aetna HUM Humana BCB Blue Cross Blue Shield KAI Kaiser Permanente UHO United Health One WEL Wellpoint CIG Cigna HIG Highmark CAR Carefirst MET Metropolitan ClientID 99910 99911 99912 99913 99914 FirstName Darcy Mark Terry Henry Roberta LastName DateOfBirth Wilberforce 3/26/1988 Starsky 1/12/1912 Hutch 6/3/1965 Green 6/4/1956 Good 5/10/1944 Gender F M F M F StartDate StrengthScore FlexibilityScore Veteran 8/15/2018 2,00 3,00 FALSE 8/4/2018 3,00 4,00 FALSE 8/3/2018 4,00 5,00 FALSE 8/28/2018 5,00 6,00 FALSE 8/15/2018 6,00 7,00 FALSE InitiationFee 
Purchase answer to see full attachment