7 Reports

OBJECTIVES

For which you can use reports.
How to create labels with the Wizard.
Creating an automatically generated report with manual adjustments.
Creating a grouped report.
Creating a report with images of chocolates on labels.

Reports are overviews that are usually meant for printing. Also, labels are a form of reports.

7.1 About creating reports

Reports are usually overviews and summaries of large amounts of information.

If you want to print a sales report clearly on paper, then you use a report. In a report, you can also calculate subtotals and grand totals. You can create reports by hand, but it’s easier to use the Wizard.

Reports can be dynamically by using parameters. When generating the report you will be prompted for additional information. As an example see report Sales per box. When opening this report you are asked to enter a start date and after that for an end date.

7.2 Creating Labels

How to create labels with the Wizard.

In this task, you are going to create address labels for the customers with the Label Wizard. First, you have to select the table with the necessary fields.

Task 7.1 File: candy365.accdb

Open the database.
Select table Customers.
Choose tab Create > Labels (group Reports.
Select measures Metric, manufacturer Avery and then product Avery L7160, see Figure 7.1.

Figure 7.1: Selection label type Avery L7160.

Click Next. In the screen that is displayed now, you can change font and color for the text.
Accept the default settings and click Next.

By double-clicking on a field name, the field is inserted at the cursor location. You can also use button >. The field name appears between brackets on the prototype label. Also, text and spaces can be typed. With the Enter button, you can create a new line.

Create the following prototype label (with 1 space between the first and last name, and 2 spaces between zip code and city):

{FirstName} {LastName}
{Address}
{ZipCode} {City}

Click op Next. In the screen that is displayed now you can specify if the labels should be sorted, and if so, by which fields.
There must be sorted by ZipCode. Add this field and click Next. The last screen of the Wizard is displayed now. Here you can specify the name for the report.
Name the report Labels Customers and click Finish.

Close the report.

7.3 Generated report

Access can automatically generate a report based on a table or a query. Sometimes the generated report is sufficient, but mostly you need to make manual adjustments.

Task 7.2 File: candy365.accdb

Open the database.
Select query Sales per region per box.
Click tab Create > Report (group Reports). The report is generated and opened in Layout View.

It’s not beautiful that the value of Region is repeated for each record and that the Sales values are not properly formatted. In the following steps, this will be changed.

Close the report and answer the question to save the changes with Yes. The dialog box Save As is displayed.
Name the report Sales per region per box and click OK.
Open the report Sales per region per box in Design View.
Select in section Detail the field Region. In the Property Sheet change the value of property Hide Duplicates in Yes.

The Property Sheet is on the right side of the screen and can be made visible and invisible by clicking on button Property Sheet or by using hotkey F4.

Select in section Detail the field Sales. In the Property Sheet set the value of property Format on Currency.
Switch to Report View. The value of field Region is now displayed only once and the sales values are in currency format.
Close the report and save the changes.

7.4 Report with grouping

INFORMATION NEEDS: Create a report which shows over a specified period the sales per box, as well as the details of each order. As an example, see Figure 7.4 which shows part of the report for November 2009.

Figure 7.4: Report November 2009 (partial view).

ANALYSIS

The required data are BoxName, OrderCode, OrderDate, Quantity, and a calculated field Sales from the expression [Quantity]*[BoxPrice]. A query for this data is already available with the name Sales per box per order per period.

Task 7.3 File: candy365.accdb

Open the database.
Select query Sales per box per order per period.
Choose tab Create > Report Wizard (group Reports).

Add all query fields, then click Next. In the screen that is displayed now, you can specify if you want to add grouping levels.
Remove existing grouping levels (OrderCode) and add BoxName as grouping level

Figure 7.6: Specifying fields for grouping.

Click Next. In the screen that is displayed now, you can specify the sort order.
Specify to sort ascending on OrderCode.

Figure 7.7: Specifying sort order and summary information.

Click button Summary Options… and select that the Sum should be calculated for the fields Quantity and Sales.

Figure 7.8: Specifying calculated summary values.

Click OK and after that Next. Now you can specify the layout of the report.
Select Outline layout.

Click Next. The last screen of the Wizard is displayed now. Here you can specify the name for the report.
Name the report Sales per box per order per period and click Finish.

The report is created and displayed in Print Preview. Because the query has parameters for the start and end date, you should enter values for these parameters.

Test with Start date 11/1/2009 and End date 11/30/2009.
Close the report.

7.5 Praline Pictures

In this task, you create a report with pictures of the pralines and the code and name above it. For that, labels are used, and on each label the data of the praline.

Task 7.4 File: candy365.accdb

Open the database.
Select table Pralines.
Choose tab Create > Labels (group Reports).
Select measures Metric, manufacturer Zweckform and then product Zweckform 3415.

Click Next. In the screen that is displayed now, you can change font and color for the text.
Accept the default settings and click Next.
Create the following Prototype label ( with 1 space between the fields):
```
{PralineCode} {PralineName}
```
Click Next. Specify to sort by PralineCode.
Click Next. Name the report Labels Pralines.
Click Finish. The report is generated and displayed in Print Preview.
Switch to Design View.
Click op tab Design > Bound Object Frame (group Controls) and draw a rectangle of approximately 1 inch by 1 inch in the Detail section.

Make sure the frame remains selected, and then apply through the Property Sheet the following changes:

In tab Format: set Width and Height on 1 inch. Access may adjusts the dimensions a little bit. That’s no problem.

In tab Data: specify for property Control Source field Picture.

Select the label which is located substantially behind the frame.

Remove the label with the Delete key.
Switch to Print Preview.

It is now almost good. Only the images don’t all start at the same height so that the representation is chaotic. The text of PralineCode and PralineName should get a fixed height so that all images are placed at the same height.

Switch to Design View, select the text box and set property Height on 0.4 inch. Set also the properties Can Grow and Can Shrink on No.

Align text box and frame to the left.

Switch to Print Preview.

Close the report and save the changes.

7.6 Exercises

Exercise 7.1 Sales per region per box (rapp001)

Create a report with the sales per region per box. The report should also show the total sales for each region and the percentage across all regions. Name the report rapp001.

Exercise 7.2 Sales per box per region (rapp002)

Create a report with per box the sales per region. Name the report rapp002.

Exercise 7.3 Box and PralineCosts (rapp003)

Create a report that shows all pralines in each box, together with the costs of these pralines. Also, display the sum of all praline costs. Name the report rapp003.

Exercise 7.4 Box and Praline costs grouped (rapp004)

Create a copy of the report rapp003 and name the copy rapp004. Make the report more legible to print all data of a box on a separate page. This can be done by inserting a page break before the header of each group. You need to set the property Force New Page of the groupheader with the value Before Section.

Furthermore, make some small changes to the layout. The text box Sum should be moved to the right with only a small horizontal line above the sum.

Every box is a group, so the group header in this report is named Box Header.

Exercise 7.5 Sales per box per order per period (rapp005)

Create a copy of the report Sales per box per order per period and name the copy rapp005. Change the design of the report so that the total amount per box is printed next to the name box.

Exercise 7.6 Box data on separated pages (rapp006)

Create a copy of report the rapp005 and name the copy rapp006. Change the design of report rapp006 so that each box starts on a new page.

Set property Force New Page of the group header (BoxName Header) on Before Section.

Exercise 7.7 Box Labels (rapp007)

Create a report with labels for all boxes according to the following picture. Name the report rapp007.

label type Avery J8163 1 1/2” x 3 9/10”, Font Consolas 12pt normal black.

All Seasons

Code  : ALLS
Weight: 150 gram

Exercise 7.8 Yearly sales per region (rapp008)

A report based on a parameter query and grouping.

Create a report showing the number of boxes sold in a given year per region. Upon opening the report you should be asked for which year you want to see the report. Name the report rapp008.

In the following picture, you see the beginning of the output for the year 2009.

First create a parameter query Yearly sales per region which provides the necessary data for the report and asks for the sales year.