Central Sierra Insurance wants you to expand

Central Sierra Insurance wants you to expand the functionality of its database to store policy information. To ensure consistency, the starting file is provided for you. Create two new tables by importing XML files and establish relationships between the new tables and the existing tables. Then, create a merge into an existing letter. [Student Learning Outcomes 8.1, 8.3, 8.6, 8.7] File Needed: CentralSierra-08.accdb (Available from the Start File link.), Policies-08.xml, Policies-08.xsd, PolicyCoowners-08.accdb, and PolicyLetter-08.docx (Available from the Resources link.) Completed Project File Name:[your name]-CentralSierra-08.accdb Skills Covered in This Project Import an XML file to create a new table. Import a table from an Access database. Define a relationship with the Lookup Wizard. View and edit relationship properties. Use Mail Merge to create a form letter. Save the settings in a mail merge document. 1. Open the CentralSierra-08 database start file. 2. The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor 3. Enable content in the database. 4. Import an XML file to create a Policies table. MORE INFO Be sure you have all of the .xml and .xsd files for this project in the same folder. a. Click the New Data Source button, select From File, and then select XML File. b. Locate, select, and open the Policies-08.xml file that contains the records you want to import. c. Click OK to start the import process. The Import XML dialog box opens. d. Expand the Policies table to display the fields in the table. e. Choose to import both the Structure and Data. f. Don’t save the import steps. 5. Open the Policies table in Datasheet view to verify that the nine records shown in Figure 8-135 were imported into the table. Change the misspelling of “Farmacy” to “Pharmacy” in the Company field of PolicyID FP649280. 6. View the properties of the table fields. a. Switch to Design view of the table. Start Date:08/01/2212:00 AMUS/Eastern Due Date:08/07/2211:59 PMUS/Eastern End Date:08/07/2211:59 PMUS/Eastern Print Info Student Name: COLLINS, DEANDRE Student ID: 919652124 Username: DeAndre814 8/1/22, 6:37 PM SIMnet – Lesson_13_task_13.2C_365Project https://pennstateworldcampus.simnetonline.com/sp/assignments/projects/details/7066758 2/3 Figure 8-135 Datasheet view of the Policies table Figure 8-136 Datasheet view of the PolicyCo-owners table b. Select each of the fields and review the properties of that field. The .xsd file contains specifications to set the field type and sizes, but not everything was set as desired. c. Make all fields, except Company, required. TotalPremium, a calculated field, does not have a Required property. d. Set the Default Value of State to CA. e. Select Lookup Wizard in the AgentID Data Type property. Follow the wizard steps to look up the values from the EmpID field in the Employees table. See SLO 5.2: Creating and Customizing a Relationship through the Lookup Wizard if you need a review of this step. Click Yes if prompted to save the table. Click Yes to acknowledge the data integrity warning message. f. Close the table. 7. Import a table from an Access database to create a PolicyCo-owners table. a. Click the New Data Source button, select From Database, and then select Access. b. Locate, select, and open the PolicyCo-owners-08.accdb file that contains the table you want to import. c. Click OK to start the import process. The Import Objects dialog box opens. d. Choose to import the PolicyCo-owners table. Be sure to import both the table definition and data. e. Don’t save the import steps. 8. Open the PolicyCo-owners table in Datasheet view to verify that the five records shown in Figure 8-136 were imported into the table. 9. View the properties of the table fields. a. Switch to Design view of the table. b. Select each of the fields and review the properties of that field. The database file contained specifications to set the field type, sizes, and required properties. c. Make the FirstName and LastName fields required. d. Select Lookup Wizard in the PolicyNumber Data Type property. Follow the wizard steps to look up the values from the PolicyID field in the Policies table. Click Yes to acknowledge the message to save the table, and click Yes again when the data integrity warning message appears. Click OK to acknowledge the index message. e. Close the table. 10. Enforce referential integrity in the table relationships. a. Open the Relationships window. b. Add the Policies and PolicyCo-owners tables to the Relationships window if not already shown. c. Drag the EmpID field from the Employees table on top of the AgentID field in the Policies table if the relationship does not already display. d. Edit the relationship between Employees and Policies to Enforce Referential Integrity and Cascade Update Related Fields. e. Drag the PolicyID field from the Policies table on top of the PolicyNumber field in the PolicyCo-owners table if the relationship does not already display. f. Edit the relationship between Policies and PolicyCo-owners to Enforce Referential Integrity, Cascade Update Related Fields, and Cascade Delete Related Records. g. Save your changes and close the Relationships window. 11. Perform a mail merge using an existing letter. a. Select the Policies table and click the Word Merge button. b. Select Link your data to an existing Microsoft Word document if it is not already selected, and click OK. c. Locate, select, and open the PolicyLetter-08.docx Word file. Note that this document contains placeholders so you know where to position the merge fields (Figure 8-137). d. Click the Next: Write your letter link. e. Replace the [Insert Current Date] placeholder in the Word document by typing the current date. f. Replace the [AddressBlock] placeholder with the <> merge field. Accept the default selections on the Insert Address Block dialog box. g. Replace the [Greeting] placeholder with the <> merge field and select the colon (:) from the punctuation drop-down list in the Greeting line format area. 8/1/22, 6:37 PM SIMnet – Lesson_13_task_13.2C_365Project https://pennstateworldcampus.simnetonline.com/sp/assignments/projects/details/7066758 3/3 Figure 8-137 Mail Merge pane, Step 3 Figure 8-138 Preview of merged policy letter h. Replace the remaining placeholders by clicking the More items link to open the Insert Merge Field dialog box and insert the corresponding field. i. Click the Next: Preview your letters link. The document updates to show how the merged data appears (Figure 8-138). j. Click the navigation arrows in the Preview your letters area to scroll through the nine letters to verify that the merge works correctly. k. Click the Next: Complete the merge link. l. Click the Edit individual letters link and merge all the records. m. Save the completed merged document as [your initials] PolicyLetter Merge.docx and close the document. 12. Save a copy of the main document as [your initials] PolicyLetter Main.docx to preserve the merge settings. 13. Close the document. 14. Save and close the database. 15. Upload and save your project file. 16. Submit project for grading