Practice Activities

Lesson 6: Advanced Report Design, part 1

Try This: Do the following steps
 1. Open the Brown Bag Lunch database you have been working on.

Or, you may download BBL Adv ver6.accdb

2. Create a Select Query and add the following Record Sources: tblReceiptProducts, CustomerNameSQ, tblProducts.

JOIN the Tables by Key data:
From CustomerID in tblReceiptProducts to

Customer ID in CompanyNameSQ


From ProductID in tblReceiptProducts to

ProductID in tblProducts.

 

3. Add the following Fields to the QBE Grid:
From tblReceiptProducts:

ReceiptID, DateReceipt

 

From CompanyNameSQ:

Company, Fullname, Address, CityStateZip, Phone

 

From tblProducts: Price

4. Save the Query as rptReceiptSQ. 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 rptReceiptSQ as the Record Source. Select all available Fields. Group by ReceiptID. Choose the Stepped Layout. Enter the Title: Brown Bag Lunch Co.

Select to Preview the Report and Finish the Wizard.

7. Close the Print Preview. Edit the Report in Design View.
Delete the Labels in the Page Header.

8. Arrange the Controls so that they look like a business address.

9. Select and edit the following Controls: DateReceipt , Fullname, Address, CityStateZip, Phone
Width: 3”
Left: 0.25”
Text Align: Left

10. Move the Price Control to the Report Header. Format the Price Control so that the data is aligned right. Add a Label: Total this Receipt.

11. Format the Properties for the DateReceipt: Medium Date.

12. Save the Brown Bag Lunch Co Report. Go to Print Preview and see the print out. Return to Design View and adjust the Controls as needed,

13. Close the Brown Bag Lunch Co Report..

14. Close the Brown Bag Lunch database. Get a cookie, if you wish.