Article Index
Microsoft Access Tutorial
Queries
Creating A Database
Tables
Creating Tables
Queries
Forms In Microsoft Access
Reports In Microsoft Access
Data Access Pages
Controlling The User's Input
Subdatasheets
Designing Forms
Exploring And Analyzing Data
Tables Looks
Design And Exploration
Forms Design Propertie
Controlling Data Output
Microsoft Access Controls
Design And Improvements
Importing And Exporting Data
Macros
Switchboards

Many people will probably spend more time looking at, or working from, your reports. That's why you should spend a great deal of time designing them and configuring their functionality.

Although reports appear to represent the printed sheet of paper that will display some of the effectiveness of your database, they are very valuable because it is from here you will print peoples time sheets, business receipts, company brochures, customers mailing labels, etc. Some reports are quite easy to design as their minimum purpose doesn't require too much behind the scenes functionality.

Some other reports can involve (a lot of, or some) coding, where you have to conditionality print some details concerning particular issues. As we go little by little, we will get to more complicated reports, like designing and printing paychecks that depend on many factors (regular time, overtime, vacation hours, bonus...).

A Simple List:

In its simplest form, a report can just consist of names of members of a club, students in a class room, or your friends.

Open the Music Collection7 database.

On the Standard toolbar, click the New Object arrow and choose Report.

In the New Report dialog, choose Design View and specify the source data as tblArtists. Then click OK.

Right-click anywhere on the empty report and choose Report Header/Footer.

On the Toolbox, click the Label, then click under the Report Header bar. Type Music Collection - Artists Names and press Enter.

From the Formatting toolbar, change the label's font to Garamond, size=20, Bold, and color = Green. Right-click on the label and choose Size -> To Fit.

On the toolbar, double-click the Label and click on the left side under the Page Header bar. Type Nbr: and press Enter. Click on the right of the Nbr label to add another label, type Artist Name and press Enter. Click the label on the Toolbox to give it back.

Select both labels (you can click inside the vertical ruler just left of the Nbr label). On the Formatting toolbar, change the font to Garamond, Size 14, Bold, color = Brown (2nd column, 1st row). While both labels still have focus, right-click any of them and choose Size To Fit.

You should have the tblArtists list on the screen. Drag the ArtistID from the list to the left side under the Detail bar. Delete its label. Resize it to be the same width as the Nbr label. Drag the ArtistName from the list to somewhere in the Detail section. Delete its label. Move the ArtistName text box under the Artist Name label and to the right of the ArtistID text box. Enlarge the ArtistName text box so that it would fit/stretch from dimension 1 to 4 of the horizontal toolbar.

Select both text boxes. Change their font to Verdana, Size=10, color = Blue.

Shorten the Detail section so that it would just fit the height of the text boxes.

Save the report as rptArtistsNames.

Preview the report and close it.

Report Design:

The techniques you use to design a form are the same you apply when embellishing your report. The difference, which is what you will always keep in mind is that while the former is used a the interactive screen interface serving the users, the latter will constitute the printed sheet.

The same toolbox is used to add the labels, text boxes, rectangles, lines, etc.

From the Database Window, click the Reports object button. Then click the New button.

From the New Report dialog, choose Design View. In the combo box, specify tblAlbums as the source of data. Click OK.

You need the following four items on your screen. If you don't have them, you know what to do to get them. They are the Report1 empty report, the tblAlbums Field List, the Toolbox, and the Report Properties dialog.

Right-click somewhere on an empty area of the Report1 and choose Report Header/Footer. Now we have a foundation.

From the Toolbox, click the Label, then click somewhere under the Report Header bar. Type Music Collection - Albums and press Enter. Using the Formatting toolbar, change the Font to Garamond, side=20, Bold, color=Green. Right-click on the new label and choose Size To Fit.

From the Toolbox, click the Rectangle, then draw a rectangle that covers the title label completely. In the Rectangle Properties dialog, specify good characteristics:

Drag the AlbumID from the tblAlbums list to the Page Header section under the horizontal bar.

Change the Caption of the AlbumID label to Album Number.

Select both the label and the text box (you can click inside the vertical ruler to the left of the label. Change their font to Garamond, size=14, Bold, color Brown. While both items are still selected, right-click one of them and choose Size To Fit.

Click somewhere else to deselect. Make sure the text box doesn't cover the label. If necessary, move the text box to the right. Enlarge the AlbumID text box.

Right-click the line you just drew and choose Copy. Press Ctrl + V. Both lines are present, one on top of the other. Drag one them under the label and the text box. Adjust both lines so that the Page Header section will look harmonized.

On the Field List, click ArtistID. Hold Shift and click NumberOfTracks. Drag the whole selection under the Detail bar.

Select all items in the Detail section. Change their font to Verdana, size=10, and color Blue. Enlarge the ArtistID text box to accomodate long names.

Set the YearReleased and the NumberOfTracks text boxes alignments to Left (because they are right aligned, since they are numbers.

Drag the Notes to the right of the selection. Change the Notes text box's font style to Italic, change its font to Verdana, size=8, and color Blue. Select the Notes label and change its font to Verdana, size=10, and color Blue .

Save the report as rptAlbums and close it. If you want, you can preview it, but there is a chance you would wonder why we had put the AlbumIDs in the Page Header section, which might make it not look very good to you. That's because this form will be used it the next exercise. On your own, you could put the AlbumIDs label and text box in the Detail section. In the next section, you will be using another report...

Subreports:

ust as you are already familiar with subform, you can also create a subreport to embed in a parent form. you could get an employee's yearly vacations and bonuses in one query/report, then embedded them in his quarter or annual report.

It is usually better to create the subreport from a query. That gives you more flexibility and control over what to display and when.

Creating A Query As The Data Source:

As we are still in the Music Collection 6 database, in the Database Window, click the Queries button. Then click the New button on the Database Window's toolbar.

In the New Query dialog, choose Simple Query Wizard and click OK.

In the Tables/Queries combo box, choose Table:tblTracks.

From the Available Fields, double-click the following fields: AlbumID, TrackNumber, TrackTitle, and TrackLength. Click Next twice.

Name the query sqyTracks. Click Finish.

Close the sqyTracks query.

Creating the Subreport:

From the Database Window, click the Reports button. Then click the New button on the toolbar.

In the New Report dialog, choose Report Wizard. In the combo box, choose sqyTracks. Click OK.

Select all fields and click Next three times (accepting the defaults).

For the report layout, choose Tabular and click Next twice.

For the title of the report, type Album's Tracks. Click the Modify The Report's Design. Click Finish.

In the Page Header section, right-click the Album Title label and choose Properties. In the All tab, set its Visible field to No. In the Detail section, click the AlbumID. Set its Visible field to No.

Change the Album's Tracks label's font to Garamond, size 16, Bold, color Brown. Right-click on the label and choose Size To Fit.

Since the Album Title label and the AlbumID text box will not be seen, shrink them to 1/16 to the left. Move the other items to the left.

Delete everything in the Page Footer section.

Bring the Report Footer bar completely up close to the Page Footer bar.

Save and close the subreport. If its name is confusing, rename it sbrTracks.

Adding the Subreport to the Parent Report:

From the Database Window, open the rptAlbums1 report in Design View. If you have too many items on the screen and they are taking too much space, close the Properties, and the Field List, we need the Toolbox.

On the Toolbox, click the Control Wizard button to deselect/uncheck it.

Click the Subform/Subreport button and click in the report under the lower red line. Click somewhere to deselect. Click the Child label and delete it. Right-click the subreport and choose Properties.

Give to your subreport the following properties:

Preview your report and make some adjustments to improve its looks. Save your report, then close it.