Report Exercise

Educational Goals

The purpose of this assignment it to help students learn how to use the report wizard to develop reports and to use the design window to refine the reports and make them more readable. In the process, this assignment should help students learn to establish additional groupings and subtotals in reports, and to control the sort order for the report and entries within groupings. All these issues are covered in the Reports Chapter 11.

Assignment

Implement a Sales Journal in Tom's Trailer Sales database. The Sales Journal should look like the example below. The report has the default page footer that the report wizard creates.

I have added a Chart of Accounts table to the database for you to use to use to get the account names for the report. You also will need to use the "Is Not Null" function as a selection criteria so that your report only picks up orders that have been delivered. Since this is a sales journal, only sales that can be recognized under GAAP's revenue recognition criteria should be included. For Tom's, that means only order that have been delivered to the customer.

The report should look like the example at the end of the assignment.

This is a complex report. I recommend that you follow the following steps in developing it:

1) Start by identifying which tables contain the data you will need for the report.

2) Then, use the report wizard to tell Access™ the fields you need from each table. Lay out your fields in the wizard from top to bottom in the same order they will appear in the report from left to right.

3) You will need two groupings in your report, one for each of the two subtotals in the report: Account and Sales Order Number. You will need to add the Sales Order grouping and make it the second highest grouping in the grouping priority. Unfortunately, Access™ insists on adding additional groupings that we won't need, but we will have to delete in the design view of the report.

4) The wizard will have put the delivery date in the detail field instead of the Sales Order Number group. You will need to move it. First, you will need to reduce the size of the some fields to develop room to move things around. Then, move the Deliver Date label and the Deliver Date text box over next to the order number. Finally, move the Delivery Date text box up into the Sales Order group.

5) The wizard will have created a grouping for merchandise name, which you will need to delete. Move the Merchandise Name text box out of the Merchandise.name group and down into the detail. Then, go to the group options and delete the Merchandise.name group.

6) Next, add a text box that calculates "Amount" and edit the record source to only include orders that have been delivered

a) First, you will need to go the report's Record Source and create a field for "Amount" that multiplies the price times the quantity.

b) While you are editing the record source, add a criterion to the delivery date field that eliminates orders without a delivery date. You can do this by typing in "Is Not Null" in the criteria field.

c) Next, you will need to add a text box and label to show this amount on the face of the report. The simplest way to do this is to copy the Price text box and label; redo the label to say "Amount" and then edit the new text box's Control Source property in the Data tab to tie to the "Amount" field you just created.

7) Add a subtotal for Amount in the footer to the Sales Order Number grouping. The text shows you how to add subtotals to groups. Be sure to include the Grand Total option so that you will get a total at the end of the report. Once you have the text box in the group footer, you may need to move it around.

8) Add the label shown in the sample of the report. However, you will need to use a text box to create the label so that you can show the Sales Order #. Insert a blank text box next to the subtotal in the Sales Order Number footer and use the expression building in the text box's control source to concatenate "Sales Order # " with the value in the Sales Order Number field.

9) Add the line shown in the example report.

10) Add a subtotal in the Account Number group footer for the Amount field and add a label for it as shown in the example report. You can add the line by copying the one you built above.

11) Add lines and a label to the report footer as shown in the example report. The fastest way to do this is copy the label you made for the Sales Order # subtotal as well as the line you made to the Report footer and retype the contents of the label.

[pic]