New Perspectives Access 365 | Module 3: End of Module Project 2 Deerfield Insurance Group

GETTING STARTED

1. Save the file NP_AC365_EOM3-2_FirstLastName_1.accdb as NP_AC365_EOM3-2_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_EOM3-2_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. As a project assistant for Deerfield Insurance Group, you are developing an Access database to help manage training courses for employees in areas ranging from claims and customer service to data analytics and information technology. You need to update records and query the database to answer questions from the company managers. Modify the Employees table in Datasheet View to update fields and records. In the record with the ID value of 11963 (Gary Holmes), insert Bao for the FirstName value and Wu for the LastName value. Modify the expression in the calculated Reimbursement field to return 75 instead of 50 for the false part of the condition. Sort the records in ascending order by the LastName field. Save and close the Employees table.

2. Modify the EmployeeList query in Datasheet View to correct a field value. For ID 11234 (Avi Khan), change the Department value to 240. Note that the DeptName value automatically updates to Marketing due to the relationship between the Employees and Departments tables.

3. Display the EmployeeList query in Design View to select only those records for employees in the Marketing Department. Enter a criterion for the Department field that selects only employees in Department 240, the Marketing Department. Save and display the EmployeeList query in Datasheet View as shown in Figure 1 and then close it.

Figure 1: EmployeeList Query in Datasheet View

4. Create a new query in Design View using the Courses and Registrations tables to list course registrations by employee. Add the CourseID and CourseTitle fields from the Courses table and the EmployeeID and RegDate fields from the Registrations table to the query design grid, in that order. Add an ascending sort order to the EmployeeID field. Save the query with the name CourseRegistrations, display it in Datasheet View as shown in Figure 2, and then close it.

Figure 2: CourseRegistrations Query in Datasheet View

5. Modify the RegistrationDates query in Design View to display employees who registered on or after September 15 and to organize the records. Add criteria to select all records that have a RegDate greater than or equal to 9/15/2029. Add an ascending sort order to the LastName and FirstName fields. Delete the Rating field from the query design grid, and then move the Reimbursement field to be the third field. Save and display the RegistrationDates query in Datasheet View as shown in Figure 3 and then close it.

Figure 3: RegistrationDates Query in Datasheet View

6. Modify the ClaimsAndLowRatings query in Design View to select employees in the Claims Department with low course ratings. Add criteria to select all records that have a Department field value of 100 for the Claims Department, a StartDate after 12/31/2020, and a Rating value of less than 5. Save and display the ClaimsAndLowRatings query in Datasheet View as shown in Figure 4 and then close it.

Figure 4: ClaimsAndLowRatings Query in Datasheet View

7. Modify the FinanceOrHighRatings query in Design View to select employees in the Finance Department or those with high course ratings. Add criteria to select all records that have a Department field value of 160 for the Finance Department or a Rating value of greater than 7. Save and display the FinanceOrHighRatings query in Datasheet View as shown in Figure 5 and then close it.

Figure 5: FinanceOrHighRatings 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.