New Perspectives Access 2019 | Module 5: SAM Project 1a Global Human Resources Consultants Modifying Tables and Queries GETTING STARTED Open the file NP_AC19_5a_FirstLastName_1

Subject:MS AccessPrice:19.99 Bought7

Share With

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

Global Human Resources Consultants

Modifying Tables and Queries

GETTING STARTED

• Open the file NP_AC19_5a_FirstLastName_1.accdb, available for download from the SAM website.
• Save the file as NP_AC19_5a_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 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.
   
   In Design View of the Client table, add three new fields with the following specifications:
    
   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. 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.)
3. 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.
4. 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.
5. Switch to Datasheet View of the Client table, and select DEU (Germany) as the CountryCode value for the first record (ClientID 1 Biolane Products).
6. 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.
7. Switch to Datasheet View of the Client table, add a Total row to the datasheet, and sum the value in the Employees field.
   
   Save and close the Client table.
8. 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.
       

Figure 1: Final Revenue Query in Datasheet View

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.

Figure 2: Final EmployeeParameter Query in Datasheet View

10. Create a new parameter query in Design View based on the Consultant table with the following details:
    1. Add the ConsultantID, FirstName, LastName, and Reside fields to the query grid in that order.
    2. Right-click the LastName criteria box, and then open the zoom dialog box. Enter the following in the zoom dialog box: Like [Enter Consultant Last Name] & "*"
    3. Close the Zoom dialog box by clicking OK. Save the query using ConsultantParameter as the name and 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, using the Query Wizard, based on the SoftwareComponentsByClient query with the following options:
    1. Add the ClientName field as the Row Heading field.
    2. Add the SoftwareCode as the Column Heading field.
    3. Count the ComponentName field as the Value field.
       
       Save the query with the default name SoftwareComponentsByClient_Crosstab and close the query.
12. Create a new query in Query Design View, based on the Consultant table with the following details:
     
    1. Add the Reside and Salary fields in that order.
    2. Add the Total row to the query grid and average the Salary field.
    3. Use the 'Not' keyword to add criteria to the Reside field to return consultants located outside of the USA.
    4. Change the format of the Salary field to the Currency format.
    5. Save the query and use InternationalAvgSalaryByCountry as the query name.
    6. Run the query.
       Close the InternationalAvgSalaryByCountry query.
13. Create a new query in Query Design View with the following details:
    1. Add the Client and ClientSoftware tables.
    2. Add the ClientName and ProjectStart fields from the Client table and the SoftwareCode field from the ClientSoftware table.
    3. Set the criteria for the SoftwareCode field (using the 'in' keyword) to only include the software codes ONBD, RECR, and SCRE.
    4. Using the expression builder, create a new field named IsCorp.
    5. Within the expression builder, use the iif function to populate the IsCorp with 'True' if the client name contains 'Corporation' and 'False' if not.
    6. 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 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 UnassignedConsultants as the query name, and view the results in Query 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 SAM website to submit your completed project.