Homework answers / question archive / Illustrated Access 2019 | Module 9: End of Module Project 1 Homestead Partners Creating Action Queries GETTING STARTED Open the file IL_AC19_EOM9-1_FirstLastName_1

Illustrated Access 2019 | Module 9: End of Module Project 1 Homestead Partners Creating Action Queries GETTING STARTED Open the file IL_AC19_EOM9-1_FirstLastName_1

MS Access

Share With

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

Homestead Partners

Creating Action Queries

GETTING STARTED

• Open the file IL_AC19_EOM9-1_FirstLastName_1.accdb, available for download from the SAM website.
• Save the file as IL_AC19_EOM9-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. Copy the Surveys table and paste both the structure and the data back into the database as a table using the name BackupSurveys.
2. Create a new query in Design View and individually select all of the fields from the Surveys table. Add criteria to select only those records with a survey date in June 2021. Change the query into a Make Table query using June2021 as the new table name. Run the query, which should paste eight records into the new table. Save the query with the name MakeJune2021 and then close it. [Hint: The month of June has 30 days. Use the AND keyword in your criteria.]
3. Create a new query in Design View and individually select all of the fields from the Surveys table. Add criteria to select only those records with any date in July 2021. Change the query into an Append query and select the June2021 table to append the records into. Run the query, which should append 46 records into the June2021 table. Save the query with the name AppendJuly2021 and then close it. Rename the June2021 table to be JuneJuly2021 and then open it to review all 54 records as shown in Figure 1. Close the JuneJuly2021 table. [Hint: The month of July has 31 days. Use the AND keyword in your criteria.]

Figure 1: JuneJuly2021 Table in Datasheet View

4. Create a new query in Design View and select all of the fields from the Surveys table. Add criteria to select only those records with any date in June 2021. Change the query into a Delete query. Run the query, which should delete eight records from the Surveys table. Save the query with the name DeleteJune2021 and then close it. [Hint: The month of June has 30 days. Use the AND keyword in your criteria.]
5. Create a new query in Query Design View and select the ManagerID field from the Properties table. Add criteria to select only those records with a ManagerID field value of 3. Change the query into an Update query and update the ManagerID field value to 6. Run the query to update 53 records, save it with the name UpdateManager, and then close it.
6. Open the PropertiesWithNoSurveys query in Query Design View and delete the criteria row that contains criteria for the PropertyNo field so that no criteria exist in the query grid. Add the Surveys table to the query and add the SurveyID field from the Surveys table to the query grid. Change the join properties between the two tables to select all records from the Properties table. Use Is Null criteria for the SurveyID field to select only those properties with no matching records in the Surveys table. Save the query, display it in Datasheet View as shown in Figure 2, and then close it.

Figure 2: PropertiesWithNoSurveys Query in Datasheet View

7. Change the Navigation Options to show hidden objects and unhide the CityListing query.
8. Hide the AppendJuly2021, DeleteJune2021, MakeJune2021, and UpdateManager queries in the Navigation Pane (Hint: Do not hide in group) and then change the Navigation Options to hide hidden objects.

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.