by   
Laura C. Larsson

Building and Revising a Report in Access 97

Report Table of Contents 

Introduction 

If you think of forms as the method of inputting data, reports are the method of outputting information. As with the process of creating forms, you must also think about how you want your report to look, which questions (queries) and data you wish to view and retrieving complete and accurate data. To get accurate data, you must be sure that all data in a field you want to report on is complete - with one exception. You may wish to generate a single claims report for a missing issue. In that case, missing information generates a report to the publisher requesting the missing issue (or issues). 
 

What is a Report? 

Reports can be used to generate claims for missing issues (as mentioned above); can give you information on the growth of your collection, on how much it costs you to acquire journals each year, and can provide you with a list of titles and URLs for journals which have Web pages.  Reports can also include simple calculations, graphs, logos, images and more. In short, reports do something useful with your data. 

Some of the information on your report comes from the underlying queries or tables. The remaining elements come from the report's design. The title and column headings come from the report's design and the data from the underlying table, query or SQL statement. Numerical totals come from expressions which are also stored in the form's design. 

A report is made up of five sections, a report header which often contains the logo and the title of the report, a page header which gives information about the data which comes afterwards, detail (data from the tables), a page footer, and a report footer. 

In Design view, sections are represented as bands in the report. Each section that the report contains is represented once but in the printed report, some sections may be repeated many times. You make the determination as to where information appears in every section by placing controls, such as labels and text boxes. 
 

Before Creating a New Report

Consider which of your tables, forms or queries could be used as the basis of your report. Identify data elements and checking to be sure that all records contain the appropriate data is important. Create a query if your data comes from two or more tables - or if you plan to use derived data. You can also add a logo or picture to the top of the report to make it look more attractive. Then lay out the report on paper to get an idea of what it looks like. 

Sample Reports 

How do You Build a Report? 

Spend some time looking at reports generated by other people. Use them for ideas. It helps to create a file folder of report ideas. Even portions of a report can come in handy. You never know when someone else's idea will come in handy for your work. This is especially true if you're graphically challenged. 
 

Building a List of Journal Titles Report with the New Report Wizard

When the New Report dialog box appears, a variety of report possibilities appear. Design View is used when you want to create a custom report from scratch. Other report formats are also available but will not be covered in this tutorial. 
 

Pick the New Report Wizard

For our purposes, you will select the Report Wizard. As with the other Wizards, the Report Wizard will ask you a series of questions and will help you create the final format using predesigned report templates. 
 

Pick the Table and Fields 

 

Pick the Sort Order

Sort by JournalName. 
 

Pick the Layout of Your Report

Select the Report Style

Decide on the Report's Title

The Final Report

Building a List of Subject Headings and Making it Look Nice

Now that we have the basis for a report, let's fix the format up a little. Select Subject Alpha report, then Design. Click on Subject, then Delete. This gets rid of the extra words, "Subject" which appear on every line. 

Then click on Subject. Now right click on your mouse. Highlight Properties. Move the cursor down the page to Border. Select Border Color and change the color to white. This way, although you still have a border around the subject heading, you can't see it (white on white). This is one of the interesting elements programmed into the software which you just have to work around. 
 

Creating a Report using Design View

Create a new query when you need to use data from more than one table. 

In Design view you can add controls such as text boxes and labels. The process is tedious. You would likely be better off creating a report in 
 

Creating a Report using AutoReport

In the New Report dialog box, click the AutoReport: Tabular wizard: 
 
In the AutoReport: Tabular report , the fields in each record appear on one line, and the labels print once at the top of each page. If you simply wanted a columnar report, use the AutoReport: Columnar. In a columnar report each field appears on a separate line with a label to its left. 

Select the Subject heading query that contains the data you want to base your report on and click OK. 

Saving a Form as a Report

After selecting the form, click the Save As Report button and give the report a name. Preview the new report by selecting the Report tab on the Database window and double-clicking on the report you just created. If it looks okay, you've got a new report. But if your original form is boring, your report will also be boring. Play around with this method of creating a report when you have time. 

Revising a Report 

Again, in Design View you can change the font style and size on controls and rearranee objects on the report to your heart's content. If you think things might get a little out of control, open the report in Design view and save it with another name. Use the new file to practice on. 
 

Adding the Current Date / Time to a Report

Special Reports: Mailing Labels

 In the Database window, click the Reports tab, then New. In the New Report dialog box, select the Label Wizard. Click the table or query that contains the data for the labels, and then click OK. 

Determine which label size you want. European colleagues can select the Metric radio button to select European label sizes. Be sure to select the appropriate radio button to get either continuous feed or sheet feed. Be aware that you can also Customize a label if none of the current sizes are appropriate. 

Click Next. In the next dialog box, select the font, font size, color and weight. You could also select italic or underline by clicking one or both of the check boxes. Click Next. From the available fields on the left, you can select the fields you wish to have read into your label. 

Select {PublisherName} 
           {Address} 
           {Address1} 
           {City}, {StateOrProvince}  {PostalCode}  {Country} 
 
Then Next. 

If you want to sort your labels, you can. Sort by PublisherName by clicking on PublisherName and hitting the > button. Then Next. 

Give your labels a name and take a look at the labels by selecting the "See the labels as they will look printed" radio button. 

If the resulting label report doesn't look the way you want, you can delete the report and run the Label Wizard again. In fact, it will likely take some playing around and printing off several sheets of paper with labels to get exactly what you want. I get my sheet of labels out, print the sheet of labels on a plain piece of paper. Then I hold the printed sheet over the labels to see how far off I am in the layout. I fix the paper label format until everything works correctly. 
 

Example of a sheet of labels (somewhat enlarged)

For More Information 

Next: Future Considerations 

Back: Writing Queries