New Perspectives Access 2019 | Module 9: SAM Critical Thinking Project 1c Global Human Resources Consultants CREATING ACTION QUERIES AND MODIFYING THE NAVIGATION PANE GETTING STARTED Open the file NP_AC19_CT9c_FirstLastName_1

Subject:MS AccessPrice:19.99 Bought4

Share With

New Perspectives Access 2019 | Module 9: SAM Critical Thinking Project 1c

Global Human Resources Consultants

CREATING ACTION QUERIES AND MODIFYING THE NAVIGATION PANE

• GETTING STARTED

• Open the file NP_AC19_CT9c_FirstLastName_1.accdb, available for download from the SAM website.
• Save the file as NP_AC19_CT9c_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 software division of Global Human Resources Consultants (GHRC), which sells modular human resources (HR) software to large international companies.For high-level planning purposes, you havecreated an Access database to track new clients, the HR software modules they have purchased, and the lead consultant for each installation. In this project, you will build action and advanced queries. You will also modify the options that affect the Navigation Pane.
   
   Copy the Client table, paste both the structure and the data into the database as a table, and use ClientBackup as the table name.
2. Create a new Make Table query as follows:
   1. Select all of the fields from the Consultant table. 
   2. Add criteria to select only those records with salaries greater than or equal to $80,000.
   3. Use HighSalaries as the new table name.
   4. Run the query, which should add three records to the HighSalaries table. Save the query using MakeHighSalaries as the query name and then close it.
3. Create a new Append query as follows:
   1. Select all of the fields from the Consultant table. 
   2. Add criteria to select only those records with salaries greater than or equal to $70,000 and less than $80,000.
   3. Append the records to the HighSalaries table.
   4. Run the query, which should add 13 records to the HighSalaries table. Save the query with the name AppendHighSalaries and then close it. 
      The HighSalaries table should contain 16 records, as shown in Figure 1.

 Figure 1: HighSalaries table in Datasheet View

4. Create a new Delete query as follows:
   1. Select all of the fields from the Client table. 
   2. Add criteria so only those records containing a date prior to 7/1/2021 in the ContractSigned field are selected.
   3. Run the query, which should delete three records from the Client table. Save the query with the name DeleteOldContracts and then close it.
5. Create a new Update query as follows:
   1. Select the ComponentName, InstallationFee, and MonthlyFee fields from the SoftwareComponent table. 
   2. Add criteria so only those records with an InstallationFee value greater than or equal to 2000 are selected.
   3. Update the InstallationFee and MonthlyFee fields to increase them by 10% of their current values.
   4. Run the query to update eight records, save it using UpdateFees as the query name, and then close it.
6. Open the UnsoldComponents query and complete the following tasks:
   1. Delete the criteria row that contains criteria in the InstallationFee field.
   2. Add the ClientID field from the ClientSoftware table to the query grid in the fourth column.
   3. Set the query to select all records from the SoftwareComponent table. 
   4. Select only those records from the SoftwareComponent table with no matching records in the ClientSoftware table. 
      
      Save and close the UnsoldComponents query. The query should match Figure 2 when in Datasheet view.

 Figure 2: UnsoldComponents Query in Datasheet View

7. Change the Navigation Options to show hidden objects and unhide the Revenue query.
8. Hide the ConsultantSalaries 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.