Homework answers / question archive / Illustrated Access 2019 | Module 5 - SAM Project 1a You work in the software division of Global Human Resources Consultants (GHRC), which sells modular Human Resource (HR) software to large international companies

Illustrated Access 2019 | Module 5 - SAM Project 1a You work in the software division of Global Human Resources Consultants (GHRC), which sells modular Human Resource (HR) software to large international companies

MS Access

Share With

Illustrated Access 2019 | Module 5 - SAM Project 1a

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 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.
   1. A field named Website with a Hyperlink data type.
   2. A field named Logo with an Attachment data type.
   3. A field named Notes with a Long Text data type.
   4. For the Notes field, use Yes as the value for the Append Only property value.
      
      Save the Client table.
2. 
   In Design View of the Client table, add three new fields with the following specifications:
3. While still in Design View of the Client table, modify the following field properties:
   1. Enter Client as the Caption property for the ClientName field.
   2. Enter 30 as the Field Size property for the ClientName field.
   3. Choose Medium Date as the Format property for the ContractSigned and ProjectStart fields.
      
      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.)
4. While still in Design View of the Client table, modify the following field properties:
   1. Choose Yes as the Required property for the Employees field.
   2. Enter >=500 as the Validation Rule property for the Employees field.
   3. Enter Minimum is 500 for the Validation Text property for the Employees field, and then save the Client table. If prompted to test data integrity rules, click Yes.
5. While still in Design View of the Client table, add a new field immediately below the existing fields using the Lookup Wizard and the following information:
   1. Enter CountryCode as the field name.
   2. Get the values from the Country table.
   3. Select both the CountryCode and CountryName fields.
   4. Sort the records in ascending order by the CountryName field.
   5. Do not hide the key column.
   6. Store the CountryCode value, use the other default options of the Lookup Wizard, and save the Client table as prompted.
   7. Use Yes for the Column Heads Lookup property for new CountryCode field.
      
      Save the Client table.
6. Switch to Datasheet View of the Client table, and select DEU (Germany) as the CountryCode value for the first record (ClientID 1 Biolane Products).
7. Switch to Design View of the Client table and modify these table properties:
   1. Add [ProjectStart]>=[ContractSigned] as the Validation Rule for the table.
   2. Enter Contract must be signed before the project start date as the Validation Text property for the table.
      
      Save the Client table. Click Yes if prompted to test for data integrity.
8. Switch to Datasheet View of the Client table, and make the following modifications:
   1. Add the Support_AC19_5a_Logo.jpg file in the default folder location to the Logo Attachment field for the second record (ClientID 2 Perosis Medical Supplies).
   2. Add a Total row to the datasheet, and sum the value in the Employees field.
      
      Save and close the Client table.
9. In Design View of the Revenue query, complete the following modifications:
   1. Enter Software Module as the Caption property to the ComponentName field.
   2. Add the Total row to the query grid, count the ClientName field, and sum the InstallationFee and MonthlyFee fields.
   3. Save and view the query in Datasheet View, and then widen each of the four columns to 22.
   4. Add a Total row to the datasheet and sum the CountofClientName, SumOfInstallationFee, and SumOfMonthlyFee as shown in Figure 1.
      
      Save and close the Revenue query.

9. Create a new query in Query Design View based on the Client table with the following details:
   1. Add the ClientName and Employees fields in that order.
   2. Sort the records in descending order on the Employees field.
   3. Return the top 5 records.
   4. Add <[Enter maximum number of employees] as a parameter prompt for the Employees field.
   5. Save the query and use EmployeeParameter as the query name.
   6. Run the query using 2000 as the parameter prompt as shown in Figure 2.
      
      Close the EmployeeParameter query

10. Create a new query in Query Design View with the following details:
    1. Add the Client, ClientSoftware, and SoftwareComponent tables.
    2. Add the ClientName field from the Client table, the SoftwareCode field from the ClientSoftware table, and the ComponentName field from the SoftwareComponent table to the query grid in that order.
    3. Change the query from a Select query to a Crosstab query.
    4. Use the ClientName field as the Row Heading field, use the SoftwareCode as the Column Heading field, and count the ComponentName field as the Value field.
       
       Save the query with the name SoftwareComponentsByClient and view the query in Datasheet View. Save and close the SoftwareComponentsByClient query.
11. Use the Find Unmatched Query Wizard to find records in the Consultant table that contain no related records in the Client table using the following details:
    1. The ConsultantID field in each table contains matching data.
    2. Include the ConsultantID, FirstName, and LastName fields in the query results.
    3. Use UnassignedConsultantsas the query name, and view the results in Query Datasheet View as shown in Figure 3.

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.