Fill This Form To Receive Instant Help

Help in Homework
trustpilot ratings
google ratings


Homework answers / question archive / Shelly Cashman Access 2019 | Module 6: SAM Project 1a Personal Insurance, Inc

Shelly Cashman Access 2019 | Module 6: SAM Project 1a Personal Insurance, Inc

MS Access

Shelly Cashman Access 2019 | Module 6: SAM Project 1a

Personal Insurance, Inc.

Advanced Report Techniques

 

 

  • *GETTING STARTED
  • Open the file SC_AC19_6a_FirstLastName_1.accdb, available for download from the SAM website.
  • Save the file as SC_AC19_6a_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.
  • 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_6a_Claims.txt
  • 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. Personal Insurance, Inc. is a national company that insures homeowners and renters. As a regional manager, you need to be able to create professional reports for employees and for entities outside the company.

    Import the data from the file Support_AC19_6a_Claims.txt, and then append the records to the Claims table. The text file is a delimited file with a comma separating each of the six fields. Do not create a new table, and do not save the import steps.

     
  2. Open the Regional Customer List Report in Design View, and then complete the following tasks:
     
    1. Add the current date and time to the Report Header section.
    2. In the Date and Time dialog box, use the option button to select the second date format (e.g., 01-Aug-19) and the second time format (e.g., 4:19 PM).
    3. Resize the report so that the right boundary of the report is approximately at the 8.5" mark on the horizontal ruler.

      Save the report without closing it.
  3. With the Regional Customer List Report still open in Design View, complete the following tasks:
     
    1. Select the Region Header, and then display the Property Sheet for this section.
    2. Change the Repeat Section property to Yes.
    3. Change the Force New Page Property to Before Section.

      Save and close the report.
  4. Modify the Claims by ClaimRep Report by completing the following tasks:
     
    1. Open the Claims by ClaimRep Report in Design View.
    2. Open the field list for the report.
    3. Add the LastName field to the ClaimRepNumber Header section of the report so that the left edge of the control is at the 5" mark on the horizontal ruler. The left edge of the label is just to the left of the 4" mark on the horizontal ruler. (Hint: You may need to reposition and resize the LastName control and its label after inserting it into the report.)

      Confirm that the report matches Figure 1, and then save the report without closing it.

       
  5. With the Claims by ClaimRep Report still open in Design View, complete the following tasks:
     
    1. Select the ClaimID, CustomerID, PolicyNumber, ClaimAmount, and Paid controls simultaneously.
    2. Group the controls.
    3. Resize the controls by dragging their right edge to the 4" mark on the ruler.
    4. Bold the controls.

      Confirm that the report matches Figure 1, and then save and close the Claims by ClaimRep Report.

* Figure 1: Claims by ClaimRep Report – Design View

 

The figure shows the Claims by ClaimRep Report in Design View. The LastName field has been added to the ClaimRepNumber Header section. The ClaimID, CustomerID, PolicyNumber, ClaimAmount, and Paid controls are resized to the 4" ruler mark on their right edges and the controls are bolded.

 

  1. Modify the Homeowners Coverage Report by completing the following tasks:
     
    1. Open the Homeowners Coverage Report in Design View.
    2. Change the Text11 label in the Page Header section using TotalCoverage as the new label name.
    3. Format the text box control in the Detail section containing the =[Liability]+[PropertyDamage] calculation so that it displays with the Currency number format and two decimal places.

      Save the Homeowners Coverage Report, switch to Report View, confirm your report matches Figure 2, and then close the report.

* Figure 2: Homeowners Coverage Report—Report View

 

The figure shows the Homeowners Coverage Report in Report View. The label in the last column in the Page Header section is TotalCoverage and the TotalCoverage text box in the Detail section has been formatted as Currency with two decimal places.

 

  1. Open the Basic Umbrella Policy List Report in Design View, and then change the Alternate Back Color property for the Detail section to No Color. Save and close the report.
  2. Open the Homeowners Premium Report in Design View, and then use the Subform/Subreport Wizard to add a subreport with the following options:
     
    1. Add the subreport below the Address label in the Detail section, at approximately the 1" mark on the vertical ruler.
    2. Use the Homeowners table as the source of the subreport.
    3. Select the PolicyNumber, PropertyDamage, and Premium fields (in that order) from the Homeowners table to add to the subreport.
    4. Ensure that the Choose from a list option button is selected, and accept the default link Show Homeowners for each record in Customers using CustomerID for the subreport.
    5. Save the subreport as Homeowners subreport (which is the default name). (Hint: The width of the subreport may expand the width of the report. Do not manually adjust the width.)
    6. Delete the label associated with the subreport.

      Save the Homeowners Premium Report, switch to Report View, confirm your report matches Figure 3, and then close the report.

* Figure 3: Homeowners Premium Report—Report View

 

The figure shows the Homeowners Premium Report in Report View. A subreport based on the Homeowners table has been added beneath the Address label at approximately the 1" mark on the vertical ruler. The subreport includes the PolicyNumber, PropertyDamage, and Premium fields. There is no label associated with the subreport.

 

  1. Open the Customers by State Report in Design View, and then modify the report by completing the following tasks:
     
    1. Resize the width of the report as shown in Figure 4. (Hint: The right border of the report is at the 8" mark on the horizontal ruler.)
    2. Move the PostalCode label and control so that it appears under the City control in the approximate location shown in Figure 4. (Hint: Align the bottom of the label and control with the 2" mark on the vertical ruler.)
    3. Select the Region, PhoneNumber, and DateOfBirth controls simultaneously. Do not select the labels.
    4. Align the controls to the left as shown in Figure 4.

      Confirm that the report matches Figure 4, and then save and close the Customers by State Report.

* Figure 4: Customers by State Report—Design View

 

The figure shows the Customers by State Report in Design View. The report has been resized to the 8" mark on the horizontal ruler. The PostalCode label and control appear under the City control. The Region, PhoneNumber, and DateOfBirth controls are aligned to the left.

 

  1. Open the Customer Address List Report in Design View, and then modify the report by completing the following tasks:
     
    1. Select all labels in the Page Header section.
    2. Bold the labels.
    3. Select the Address control in the Detail section, and then change the Can Grow property to Yes.
    4. Resize the Detail section of the report by dragging the lower boundary of the section to just below the controls in the report, at approximately the .5" mark on the vertical ruler for the Detail section.

      Confirm that the report matches Figure 5, and then save and close the Customer Address List.

* Figure 5: Customer Address List—Design View

 

The figure shows the Customer Address List in Design View. The labels in the Page Header section are bold. The Detail section has been resized so that the lower boundary is just below the controls at approximately the .5" mark on the horizontal ruler. The Can Grow property for the Address control was changed to Yes.

 

  1. Modify the Renters Premium List Report by performing the following tasks:
     
    1. Open the Renters Premium List Report in Design View.
    2. Use the Group, Sort, and Total pane to add a footer section to the Group on CustomerID section.
    3. Add a text box control in the CustomerID Footer section.
    4. Convert the text box control into a calculated control that averages the Premium field. (Hint: Your control should contain the formula =Avg([Premium]).)
    5. Format the control so that it displays with the Currency number format and two decimal places.
    6. Change the label using Average Premium as the label name.
    7. Reposition the control and its label to the approximate location shown in Figure 6. (Hint: The left edge of the text box should be located at the 5" mark on the horizontal ruler.)

      Confirm that the report matches Figure 6, and then save and close the Renters Premium List.

* Figure 6: Renters Premium List—Design View

 

The figure shows Renters Premium List in Design View. A group footer has been added to the report. A text box control appears in the group footer with the formula =Avg([Premium]) formatted as Currency with two decimal places. The label for the control is Average Premium. The control is positioned so the left edge is at the 5" mark on the horizontal ruler.

 

  1. Create a new blank report in Design View, and then complete the following tasks:
     
    1. Open the Property Sheet for the report, and then apply the Record Source for the report to the Claims table.
    2. Save the report with the name Basic Claims Report, but do not close the report.
    3. Add the ClaimID, CustomerID, and PolicyNumber fields to the report and then reposition them so that the left edges of the three controls are at the 3" mark on the horizontal ruler and only a single dotted line separates the labels and controls vertically.
    4. Add the title Basic Claims Report to the Report Header section.
    5. Add page numbers to the report at the Top of Page (Header) position, using the Page N of M format and Right alignment.

      Confirm that the report matches Figure 7, and then save and close the Basic Claims Report.

* Figure 7: Basic Claims Report—Design View

 

The figure shows Basic Claims Report in Design View. There are three controls and associated labels on the report: ClaimID, CustomerID, and PolicyNumber. The left edges of the text boxes are at the 3" mark on the horizontal ruler. The title Basic Claims Report has been added to the Report Header. Page numbers have been added to the Page Header section aligned to the right.

 

  1. Open the NE Homeowners by State Report in Design View. Group the report by the State field, and then sort the report by the CustomerID field in ascending order. Do not add any additional grouping or sorting options to the report. Save and close the NE Homeowners by State Report. (Hint: This report does not contain any fields.)
  2. Modify the Claims Total Report by performing these tasks:
     
    1. Open the Claims Total Report in Design View.
    2. Add a text box control to the Report Footer section. The left edge of the control (not the label) is at the 5" mark on the horizontal ruler.
    3. Convert the text box control into a calculated control that sums the ClaimAmount field. (Hint: Your control should contain the formula =Sum([ClaimAmount]).)
    4. Format the control so that it displays with the Currency number format and zero decimal places.
    5. Change the label using Total as the new label name.
    6. If necessary, reposition the control and its label so that the left edge of the text box is located at the 5" mark on the horizontal ruler.
      Confirm that the report matches Figure 8, and then save and close the Claims Total Report.

* Figure 8: Claims Total Report—Design View

 

The figure shows Claims Total Report in Design View. A text box control has been added to the Report Footer section. The formula in the text box is =Sum([ClaimAmount]) and the control is formatted as Currency with zero decimal places. The label for the text box has been changed to Total. The left edge of the text box is at the 5" horizontal ruler mark.

 

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.

Option 1

Low Cost Option
Download this past answer in few clicks

19.99 USD

PURCHASE SOLUTION

Already member?


Option 2

Custom new solution created by our subject matter experts

GET A QUOTE