The system requirements document for your STRS (strs-req.doc) indicates that 6 reports are required:

• Tutorial Allocation (Figure 2)
• Tutorial Attendance Listing (Figure 3)
• Assessment Listing (Figure 4)
• Final Result Report - surname order (Figure 5)
• Final Result Report - result order
• Final Result Report - course code order

So we had better get started.

The process for constructing a report is:

• analyse the report to decide which fields are in it and which tables the fields are in
• create a query that contains the required fields
• decide on the sorting order for the records in the query
• use the report design window to create the report based on the query

I will give instructions below for the Tutorial Attendance Listing and you can try the other reports.

I am going to assume that you have done some query design in a previous subject. If you haven't, ask for help.

Creating the Tutorial Attendance Listing (Figure 3)

Analyse the Report

This report allows the tutors to keep a record of the students that have attended each tutorial.

Each tutorial needs to appear on a seperate page.

The fields and their tables are:

The sorting order is tutorial number then student surname then student given name.

Create the Query

In the Create tab and Other group select Query Design. The Query Design window should appear with a Show Table window over the top.

Add the Tutor, Tutorial and Student tables then Close the Show Table window. NOTE that the tables AND their relationships appear at the top of the Query design window like the Figure below.

Drag the fields down from the tables to the Field row of the query design area in the following order - t_name, t_phone, tute_no, tute_time, tute_room, stud_surname, stud_givenname.

To get the sort order right for the query in the Sort row choose ascending for the sort order for the following fields - tute_no, stud_surname, stud_givenname.

Your query should now look like the Figure below:

Save the query and call it TutorialAttendanceListing

Test the query to see if it works - Results tab, Run. Within each tutorial the student surnames should be in alphabetical order. Where 2 students have the same surname they should be listed in given name order.

Create the Report

We will use the Report Wizard to generate the Tutorial Attendance Listing report and then the Report Design window to customise it.

To start the Report Wizard - Create tab, Reports option, Report Wizard.

From the Tables/Queries drop down box select the TutorialAttendanceListing query to underlie the report.

Transfer all the Available Fields to the Selected Fields except for tute_no which we don't need to appear on the report. Press the Next button.

In the next window choose to view the data by Tutorial. Note that the report is starting to take shape in the yellow-bordered window. Press the Next button.

In the next window DON'T add any grouping levels. We have already taken care of this in our query design. Press the Next button.

In the next window DON'T choose to sort. We have already taken care of this in our query design. Press the Next button.

In the next window accept the default values. Press the Next button.

In the next window choose any style. Press the Next button.

In the next window make the title Tutorial Attendance Listing. Choose to Preview the report. Press the Finish button.

A preview of how the report will print should appear. How close is the design to what we want? What changes are needed?

Close the Print Preview window. The Report Design window appears. Note that the report design is in a number of sections.

We now need to customise the report design in a number of ways:

• The Tutorial Attendance Listing title is in the Report header section and will not appear on every page. We want it to appear on every page. Create some space in the Page Header section. Drag the title from the Report Header section to the Page Header section.
• Delete the Report Header section - right click anywhere in the Report Header section, click off the Report Header/Footer icon ner the bottom of the list.
• In the Page Header section we need to delete the column heading labels - t_name, t_phone, etc. This is tricky because they are tied to data entry areas in the tue_no Header section. Select a label, for example t_name. In the Arrange tab and the Control Layout option group select Remove. Now press Delete. The fields down in the Detail section may get all messed up but you can fix this later. Repeat the procedure for the other labels in the Page Header section. Now add the subject name as per the report design to the Page Header section.
• In the tute_no Header section we need to indicate that we want a new page for each Tutorial group. Make sure the Property Sheet is displayed then click anywhere in the tute_no Header section. Down in the Properties list is Force New Page. We want a new page before each section.While your in the tute_no Header section move the fields around so the report looks more like the required design.

Your report design should now look similar to the Figure below. Run the report.

Create the Other Reports

You now have all the techniques to create the other reports. Away you go!