New Perspectives Access 365/2021 | Module 5: SAM Critical Thinking Project 1c Global Human Resources Consultants
Modifying Tables and Queries
· Excel Projects Help
GETTING STARTED
1. Save the file NP_AC365_2021_CT5c_FirstLastName_1.accdb as NP_AC365_2021_CT5c_FirstLastName_2.accdb
a. Edit the file name by changing “1” to “2”.
b. If you do not see the .accdb file extension, do not type it. The file extension will be added for you automatically.
2. With the file NP_AC365_2021_CT5c_FirstLastName_2.accdb open, ensure that your first and last name is displayed as the first record in the _GradingInfoTable table.
a. If the table does not display your name, delete the file and download a new copy.
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 have created 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 improve the tables and queries of the database. Add three new fields to the Client table with the following specifications:
a. A field named Website with the Hyperlink data type.
b. A field named Logo with the Attachment data type.
c. A field named Notes with the Long Text data type. For the Notes field, use Yes as the value for the Append Only property value. Save the Client table.
2. Modify the following field properties in the Client table:
a. Set Client as the caption for the ClientName field.
b. Set 30 as the field size for the ClientName field.
c. Format the ContractSigned and ProjectStart fields using the Medium Date format. Save the Client table. If prompted that some data may be lost, click Yes to continue. (Hint: No data is lost because no values exceed 30 characters in the ClientName field.)
3. Still in the Client table, modify the following field properties:
a. Make the Employees field required.
b. Set a validation rule of =500 for the Employees field.
c. Set Minimum is 500 as the Validation Text property for the Employees field, and then save the Client table. If prompted to test data integrity rules, click Yes.
4. Add a new Lookup field immediately below the existing fields using the following information:
a. Enter CountryCode as the field name.
b. Select the CountryCode and CountryName fields from the Country table.
c. Sort the records in ascending order by the CountryName field.
d. Do not hide the key column.
e. Store the CountryCode value, use the other default options, and then save the Client table as prompted.
f. Use Yes for the Column Heads Lookup property for the new CountryCode field. Save the Client table.
5. Select DEU (Germany) as the CountryCode value for the first record (ClientID 1 Biolane Products) of the Client table.
6. Modify the following table properties in the Client table:
a. Set [ProjectStart]=[ContractSigned] as the validation rule for the table.
b. Set Contract must be signed before the project start date as the validation text for the table. Save the Client table. Click Yes if prompted to test for data integrity.
7. Add a total row to the datasheet, and then sum the value in the Employees field in the Client table. Save and close the Client table.
8. Switch to the Revenue query and complete the following modifications:
a. Enter Software Module as the caption for the ComponentName field.
b. Add the total row to the query grid, count the ClientName field, and sum the InstallationFee and MonthlyFee fields.
c. Save and view the query in Datasheet View, and then widen each of the four columns to 22. Add a total row to the datasheet, and then sum the CountofClientName, SumOfInstallationFee, and SumOfMonthlyFee fields as shown in Figure 1. Save and close the Revenue query.
Figure 1: Final Revenue Query in Datasheet View
9. Create a new query based on the Client table with the following details:
a. Add the ClientName and Employees fields in that order.
b. Sort the records in descending order on the Employees field.
c. Return the top five records.
d. Add [Enter maximum number of employees] as a parameter prompt for the Employees field.
e. Save the query and use EmployeeParameter as the query name.
f. Run the query using 2000 as the parameter prompt, which should yield the results shown in Figure 2. Close the EmployeeParameter query.
Figure 2: Final EmployeeParameter Query in Datasheet View
10. Create a new parameter query based on the Consultant table with the following details:
a. Add the ConsultantID, FirstName, LastName, and Reside fields to the query grid in that order.
b. Enter the following in the zoom dialog box for the LastName field: Like [Enter Consultant Last Name] & "*"
c. Save the query using ConsultantParameter as the name, and then run the query to test it. Enter Co in the parameter prompt, and note that two records are returned. Close the query.
11. Create a crosstab query based on the SoftwareComponentsByClient query with the following details:
a. Add the ClientName field as the row heading.
b. Add the SoftwareCode field as the column heading.
c. Count the ComponentName field as the value. Save the query with the default name SoftwareComponentsByClient_Crosstab and close the query.
12. Create a new query based on the Consultant table with the following details:
a. Add the Reside and Salary fields in that order.
b. Add the total row to the query grid, and then average the Salary field.
c. Add criteria to the Reside field to return consultants located outside of the USA.
d. Change the format of the Salary field to the Currency format.
e. Save the query and use InternationalAvgSalaryByCountry as the query name.
f. Run the query. Close the InternationalAvgSalaryByCountry query.
13. Create a new query with the following details:
a. Add the Client and ClientSoftware tables.
b. Add the ClientName and ProjectStart fields from the Client table and the SoftwareCode field from the ClientSoftware table.
c. Set the criteria for the SoftwareCode field to only include the software codes ONBD, RECR, and SCRE.
d. Using the Expression Builder, create a new field named IsCorp.
e. Use the iif function to populate the IsCorp field with "True" if the client name contains "Corporation" and "False" if not.
f. Save the query as CorporateNewHireClients and run the query. Close the CorporateNewHireClients query.
14. Use the Find Unmatched Query Wizard to find records in the Consultant table that contain no related records in the Client table by completing the following tasks:
a. Ensure the ConsultantID field in each table contains matching data.
b. Include the ConsultantID, FirstName, and LastName fields in the query results.
c. Use UnassignedConsultants as the query name, and then view the results in Datasheet View as shown in Figure 3.
Figure 3: Final UnassignedConsultants 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 website to submit your completed project.