Practice Activities

Lesson 7: Advanced Report Design, part 2

Try This: Do the following steps
 1. Open the Brown Bag Lunch database you have been working on. Or, you may download BBL Adv ver7.accdb.

2. Create a Select Query and add the following Record Sources: tblReceiptProducts, tblProducts. JOIN the Tables by Key data:
From ProductID in tblReceiptProducts to ProductID in CompanyNameSQ

3. Add the following Fields to the QBE Grid:
From tblReceiptProducts: ReceiptID, DateReceipt
From tblProducts: Specialty, Type, Description, Price
Sort by Specialty (ascending)

4. Save the Query as rptReceiptProductsSQ. Run the Query to test it.

5. Return to the Design View and select the Totals in the Query Tools. Change the Price from Group BY to Sum. Save the Query and Run it again. This Query should calculate the SUM of the Prices for each customer. Close the Query.

6. Create a new Report with the Report Wizard.

Select rptReceiptProductsSQ as the Record Source.

Select all available Fields.

View this Report by tblReceiptProduct. Group by ReceiptID.

Sort by Specialty then by Description.

Select to Summary and SUM the Price.

Choose the Stepped Layout. Enter the Title: ReceiptProductsSubform.

Check to Preview the Report and Finish the Wizard.

 

7. Review this Report in Print Preview. Close the Print Preview. Edit the Report in Design View.

Delete the Labels in the Page Header. Resize the Page Header so that the Height is 0”.

8. Select the Report Header and edit the Property for Visible: No.

9. Select the ReceiptID Header and edit the Property for Visible: No.

10. Select the ReceiptID and edit the Property for Visible: No.

11. Select all of the Controls the Detail Section and edit the Property for Can Grow: Yes.

12. Save and close the ReceiptProductsSubform.

13. Open the Brown Bag Lunch Co Report in Design View.

14. Use the Subform/Subreport Wizard to add a Subreport to the Detail Section. Start the Wizard by clicking under the Name and Address Controls in the Detail Section. Use these answers for the Wizard: Use an existing Report: ReceiptProductsSubform. Define your own link: ReceiptID. Edit the Label: ReceiptProductsSubform and finish the Wizard.

15. Select the Subreport and use the Report Design Tools to Format the Shape Outline Color: Transparent.

16. Save the Brown Bag Lunch Co Report. Go to Print Preview and see the print out. Not bad?

17. Close the Brown Bag Lunch Co Report.

18. Close the Brown Bag Lunch database.