 |
|
|
|||||||||
 |
|||||||||||
|
|||||||||||
 |
|||||||||||
|
|
 |
|
Using AutoReport
1. Introduction.In this session we'll be taking a look at various ways of producing output from your database. We've already had a brief look at forms, which are useful for inputting data and viewing it on the screen, but if hard copy is required, you need to use an Accessreport. Although it is possible to print a form, the results are rarely satisfactory for several reasons. Firstly, the screen is a different shape from a standard A4 page, and secondly, forms are often designed using coloured text, backgrounds and shading, which do not reproduce well when printed. Access 97 provides several different options for creating a new report including: · AutoReports · A Report Wizard · A Label Wizard · A Chart Wizard 2. Using AutoReport2.1. Creating a columnar AutoReport.AutoReport provides a very quick method of creating a basic report from a table or query but you have very little control over the layout of the report. You can create either a Columnar or a Tabular report, and we'll try both. In the Database window, select the Reports tab and click New. In the New Report dialog box displayed in figure 1, select AutoReport: Columnar and Customers in the bottom pull down list box for specifying the table or query where the object’s data comes from.
Figure 1. Click OK and the report should be automatically created, appearing as shown below in figure 2.
Figure 2. The report style comes from the last style you used if you have already created a report using the Report Wizard. If you have not yet created any reports, this report appears in the default Normal style. As you move the pointer over the report on the screen you will see that it takes the form of a magnifying glass with a minus sign in the middle of it. Left-click the mouse button and the view zooms out so that you can see how the whole page looks. You will notice that Access automatically adds a footer containing the date and the page number. The magnifying glass now contains a plus sign, and the text you place it over when you click the left button will appear near the centre of the screen. Place the magnifying glass over the bottom left hand corner of the report and click the left mouse button. The view returns to full-size and you can see the date in the footer. This is not a particularly attractive looking report; so close it without saving it. 2.2. Creating a tabular AutoReport.In the Database window, again select the Reports tab and click New. In the New Report dialog box, select AutoReport: Tabular and Customer in the bottom list box. Click OK and the report should be created, looking like the figure below in figure 3. Note that the Credit Limit and Sales Rep Number fields have been omitted because of space constraints.
Figure 3. Click the mouse button to zoom out; you will see that the report is laid out in Landscape view. 2.3. Editing the report formatThis report is in many ways preferable to the columnar format - it uses a good deal less paper when printed, but it could be improved. You can, for example, change the report title, centre the field for County and type your name in the Page Footer section of the report. Zoom in again if necessary to return to normal view. Then click the Design View button on the toolbar. The report appears in Design view as shown below in figure 4
Figure 4. Anything appearing in the Report Header section will appear once at the beginning of the report. Anything appearing in the Page Header section will appear at the top of every page. The Detail section contains the data fields Anything appearing in the PageFooter section will appear at the bottom of every page. Click in the default heading box, which says Customer. Black squares known as handles appear around the box. When you place the pointer over the larger handle in the top left hand corner, it changes to a pointing finger, and you can drag the entire box to a different location. When you place the pointer over any of the other handles, it changes to a double-headed arrow and dragging it will change the size of the box. Click again in the Customer box and the pointer becomes an insertion pointer. Edit the heading so that it says Customer Details. Click in the text box for Name in the Detail section (not the label in the page Header section). Handles appear around it. Click the Center box on the Formatting toolbar. Return to Print Preview to view your changes. The Balance field is much longer than it needs to be. Return to Design view. Select the label Balance, and hold down the Shift key while you select the data field Balance. Then drag the middle handle on the right of either box to decrease the width of the boxes. Return to Page Preview, and the report should look something like the one below in figure 5.
Figure 5. Save the report as rptCustomer. 2.4. Adding a new field to the reportIn Design view, the Toolbox should be visible on your screen. If it is not, click the Toolbox icon on the toolbar. (The toolbox may be 'docked' under the Standard toolbar.) From the Toolbox select the Label tool and drag out a box in the Page Footer section of the report. (In Access 7 the Label tool is slightly different in appearance, a single uppercase A.) Type your name in the label box. 2.5. Formatting the reportYou may want to make some adjustments to the report layout before you print it. For example, it is shown in Landscape View on the screen, but you may prefer to print it in Portrait view. From the menu, select File, Page Setup. Click the Page tab and select Portrait. Click the Print Preview button. The main part of the report is shown in Portrait view, but you will see from the page selector at the bottom of the screen that the report spills onto a second page. Go to page 2 and note that it contains part of the line from the Detail section and Page 1 of 1 from the Footer section. (Access regards this as a one-page report that just happens to be too wide to print on one sheet of A4 paper.) You need to move the objects (fields, line and page number) to the left. Return to Design view, and scroll to the right so that you can see the edge of the report. Select the line in the Page Footer section so that handles appear. Place the pointer over the right hand handle so that it becomes a double-headed arrow, and keeping one finger on the Shift key, drag it to the left to shorten the line to line up with Post Code. (Keeping Shift pressed ensures that the line will move only horizontally so it will not become misaligned.) Select the field ="Page " & [Page] & " of " & [Pages] in the report footer and drag it to the left to line up with Post Code. Delete fields Balance, Credit Limit, and Sales Rep. Numberfrom the Details section. Select the field Now( ) and make it narrower. (Now( ) is a function giving the current date and time and is identical to that used in Excel.) Click the Print Preview button. You may get a warning message: “The section width is greater than the page width, and there are no items in the additional space, so some pages may be blank.” Click OK. What this means is that you need to make the report area narrower to fit the page. Place the pointer over the right edge of the report area and drag it to the left. Before printing you should check the page selector to see how many pages will be printed. If there is more than one for a short report, probably the Page Footer has spilled on to a second page and the report needs to be made narrower. When you are happy with the report, select File, Print. Save and close the report. 3. Use the Report Wizard to create a Sales Rep. report.The Report Wizard gives you more control over the design and layout of your report. We'll start by creating a simple Sales Representative report. In the Database window, make sure the Reports tab is selected and click New. In the New Report dialog box, select Report Wizard and SLSREP in the bottom list box. Click OK. Add all the fields to the Selected Fields list as shown below, and then click Next. (It is quickest to click the double arrow between the list boxes to move all the fields to the Selected Fields box, and then select and move the two fields you don't want back to the Available Fields box.) In the next dialog box select Sales Rep and click the arrow between the list boxes to have the records grouped by Sales Rep number. Click Next.
Figure 6. Select Lastname and Firstname as the two fields to sort on in Ascending sequence. Press the Summary Options button in this window. The Summary Options window is displayed. We will display the Sum of Sales on the report. Click the appropriate boxes to put check marks in them.
Figure 7. Click OK, and Next. Now, you have to select a report layout. Have a look at the options – I have selected Align Left 1. Make sure Landscape is checked and the check box to fit all fields on one page. Click Next.
Figure 8. Browse through the styles in the next window. I have selected Corporate. Click Next. In the next window, type Sales Representative Details as the title for the report, and click Finish. The report appears, showing totals for each Sales Representative and a grand total at the end, as shown below. You can make any alterations that you require by switching to Design View.
Figure 9. Save the report and close it. It will automatically be named Sales Rep. Details. Right-click the name in the Database window and rename it rptSalesRep. 4. Basing a Report on a Query.4.1. Creating the Sales Rep query.First of all, we need to create the query that will be used as the basis for the report. The query will be named qryCustNamesbySalesRep and will print the customer number and name for each Sales Rep. The Sales Rep Number, Lastname and Firstname will also be reported. In the Database window select the Queries tab and click New. Select Design View and click OK. In the Show Table dialog box, select and add Customer and SLSREP. Make entries so that your query grid looks like figure 10. Note that Sales Rep Number sorts the query as it is the index and the first column in the QBE.
Figure 10. Run the query and the results should be as displayed in figure 11.
Figure 11. Save the query as qryCustNamesbySalesRep and close it. The next stage is to create the report based on the query. 4.2. Creating the Sales Rep. Report.Use the Report Wizard to create a new report based on qryCustNamesbySalesRep. In the next window, click the double arrow to add all the fields to the list of selected fields. In the next window, scroll through the options in the box labelled How do you want to view your data? Select SLSREP and click Next. Do NOT add a grouping level as the Sales Rep Number, Lastname and Firstname should have been already selected. Click Next. In the next Sorting and Grouping dialog box do NOT add any sorting options and again click Next. Select Align Left 1 as the style for the new report. Click Next. Select Corporate style for the report and click Next. Give the report the title Customer and click Finish. The report should appear as outlined in figure 12.
Figure 12.
4.3. Sorting and Grouping the Report.The report needs to be grouped in order that details for each Sales Rep are printed on a different page. Right click the square at the intersection of the ruler lines and select Sorting and Grouping. (If you are less adventurous, click the Sorting and Grouping icon on the Report Design toolbar.) The sorting and grouping dialog box should appear, and the report is already sorted by SalesRepNumber (major) and LastName (minor). In the Group Properties at the bottom of the screen, select Yes for Group Footer for the SalesRepNumber. You need the footer to allow insertion of a page break control. The Sorting and Grouping dialog box should appear as below in figure 13.
Figure 13. Close the box and drag the sections so that they are quite close together. In Design view, your report should appear as in figure 14.
Figure 14. 4.4. Inserting a page break between Sales Reps.Click the Page Break icon on the Toolbox. If the Toolbox is not displayed you can display it by selecting View, Toolbars, Toolbox from the menu. The Page Break icon is the eighth icon down in the left column of the toolbox. Continuing in Design view, click in the SalesRepNumber Footer section near the left margin. The page break should appear as a short dotted line. Switch to Print Preview and your report should display details for each Sales Rep on a different page. Save the completed report and give it a descriptive name to allow easy identification from the Database window.
|
|
|
|
[Home] [Database Creation] [Queries] [Maintenance] [Reports] [Normalisation] [Switchboards] [Macros] |