New Perspectives Access 2019 | Module 2: SAM Critical Thinking Project 1c Global Human Resources Consultants

GETTING STARTED

1. Open the file NP_AC19_CT2c_FirstLastName_1.accdb, available for download from the SAM website.

2. Save the file as NP_AC19_CT2c_FirstLastName_2.accdb by changing the “1” to a “2”.

a. 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.

3. To complete this SAM Project, you will also need to download and save the following data files from the SAM website onto your computer:

Support_AC19_CT2c_Clients.xlsx

Support_AC19_CT2c_Consultants.txt

Support_AC19_CT2c_Projects.accdb

4. 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. Most of the data that you need for the Global Human Resources Consultants database is currently stored in Excel workbooks. Import the data from the Support_AC19_CT2c_Clients.xlsx support file and add it to the end of the Client table. Do not analyze the table after importing the data, and do not save the import steps. The Client table should look like Figure 1 when in Datasheet View.

Figure 1: Updated Client Table

2. More consultants were recently hired to prepare for a projected increase in business. Import the data from the Support_AC19_CT2c_Consultants.txt support file and add it to the end of the Consultant table. Do not analyze the table after importing the data, and do not save the import steps.

3. Consultants work on projects, such as creating websites for internal communication. Import the Project table from the Support_AC19_CT2c_Projects.accdb support file into the database. Do not save the import steps. The Project table should look like Figure 2 when in Datasheet View.

Figure 2: New Project Table

4. Consultants can reside in any country, but many of the consultants reside in the United States. In the Consultant table, change the Default Value property for the Reside field to USA. Save the change but do not close the table.

5. Still in the Consultant table, set the Caption property to First Name for the FirstName field and Last Name for the LastName field. Save the changes and close the table.

6. In the Client table, make the ClientName field a required field. Save the change to the Client table and then close the table. (Hint: The "Data Integrity rules have been changed" warning message will appear. The data is valid, so click No and continue saving the table.)

7. In the Consultant table, apply the Currency format to the Salary field. Save the change but do not close the table.

8. With the Consultant table still open, move the Salary field so that it appears immediately after the StartDate field. Add a new field following the Reside field with the field name SkillCode and the Short Text data type. Save the change and close the table.

9. Open the Project table and make the following changes:

a. Resize the Description field so that all field values are completely visible.

b. Add a new field following the Description field with the field name Months and the Number data type. Save the changes and close the table.

10. Open the Consultant table, delete the Mobile field, and then save the change. The Consultant table should look like Figure 3.

Figure 3: Updated Consultant Table

11. With the Consultant table still open, decrease the number of decimal places for the Salary field to 0. Save the change and close the table.

12. Open the Project table and make the following changes to the Months field:

a. Change the default value to 3.

b. Increase the number of decimal places to 2. Save the changes and close the table.

13. Open the Project table and add a new field below the Months field named ConsultantID with the Number data type. This field will serve as the foreign key field to allow one record in the Consultant table to link to many records in the Project table.Save and close the Project table.

14. Open the Relationships window and create a one-to-many relationship between the Consultant and Project tables using the common ConsultantID field. Enforce referential integrity on the relationship.

15. With the Relationships window still open, create a one-to-many relationship between the Skill and Consultant tables using the common SkillCode field. Enforce referential integrity on the relationship. The Relationships window should look like Figure 4. Save and close the Relationships window.

Figure 4: Final Relationships Window

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.