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

GETTING STARTED

1. Save the file NP_AC365_EOM2-2_FirstLastName_1.accdb as NP_AC365_EOM2-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. To complete this Project, you will also need the following files:

Support_AC365_EOM2-2_Departments.xlsx

Support_AC365_EOM2-2_NewCourses.xlsx

3. With the file NP_AC365_EOM2-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 have created the initial database and now need to modify it to meet the needs of its users. Modify the Employees table in Design View to update its structure. Delete the Title field, which the company does not need to track. Move the Reimbursement field immediately after the Department field to create a more logical organization. Save the Employees table.

2. With the Employees table open in Design View, modify its field properties. Change the Default Value property for the Reimbursement field to 100 from 0. Change the data type of the Department field to Number to prepare for linking it to another Number field. Save the table, click Yes if you are prompted about lost data, and then close the table.

3. Modify the Registrations table in Design View to adjust its field properties. Change the Required property for the RegDate field to Yes from No and then save the Registrations table. Click Yes if prompted to test data integrity.

4. With the Registrations table open in Design View, move the RegDate field so it becomes the second field in the table, which creates a more logical field order. Save the Registrations table.

5. Display the Registrations table in Datasheet View, and then change the Format property of the RegDate field to Medium Date to make the dates easier to read. The first 12 records in the final Registrations table are shown in Datasheet View in Figure 1. Save and close the table.

Figure 1: Registrations Table in Datasheet View

6. Use the Import Spreadsheet Wizard to add data to the Courses table from Support_AC365_EOM2-2_NewCourses.xlsx by appending a copy of the records from the New Courses worksheet into the existing Courses table. Do not save the import settings. Open the Courses table in Datasheet View, and then scroll to the bottom of the datasheet to review the new data, shown in Figure 2. Close the Courses table.

Figure 2: Courses Table in Datasheet View

7. Open the Employees table in Datasheet View to add missing fields to the table. With the insertion point in the Click to Add column, add the Address Quick Start selection to the end of the table. Delete the Country Region column because all employees reside in the United States. Save the Employees table.

8. Display the Employees table in Design View to modify a field property. Use StrAddress as the new name of the Address field and Str Address as its Caption property. Save the Employees table, display it in Datasheet View, as shown in Figure 3, and then close the table.

Figure 3: Employees Table in Datasheet View

9. Import the Excel workbook named Support_AC365_EOM2-2_Departments.xlsx as a new table. The first row of the spreadsheet contains column headings. Use the default Field Options. Apply the DeptID field as the primary key. Name the new table Departments and do not save the import settings. Open the Departments table in Datasheet View, resize the DeptName field to its best fit to review the data as shown in Figure 4, and then save the table.

Figure 4: Departments Table in Datasheet View

10. Display the Departments table in Design View to change a property. Change the Field Size property of the DeptID field to Long Integer to prepare for linking it to a field in the Employees table. Save the Departments table, clicking Yes if prompted about lost data, and then close the table.

11. Open the Relationships window and then add the Departments table to the window. Create a one-to-many relationship between the primary Departments table and the related Employees table using the DeptID field from the Departments table and Department field from the Employees table. Enforce referential integrity on the relationship, and then save and close the 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 website to submit your completed project.