Database Design - Session 3: Creating a simple report

Reports are used to present information in printed form. Not everyone is inclined or able to access information on-line, therefore any workable database should have a means of printing well formatted and highly tailored reports. Most RDBMS packages will allow reports to be exported into other software (Access allows a wide range of exporting options, including the option of "publishing" the report using Word).

Using a report will allow control over:

The layout (page headers, page numbers etc.)
Simple calculations (e.g. give a total number of children)
The order of the records in the report
Creating sub sections (grouping)

Designing the "all students" report.

The "all students" report (Figure 3.1) lists all of the students whose details we have recorded in the table STUDREC.

All Students - page 1 of 2
SREF	INIT	SNAME	DOB	GENDER	RES	KIDS	HTOWN	DISTANCE
41	S	OSMAN	29-AUG-53	M	No	3	HUDDERSFIELD	14
1	TJ	OSMAN	29-Sep-53	M	No	0	MILLHOUSE GREEN	9
2	S	LANGLEY	21-Aug-57	F	No	0	HUDDERSFIELD	14
3	H	WILSON	07-Jul-62	M	Yes	1	HUDDERSFIELD	14
4	J	CARTER	21-Mar-54	F	Yes	2	BARNSLEY	3
5	A	JONES	10-Nov-48	F	Yes	2	SHEFFIELD	14
6	S	ISHEMO	05-Dec-50	M	No	0	LEEDS	34
7	K	ARNOTT	01-Aug-60	F	Yes	2	SHEFFIELD	14
8	B	ARNOTT	23-May-62	F	Yes	1	LEEDS	34
9	N	GREEN	30-Sep-58	M	Yes	1	SHEFFIELD	14
10	H	JACKSON	21-Apr-41	M	Yes	0	SHEFFIELD	14
11	A	ARNOTT	23-Aug-54	M	Yes	2	BARNSLEY	3
12	N	HEY	10-Oct-55	F	No	0	SILKSTONE	1
13	K	WILSON	13-Mar-65	M	No	2	BARNSLEY	3
14	J	BROWN	29-Sep-53	F	Yes	1	BARNSLEY	3
15	A	ARNOTT	23-Aug-54	F	No	1	BARNSLEY	3
16	G	WHITE	03-Mar-65	M	Yes	3	BARNSLEY	3
17	J	GREEN	06-Aug-69	F	No	0	BARNSLEY	3
18	J	GREEN	09-Aug-45	M	No	0	BARNSLEY	3
19	F	WATSON	03-Mar-58	M	Yes	1	THURLSTONE	7
20	L	HARVEY	03-Aug-54	F	No	2	THURLSTONE	7
21	T	MOSLEY	31-Oct-75	M	Yes	2	SILKSTONE	1
22	J	POWERS	30-Aug-45	M	Yes	0	HENDON	128
23	J	CHESTER	15-Mar-60	M	No	0	BARNSLEY	3
24	J	MARSHAL 03-	Jul-50	F	No	2	THURLSTONE	7
25	P	ARNOTT	30-Jul-65	F	No	1	SHEFFIELD	14
26	W	PURDUM	01-Sep-69	M	Yes	0	SHEFFIELD	14
27	B	NADIR	24-Sep-58	M	No	0	SHEFFIELD	14
28	A	OLIVER	24-Feb-58	F	Yes	1	SHEFFIELD	14
29	T	JAMISON	23-Jan-29	F	Yes	0	SILKSTONE	1
30	C	ARNOTT	03-Apr-53	F	Yes	0	HUDDERSFIELD	14

22 August 2004:							Page 1 of 2

Page 2 of 2 - All Students
SREF	INIT	SNAME	DOB	GENDER	RES	KIDS	HTOWN	DISTANCE
31	D	LEE	01-Jun-53	F	Yes	0	HUDDERSFIELD	14
32	E	DAVIS	02-Nov-53	M	Yes	0	LEEDS	34
33	F	NADIR	12-Dec-53	F	Yes	0	SHEFFIELD	14
34	G	HOLLAND	27-Dec-48	M	No	0	ROTHERHAM	20
35	H	KNIGHT	12-Jun-70	F	Yes	1	DONCASTER	26
36	I	ARNOTT	19-Jun-72	F	Yes	2	ROTHERHAM	20
37	A	WHITE	20-Feb-53	M	Yes	1	SILKSTONE	1
38	G	DAVIDSON	20-Feb-53	F	Yes	1	THURLSTONE	7
39	U	CAPRA	12-Feb-77	F	Yes	2	HUDDERSFIELD	14
40	R	AKBAR	30-Mar-78	M	Yes	0	HUDDERSFIELD	14

22 August 2004							Page 2 of 2

Figure 3.1: The "all students" report

Practical Exercise:

If you haven't already done so, load Access and ensure that the database window (the central "control form") is displayed on your screen
Click on the REPORTS tab of the database window
Begin to create a report by clicking on the NEW button

This will display a "New Report" dialogue box (Figure 3.2).

Screen Shot of the "New Report" dialogue box

Figure 3.2: New Report Dialogue Box

We need to define where the data for the report is to be taken from, this can be either a query or a table.

Click on the "pull down arrow" in the "chose a table or query" text box and select the table STUDREC (see Figure 3.3).

Screen Shot: Choosing STUREC to get the data from"

Figure 3.3: Choose the table STUDREC

We now need to determine how the report is to be generated. The basic choices are between doing everything yourself (Design View) and allowing Access to automatically generate a report for you (everything else). As this is our first report, we'll create it the quick way by using one of the Access wizards.

Select the "Report Wizard" and click on the OK button, this will show the first Screen of the Report Wizard (Figure 3.4).

Screen Shot: "Choosing Which fields to include in the report"

Figure 3.4: Report Wizard (screen 1)

Choosing the data fields to be displayed is one of the controls that you have over the presentation of your report. It is important to realise that you do not have to display all data in all circumstances.

You may have noticed that the wizard informs you that you can have more than one table or query as the basis for your report. This will be revisited when we start our discussions around relational databases. For now, selecting the single table STUDREC (as you have already done) will be sufficient.

In this example we are going to choose to use all data fields, so:

click in the "double right arrow" >>

to select all of the available data fields. You can check that you've got this right by inspecting the "selected fields" list on the right of your wizard screen.

Click on the NEXT button to move to the "grouping screen" (Figure 3.5).

Screen Shot: "Grouping Levels"

Figure 3.5: Grouping

Grouping options allow us to sub-divide the report, for example to group those students from the same home town together in one section of the report. We'll look at this in more detail later in this section.

Press the "Next" button to move to the sort order dialogue box Figure 3.6).

Screen Shot: "Sorting data in a report"

Figure 3.6: Sorting

Sorting data is an important feature of report design, however as we are keeping this example simple, we'll skip this section by clicking the "NEXT" button. This will bring us to the report layout section of the wizard (Figure 3.7).

Screen Shot: "Defining the page layout"

Figure 3.7: Page Layout

Select a Tabular Layout
Ensure that the paper orientation is set to "Portrait"
Ensure that the box to adjust field width is ticked (this will narrow field widths if needed in order to ensure that all of the fields fit on the page.
Click on the "NEXT" button to move to the STYLE options of the wizard (Figure 3.8).

Screen Shot: "selecting a pre determined style for the report"

Figure 3.8: Style

Select the FORMAL style
Click on the "NEXT" button to move to the final screen in the report wizard (Figure 3.9)

Database Design: Reports - Creating a Simple Report

Designing the "all students" report.

Practical Exercise: