New Perspectives Access 2019 | Module 9: SAM Project 1a | Global Human Resources Consultants 2025

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

Global Human Resources Consultants
CREATING ACTION QUERIES AND MODIFYING THE NAVIGATION PANE

GETTING STARTED
• Open the file NP_AC19_9a_FirstLastName_1.accdb, available for download from the SAM website.
• Save the file as NP_AC19_9a_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. You work in the software division of Global Human Resources Consultants (GHRC) which sells modular Human Resource (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 and paste both the structure and data as a table using the name ClientBackup into the database.
2. Create a new Make Table query in Design View with the following instructions:

a. Select all of the fields from the from the Consultant table.
b. Add criteria to select only those records greater than or equal to $80,000 in the Salary field.
c. Use HighSalaries as the new table name.
d. 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 in Design View with the following instructions:

a. Select all of the fields from the from the Consultant table.
b. Add criteria to select only those records greater than or equal to $70,000 and less than $80,000 in the Salary field.
c. Append the records to the HighSalaries table.
d. Run the query which should append 13 records into the HighSalaries table. Save the query with the name AppendHighSalaries and then close it.
e. Open the HighSalaries table to review all 16 records as shown in Figure 1. Close the HighSalaries table.
Figure 1: HighSalaries table in Datasheet View

4. Create a new Delete query in Design View with the following instructions:

a. Select all of the fields from the Client table.
b. Add criteria to select only those records with a ContractSigned date prior to 7/1/2021.
c. 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 in Design view with the following instructions:

a. Select the ComponentName, InstallationFee, and MonthlyFee fields from the SoftwareComponent table.
b. Add criteria so only those records with an InstallationFee that are greater than or equal to 2000 are selected.
c. Update both the InstallationFee and MonthlyFee fields to increase them by 10% more than their current values.
d. Run the query to update eight records, save it with the name UpdateFees and then close it.
6. Open the UnsoldComponents query in Design View and complete the following:

a. Delete the criteria row that contains criteria in the InstallationFee field.
b. Add the ClientSoftware table to the query and add the ClientID field from the ClientSoftware table to the query grid in the fourth column.
c. Change the join properties between the two tables to select all records from the SoftwareComponent table.
d. Enter Is Null criteria for the ClientID field to select only those records from the SoftwareComponent table with no matching records in the ClientSoftware table.

Save the UnsoldComponents query, display it in Datasheet View as shown in Figure 2, and then close it.
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. (Hint: Ensure that the query is hidden in the Object Properties, not Hidden in Group.)

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.
#MicrosoftAccess #MicrosoftOffice #Microsoft #NewPerspectivesAccess #Module #SAMProject