Homework answers / question archive / Shelly Cashman Access 2016 | Module 6: SAM Project 1a West Coast Outdoor Advertisers advanced report techniques GETTING STARTED Open the file SC_AC16_6a_FirstLastName_1

Shelly Cashman Access 2016 | Module 6: SAM Project 1a West Coast Outdoor Advertisers advanced report techniques GETTING STARTED Open the file SC_AC16_6a_FirstLastName_1

MS Access

Share With

---

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

West Coast Outdoor Advertisers

advanced report techniques

GETTING STARTED

• Open the file SC_AC16_6a_FirstLastName_1.accdb, available for download from the SAM website.
• Save the file as SC_AC16_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.
  • 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.
  • To complete this Project, you will also need to download and save the following support file from the SAM website:
    • Support_SC_AC16_6a_States.txt

 

PROJECT STEPS

1. Import the data from the file Support_SC_AC16_6a_States.txt, available for download from the SAM website, and then append the records to the States table. The text file is a delimited file with a comma separating the two fields. Do not create a new table, and do not save the import steps.
2. Open the Basic Billboards 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-18) and the second time format (e.g., 4:18 PM).
   3. Resize the report so that the right boundary of the report is approximately at the 8" mark on the horizontal ruler.

Save the report without closing it.

1. With the Basic Billboards List report still open in Design View, complete the following tasks:
   1. Select the State 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.

1. Modify the Advertisers by AdRep Report to match Figure 1 below by completing the following tasks:
   1. Open the Advertisers by AdRep Report in Design View.
   2. Open the field list for the report.
   3. Add the LastName field to the AdRepNumber Header section of the report in the postion shown in Figure 1. (Hint: You may need to reposition and resize the LastName control and/or its label after inserting it into the report.)
   4. With the Advertisers by AdRep Report still open in Design View, complete the following tasks:
      1. Select the AdvertiserID, AdvertiserName, Amount Paid, and Current Due controls simultaneously.
      2. Group the controls.
      3. Resize the controls by dragging their right edge to the 4" mark on the ruler as shown in Figure 1.
      4. Bold the controls

Save and close the Advertisers by AdRep Report.

 

Figure 1: Advertisers by AdRep Report – Design View

 

1. Modify the Advertiser Financial Report to match Figure 2 below by completing the following tasks:
   1. Open the Advertiser Financial Report in Design View.
   2. Change the Text11 label in the Page Header section to Total Amount. (Hint: Do not type the period.)
   3. Format the text box control in the Detail section containing the =[Amount Paid]+[Current Due] calculation so that it displays with the Currency number format and two decimal places, as shown in Figure 2.

Save and close the Advertiser Financial Report.

 

Figure 2: Advertiser Financial Report – Report View

 

1. Open the AdRep Phone 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 AdRep Master 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 AdvertiserName label in the Detail section, at approximately the 1" mark on the vertical ruler.
   2. Use the Rentals table as the source of the subreport.
   3. Select the RentalID, BillboardID, BeginDate, and Months fields (in that order) from the Rentals table to add to the subreport.
   4. Ensure that the Choose from a list option button is selected, and accept the default link Show Rentals for each record in AdReps and Advertisers using AdvertiserID for the subreport.
   5. Save the subreport as Rentals 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 AdRep Master Report, switch to Report View, confirm your report matches Figure 3 below, and then close the report.

 

Figure 3: AdRep Master Report – Report View

 

1. Open the Billboards by Type Report in Design View, and then modify the report to match Figure 4 on the following page by completing these tasks:
   1. Resize the width of the report as shown in Figure 4. (Hint: The right border of the report is at the 7" mark on the horizontal ruler.)
   2. Move the DEC label and control so that it appears under the State control in the approximate location shown in Figure 4. (Hint: Align the top of the label and control with the 1.5" vertical ruler.)
   3. Select the Height, Width, SqrFt, and Illuminated controls simultaneously. Do not select the labels.
   4. Align the controls to the left as shown in Figure 4.

Save and close the report.

 

Figure 4: Billboards by Type Report – Design View

 

1. Open the Rentals Financial 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 AdvertiserName 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, as shown in Figure 5 on the following page.

Save and close the report.

Figure 5: Rentals Financial Report – Design View

 

1. Modify the Advertising Rentals Report to match Figure 6 on the following page by performing these tasks:
   1. Open the Advertising Rentals Report in Design View.
   2. Use the Group, Sort, and Total pane to add a footer section to the Group on AdvertiserID section.
   3. Add a text box control in the AdvertiserID Footer section in the position shown in Figure 6.
   4. Convert the text box control into a calculated control that sums the TotalCost field. (Hint: Your control should contain the formula =Sum([TotalCost]).)
   5. Format the control so that it displays with the Currency number format and two decimal places.
   6. Change the label to Advertiser Total as shown in Figure 6.
   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 4" mark on the horizontal ruler.)

Save and close the report.

Figure 6: Advertising Rentals Report – Design View

 

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 set the Record Source for the report to the States table.
   2. Save the report with the name State Report, but do not close the report.
   3. Add the State and StateName fields to the report, and then reposition them to the approximate location shown in Figure 7 on the following page.
   4. Add the title State 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.

Save and close the report.

Figure 7: State Report – Design View

 

1. Open the Billboards by State Report in Design View. Group the report by the State field, and then sort the report by the BillboardID field in ascending order. Do not add any additional grouping or sorting options to the report. Save and close the the Billboards by State Report. (Hint: This report does not contain any fields.)
2. Modify the Rentals Total Report to match Figure 8 on the following page by performing these tasks:
   1. Open the Rentals Total Report in Design View.
   2. Add a text box control to the Report Footer section in the position shown in Figure 8.
   3. Convert the text box control into a calculated control that sums the TotalCost field. (Hint: Your control should contain the formula =Sum([TotalCost]).)
   4. Format the control so that it displays with the Currency number format and zero decimal places.
   5. Change the label to Total as shown in Figure 8.
   6. Reposition the control and its label to the approximate location shown in Figure 8. (Hint: The left edge of the text box should be located at the 4" mark on the horizontal ruler.)

Save and close the report.

Figure 8: Rentals Total Report – Design View

 

Save and close any open database objects. Compact and repair your database, close it, and then exit Access. Follow the directions on the SAM website to submit your completed project.