question archive Illustrated Access 2016 | Module 8: SAM Project 1b Salvage Database ANALYZING DATA WITH REPORTS GETTING STARTED • Open the file IL_AC16_8b_FirstLastName_1
Illustrated Access 2016 | Module 8: SAM Project 1b Salvage Database ANALYZING DATA WITH REPORTS GETTING STARTED • Open the file IL_AC16_8b_FirstLastName_1
Subject:MS AccessPrice: Bought3
Illustrated Access 2016 | Module 8: SAM Project 1b
Salvage Database
ANALYZING DATA WITH REPORTS
GETTING STARTED
• Open the file IL_AC16_8b_FirstLastName_1.accdb, available for download from the SAM website.
• Save the file as IL_AC16_8b_FirstLastName_2.accdb by changing the “1” to a “2”.
o Hint: 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.
PROJECT STEPS
1. Create a Parameter Report by completing the following steps:
a. Open the DepositRevenue query in Design View and add a parameter to the Weight field, using the parameter criteria >=[Enter minimum weight]. Save and close the DepositRevenue query.
b. Use the Report button to create a simple report based on the DepositRevenue query. Enter 300 in the Enter Parameter Value dialog box.
c. In Layout View, delete the control containing the page number (in the Page Footer section) and the control containing the count of the LocName field (in the Report Footer section) at the bottom of the report.
d. In Layout View, resize the LocName, LocCity, and OrgName columns in the report, so that the report fits on one page. (Hint: You may need to reenter 300 if prompted by the parameter query.)
e. Save the report with the name DepositParameter. (Hint: Do not type the period.)
2. Open the DepositParameter report in Design View, then change the theme of the report to Office. View the report in Report View, enter 300 for the parameter prompt, confirm the report matches Figure 1 on the next page, then save and close the report.
Figure 1: DepositParameter Report in Report View
3. Open the DepositsByLocation report in Design View. Open the Property Sheet for the report, then set the record source for the report to the DepositsByLocationAndOrg query.
4. Add a title to the report, and confirm that the title reads “Deposits by Location”.
5. While still viewing the DepositsByLocation report in Design View, complete the following actions:
a. Group the report based on the LocName field.
b. Within each group, sort the report based on the DepositDate field from oldest to newest.
c. Add the LocName field to the report in the LocName Header section.
d. Modify the LocName label so that it reads Location. (Hint: Do not type the period.)
e. Cut and paste the Location label to the Page Header section. If necessary, reposition and resize the Location label to the approximate location shown in Figure 2 on the next page.
f. Save the DepositsByLocation report, but do not close it.
Figure 2: DepositsByLocation Report in Design View
6. With the DepositsByLocation report still open in Design View, complete the following steps:
a. Add the Weight field to the right side of the Detail section of the report, as shown in Figure 2.
b. Delete the Weight label associated with the Weight text box.
c. Reposition and resize the LocName text box in the LocName Header section and the Weight text box in the Detail section as needed so that they are located in the approximate locations shown in Figure 2.
d. Resize the Detail section of the report so that it is only as tall as the controls it contains. Also make sure the report is no wider than 8".
e. Save the DepositsByLocation report and confirm that it matches Figure 2 above. View the report in Report View and confirm that it matches Figure 3 on the next page, then close the report.
Figure 3: DepositsByLocation Report in Report View
7. Open the OrganizationDeposits report in Design View, then complete the following tasks:
a. Using the horizontal ruler, select all of the controls and labels from the 6" mark through the 7.5" mark, then move them to the left so that the left edge of the DepositDate label and text box are at the 5" mark on the horizontal ruler.
b. Add DepositDate as a sorting field (from oldest to newest) below the OrganizationID grouping field.
c. Add the OrganizationID field’s footer section.
d. Add a text box in the OrganizationID field’s footer section, then modify the accompanying label to read Total Weight:. (Hint: Include the colon but not the period.) Modify the text box to include the expression =Sum([Weight]).
e. Expand the Report Footer to be about 0.5" tall.
f. Copy and paste the new label and expression from the OrganizationID footer section to the Report Footer section. Modify the label in the Report Footer section to read Grand Total:. (Hint: Include the colon but not the period.)
g. Move the new subtotal and grand total text box and label so that they are in the approximate position under the Weight text box shown in Figure 4 on the next page.
h. Save the OrganizationDeposits report, confirm that it matches Figure 4, then close the report.
Figure 4: OrganizationDeposits Report in Design View
8. Open the DepositsByOrganization report in Design View and turn it into a Summary Report by completing the following tasks:
a. Delete all controls in the Detail section of the report.
b. Cut and paste the OrgName text box in the OrganizationID Header section to the OrganizationID Footer section in the approximate location shown in Figure 5 on the next page.
c. Close the Detail section and the OrganizationID Header section.
d. Remove the Page Header and Page Footer sections (and all controls within those sections) from Design View.
e. Save the DepositsByOrganization report, then confirm that it matches Figure 5 in Design View. View it in Report View to confirm it matches Figure 6 on the next page, then close the DepositsByOrganization report.
Figure 5: DepositsByOrganization Report in Design View
Figure 6: DepositsByOrganization Report in Report View
9. Open the VolunteerList report in Design View, then make the following updates:
a. Add the OrgName Footer section. (Hint: Open the Group, Sort, and Total Pane to access this option.)
b. Modify the Force New Page property of the OrgName Footer section so that a new page is started after each section.
c. Drag the top edge of the Page Footer section up to close up the space in the OrgName Footer section.
10. While still viewing the VolunteerList report in Design View, make the following updates:
a. Move the label in the Report Header section into the Page Header section in the approximate location shown in Figure 7 below.
b. Drag the top edge of the Page Header section up to close up the space in the Report Header section.
Figure 7: VolunteerList Report in Design View
11. While still viewing the VolunteerList report in Design View, use the Format Painter to copy the formatting applied to the Organization label in the Page Header section to the Last Name, First Name, Join Date, and Donation labels in the Page Header section. Save the VolunteerList report.
12. While still viewing the VolunteerList report in Design View, add two conditional formatting rules to the Donation control (in the Detail section of the report) as described below:
a. The first conditional formatting rule should format the Donation text box where the Donation field value is greater than or equal to 500 with the Light Green background color (5th column, 7th row in the Standard Colors palette).
b. The second conditional formatting rule should format the Donation text box where the Donation value is between 100 and 499 with the Yellow background color (4th column, 7th row in the Standard Colors palette).
13. While still viewing the VolunteerList report in Design View, use the Subreport Wizard to add a subreport to the Report Footer section as described below:
a. Add the subreport to the left side of the Report Footer section. In the Subreport Wizard, select the existing OrganizationList report for the subreport.
b. Choose None as the link between the main report and the subreport.
c. Use the default OrganizationList name for the subreport label.
d. Delete the OrganizationList label from the Report Footer section.
e. Resize and reposition the subreport as shown in Figure 7.
f. Save the VolunteerList report, confirm it matches Figure 7 on the previous page and Figure 8 below, then close the VolunteerList report. (Hint: Figure 8 displays the report in Report View, not Print Preview.)
Figure 8: VolunteerList Report - Report Preview
14. Open the DepositListing report in Design View. Add lines to the LocName Footer section as described below:
a. Add a horizontal line above the =Sum([Weight]) control (in the LocName Footer section) in the approximate location shown in Figure 9 on the next page. The line should only be as long as the =Sum([Weight]) control.
b. Add a horizontal line below the Subtotal: label and the =Sum([Weight]) controls (in the LocName Footer) in the approximate location shown in Figure 9. This line should be as long as the LocName Footer section.
Figure 9: DepositListing Report – Design View
15. With the DepositListing report still open in Design View, add lines to the Report Footer section as described below:
a. Add two horizontal lines below the =Sum([Weight]) control (in the Report Footer section) in the approximate location shown in Figure 9. The lines should only be as long as the = Sum([Weight]) control.
b. Save the DepositListing report, confirm that it matches Figure 10 on the next page in Report View, then close the report.
Figure 10: DepositListing Report-A Portion of the Last Page in Report View
Save and close any open objects in your database. Compact and repair your database, close it, then exit Access. Follow the directions on the SAM website to submit your completed project.
