New Perspectives Access 2019 | Module 9: SAM Project 1b Ridgedale College CREATING ACTION QUERIES AND MODIFYING THE NAVIGATION PANE   GETTING STARTED Open the file NP_AC19_9b_FirstLastName_1

Subject:MS AccessPrice:19.99 Bought3

Share With

New Perspectives Access 2019 | Module 9: SAM Project 1b

Ridgedale College

CREATING ACTION QUERIES AND MODIFYING THE NAVIGATION PANE

GETTING STARTED

• Open the file NP_AC19_9b_FirstLastName_1.accdb, available for download from the SAM website.
• Save the file as NP_AC19_9b_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. You work in the Admissions Office at Ridgedale College to help track grants awarded to professors as well as teaching assistant assignments by class. You have created an Access database to organize these details. In this project, you will build action and advanced queries. You will also modify the options that affect the Navigation Pane.
   
   You need to create a copy of the Grants table for exclusive use by another department. Copy the Grants table and paste both the structure and the data as a table using the name GrantsBackup into the database.
2. You want to create a new table of only those currently professors in a particular salary range for use by the Research Department. Create a new Make Table query in Design View with the following instructions:
   1. Select all of the fields from the Professors table.
   2. Add criteria to select only those records less than or equal to $50,000 in the Salary field.
   3. Use LowSalaries as the new table name.
      Run the query, which should add three records to the LowSalaries table. Save the query using MakeLowSalaries as the query name and then close it.
3. The Research Department has changed the salary range of the data they need in the LowSalaries table. Create a new Append query in Design View with the following instructions:
   1. Select all of the fields from the Professors table.
   2. Add criteria to select only those records greater than $50,000 and less than or equal to $60,000 in the Salary field.
   3. Append the records to the LowSalaries table.
   4. Run the query, which should append four records into the LowSalaries table. Save the query with the name AppendLowSalaries and then close it.
   5. Open the LowSalaries table to review all seven records as shown in Figure 1. Close the LowSalaries table.

Figure 1: LowSalaries Table in Datasheet View

4. You want to remove some of the records from the Sections table that are no longer used in queries and reports. Create a new Delete query in Design View with the following instructions:
   1. Select all of the fields from the Sections table. 
   2. Add criteria to select only those records with a Semester of Spring and a SchoolYear of 2020. 
   3. Run the query, which should delete 20 records from the Sections table. Save the query with the name DeleteSpring2020 and then close it.
5. You need to apply a salary increase to a subset of records in the Professors table. Create a new Update query in Design View with the following instructions:
   1. Select the Salary field from the Professors table. 
   2. Add criteria so that only those records with a Salary that is less than or equal to $45,000 are selected.
   3. Update the Salary field to increase it by 3% more than its current value.
   4. Run the query to update two records, save it with the name UpdateSalaries, and then close it.
6. Open the UnassignedTAs query in Design View and complete the following:
   1. Delete the criteria row that contains criteria in the SchoolYear field.
   2. Add the TeachingAssistants table to the query and add the TAID, TAFirstName, and TALastName fields from the TeachingAssistants table to the query grid in the fourth, fifth, and sixth columns.
   3. Change the join properties between the two tables to select all records from the TeachingAssistants table. 
   4. Enter Is Null criteria for the ClassNo field to select only those records from the TeachingAssistants table with no matching records in the Sections table. 
      Save the UnassignedTAs query, display it in Datasheet View as shown in Figure 2, and then close it.

Figure 2: UnassignedTAs Query in Datasheet View

7. Change the Navigation Options to show hidden objects and unhide the GrantsByProfessor query.
8. Check the Hidden property for the Hybrid query in the Navigation Pane 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.