Subdiv report Creating reports in Microsoft Access database. Creating a report as a database object

1. Basic information about reports.

2. MS Access DBMS tools for creating reports.

3. Creating a report in Design mode.

3.1. Setting report page options

3.2. Report structure

3.3. Formatting a report

To obtain a printed document in the MS Access DBMS, several options are provided:

Printing a table in table mode (displaying all records or records that meet the filtering conditions in table form);

Print a set of query records in table view;

Print a form in form mode or table mode;

Print the report.

1. Basic information about reports

Report– a customizable dialog box that allows you to organize a formatted presentation of data intended for output to a screen, printer, or file. IN MS DBMS Access reports are database objects.

Reports are the best way to present database information in a printed document. Compared to other means data output for printing, reports have 2 fundamental advantages:

– provide ample opportunities for grouping, sorting and calculating subtotals and grand totals for large sets records (calculations can be made by group, several groups and for the report as a whole);

– allow you to output data in a form close to standard document forms.

Stages of creating a report.

At the first stage, a layout of the future report is developed on paper. At the same time, it is indicated: from which table or query it is necessary to display data, the levels of grouping and sorting of data are determined, which fields should be presented in the report and which controls will be used to display the data, whether calculated fields are needed, which graphic elements will be used to give professional looking report.

At the next stage, the report is constructed using a DBMS.

2. DBMS toolsMS Accessto create a report

To create a new report in the MS Access DBMS, you need to go to the tab in the database window Reports and click on the button Create . In the dialog box that appears New report(Fig. 10.1) select a report creation tool.

All tools listed in the dialog box list New report, can be divided into three main groups:

– tools for automatic report creation (Autoreport: column, Autoreport: tape);

– report creation wizards (Report Wizard, Chart Wizard and Mailing Labels);

– report creation designer.

Fig. 10.1. New Report Dialog Box

Automatic report creation tools allow you to create a report of the appropriate type based on the selected data source: table or query.

Report creation wizards allow you to create reports in dialogue mode with the user. Using wizards, you can create a data source for a report, select the type of data presentation, define fields for grouping, sorting and summarizing, select summary functions for a group, define the report layout and design style, and set the name of the report.

The report designer is the main tool that allows you to create reports of any complexity manually. The type of report and its content depend on the qualifications of the report developer.

Typically, automatic report creation tools or wizards are used to create a prototype report, and the report designer is used to polish the report to a professional look.

Working with reports can occur in three modes: in mode, in mode and in mode.

The mode is intended for generating a report and displaying all data from the source of records on the screen, the mode is intended for quickly generating a report based on mechanical substitution of data from several records of the data source (selection and sorting criteria are ignored), the mode is intended for creating and changing the structure of the report. To change the mode of working with the report, you can use the button View on the toolbar Report designer or similar menu command View.

3. Creating a report in Design mode

To create a report in the mode, you must perform the following steps:

1. Configure page parameters.

2. Select or create a record source for the report.

3. Determine the levels of grouping and sorting.

4. Place record source fields in the report.

5. Add calculated and free controls.

6. Configure the properties of control elements and report sections.

7. Format the report for a professional look.

3.1. Setting report page options

The main purpose of the report is to print information from the database in a formatted form (close to the form of a paper document). Therefore, before designing a report (placing controls in the report), you need to configure the report page parameters (select a printer, determine the sheet size and its orientation, set the margin sizes, etc.).

If there is no physical printer, you must install the printer programmatically using the command operating system Start | Setting | Printers | Installing the Printer.

To configure report page parameters, you need to run the command File | Page settings …. Dialog window Page settings presented in Fig. 10.2.

Page tab (a) Fields tab (b)

Fig. 10.2. Page Setup Dialog Box

Page settings are configured for each report.

If it is possible to output reports to different printers, then in the dialog box Page settings on the tab Page You can select the type of printer you need. Dialog window Selecting a Printer presented in Fig. 10.3.

Fig. 10.3. Select Printer Dialog Box

Other page parameters depend on the type of printer selected (see Fig. 10.2): maximum paper format and size, minimum margin sizes, paper feed type.

The color of the report depends on the type of printer selected and the settings of its properties: output in color or black and white.

3.2. Report structure

The structure of the report in the mode is shown in Fig. 10.4. Any report has the following sections:

– report title(displayed only on the 1st sheet of the report (title page) and may contain the name of the report, company logo, legal address of the enterprise, etc.);

– page header(displayed at the top of each page and may contain headings, printing date, page number, etc.);

– data area(designed to display fields from a query or table - the source of records for the report);

– footer(displayed at the bottom of each page and may contain headings, printing date, page number, etc.);

– report note(displayed only on the last sheet of the report and may contain final calculations, conclusions on the report, etc.).

Of all the sections listed, the required section is the data area.

The report title and report comment sections are displayed/hidden in the window at the same time. The same principle of showing/hiding applies to the header and footer sections (see Fig. 10.4).

When grouping records, the reports window (see Fig. 10.4) can be added for each group, and in the reports, up to 10 levels of grouping and sorting can be defined, independently the following sections:

– group header(displayed at the beginning of each new group and may contain a field by which grouping is carried out);

– group note(displayed at the end of each group and may contain group totals).

You can change the height of the area of ​​any report section using the mouse by dragging the bottom of the section up or down.

The report width is adjusted based on the selected paper size, sheet orientation, and printed margin sizes.

An example of determining the report width for A4 paper is presented in Table 10.1.

Table 10.1

The width of the report should not exceed the calculated size, since the information displayed in the report will be split over several pages.

You can change the width of the report using the mouse by dragging the right border of the report to the left or right.

Just like in the form, you can insert various elements controls: attached, calculated and free. Expressions in calculated report fields are formed according to the same rules as for forms:

= [Price] * [Quantity]

SUM([Cost]

Last Name & " " & Left(First Name; 1) & "."

Using a control element Subform/report You can embed subreports into the main report.

Properties can be specified for each control, section, and report as a whole.

Generated report Order form in mode and mode is shown in Fig. 10.5.

Fig. 10.5. Order Form report in Design mode (a) and Preview mode (b)

Fig. 10.6. Properties window for the SequentialNumber field

To display each group (order form) on a separate page, you need to set the section properties window Note from the "Order Code" group on the tab Layout property value End of page: After the section(Fig. 10.7).

Fig. 10.5. Properties window for the NoteGroups1 section

3.3. Formatting a report

One of the elements of the report window is the toolbar Form/report format, containing buttons and drop-down lists that make it easier to select formatting options (color, font, line thickness, alignment method, etc.).

Formatting also means placing controls in report sections, aligning controls, changing the size of controls, and setting spacing between controls. To perform these operations you can use the mouse or use menu commands Format (Align..., Size …, Vertical spacing... And Horizontal spacing...).

To quickly perform report formatting operations, you can customize the toolbar Form/report format by copying the buttons from the menu onto it Format.

Report is a formatted representation of data that is displayed on screen, in print, or in a file. They allow you to extract the necessary information from the database and present it in a form that is easy to understand, and also provide ample opportunities for summarizing and analyzing data. When printing tables and queries, information is displayed practically in the form in which it is stored. There is often a need to present data in the form of reports that have a traditional look and are easy to read. A detailed report includes all the information from a table or query, but contains headers and is broken into pages with headers and footers.

Report structure in Design mode

Microsoft Access displays data from a query or table in a report, adding text elements to make it easier to read. These elements include:

1. Heading. This section is printed only at the top of the first page of the report. Used to output data, such as report title text, a date, or a statement of document text, that should be printed once at the beginning of the report. To add or remove a report title area, select the Report Title/Note command from the View menu.

2. Header. Used to display data such as column headers, dates, or page numbers printed at the top of each page of the report. To add or remove a header, select Header and Footer from the View menu. Microsoft Access adds a header and footer at the same time. To hide one of the headers and footers, you need to set its Height property to 0.

3. The data area located between the page header and footer. Contains the main text of the report. This section displays the data printed for each of the records in the table or query on which the report is based. To place controls in the data area, use a list of fields and a toolbar. To hide the data area, you need to set the section's Height property to 0.

4. Footer. This section appears at the bottom of every page. Used to display data such as totals, dates, or page numbers printed at the bottom of each report page.

5. Note. Used to output data, such as conclusion text, grand totals, or a caption, that should be printed once at the end of the report. Although the report Note section is at the bottom of the report in Design view, it is printed above the page footer on last page report. To add or remove a report notes area, select the Report Title/Note command from the View menu. Microsoft Access simultaneously adds and removes report title and comment areas

Methods for creating a report

You can create reports in Microsoft Access different ways:

1. Constructor

2. Report Wizard

3. Auto report: to column

4. Auto report: tape

5. Chart Wizard

6. Postal labels

The wizard allows you to create reports by grouping records and is the simplest way to create reports. It puts the selected fields into the report and offers six report styles. After completing the Wizard, the resulting report can be modified in Design mode. By using the Auto Report feature, you can quickly create reports and then make some changes to them.

To create an Auto Report, you must perform the following steps:

1. In the database window, click the Reports tab and then click the Create button. The New Report dialog box appears.

2. Select the Autoreport: column or Autoreport: tape item in the list.

3. In the data source field, click the arrow and select a table or query as the data source.

4. Click on the OK button.

5. The Auto Report Wizard creates an auto report in a column or strip (user's choice), and opens it in Preview mode, which allows you to see how the report will look when printed.

PRACTICAL WORK No. 6

Applied software general purpose:

Data storage and processing in WS Office Access 2003

Topic: CREATING REPORTS IN MS ACCESS DBMS.

Purpose of the lesson. Studying information technology creating reports in the DBMS. Generating reports on database tables..
Tools. PC IBM PC, MS Access program.
TASKS

Exercise 1. Create an auto report. according to the “Suppliers” table.

Operating procedure.

1. Open the program Microsoft DBMS Access and open your created database.
Select a database object – Reports. Enter the menu Reference, study the section “Creating a report”.

2. Create an auto-report (in a column) for the “Suppliers” table.

Brief information. After selecting a record source and a layout (column, ribbon), AutoReport creates a report that uses all fields in the record source and applies the last AutoFormat used.

Select a database object – Reports. Click the button Create, in the window that opens New report select the “Autoreport:tape” report type (Fig. 1).

Select the Cultural Program table as the data source.
Click OK and wait until the Auto Report Creation Wizard finishes.

3. View the report in preview mode. Go to mode Constructor and see what the report looks like in this mode.

Save the report as Suppliers.

Task 2. Create a report on the “Products” table using the Report Creation Wizard.

Brief information. The wizard asks detailed questions about record sources, fields, layout, required formats, and generates a report based on the responses.

Operating procedure.

Select a database object – Reports.
Click the button Create, in the window that opens New report select the type of report creation “Report Wizard”.

Select the “Products” table as a data source, select fields Product description And Price(Fig. 2), set sorting by field Price, layout type - columnar.

Rice. 2.
Preview.
An approximate view of the report is shown in Fig. 3.

Save the report with the name "Products".

Rice. 3.
[U]Task 3. Create a report inConstructor according to the table “Branch of the company”.

In mode Constructor create a report on the table “Branch of the company” with the heading “Staffing table” and fields Last name, first name, rate.

In the report, enter the total and average values, as well as the maximum and minimum values ​​for the field Bid.

Operating procedure.

1. Select a database object – Reports.
Click the button Create, in the window that opens New report select the type of report creation − Constructor.
Select the “Company Branch” table as the data source.

2. Add a report title and note ( View/Title-Note).
To the region Header enter the inscription “Staffing table” using the button Inscription(Ahh) toolbar.

Make the title in Arial font, 16 bold italic (Fig. 4).

3. In the header, label the field names in the default font. Place the field names on one line.

4. In the data area, place the fields accordingly under the names (it is more convenient to take them from the list of fields).

5. In the report note, enter new fields using the button Field(ab).
Specify field names Total:, Average rate, Maximum rate and Minimum rate.
Enter the calculation formulas as shown in Fig. 4.

to calculate the field Total enter = Sum([Bid])

to calculate the field Average rate enter = Avg([Bid])

to calculate the field Maximum bet enter = Max([Rate])

to calculate the field Minimum bid enter = Min([Bid])

Rice. 4.
Look at the finished report in the mode Preview. Save the report under the name "Staffing Schedule".
Task 4. Create mailing labels according to the “Company Employees” table.

Operating procedure.
1. Select a database object – Reports.
Click the button Create, in the window that opens New report select the report type - “Mail labels”.
Select the “Company Employees” table as the data source.

Topic 2.3. Presentation software and office programming basics

Topic 2.4. Database management systems and expert systems

2.4.11. Training database with main button form "Training_students" - Download

DBMS and expert systems

2.4. Database management systems and expert systems

2.4.6. Creating a report as a database object

Report is a formatted representation of data that is displayed on screen, in print, or in a file.

They allow you to extract the necessary information from the database and present it in a form that is easy to understand, and also provide ample opportunities for summarizing and analyzing data.

When printing tables and queries, information is displayed practically in the form in which it is stored. There is often a need to present data in the form of reports that have a traditional look and are easy to read. A detailed report includes all the information from a table or query, but contains headers and is broken into pages with headers and footers.

2.4.6.1. Report structure in Design mode

Microsoft Access displays data from a query or table in a report, adding text elements to make it easier to read.

These elements include:

1. Title. This section is printed only at the top of the first page of the report. Used to output data, such as report title text, a date, or a statement of document text, that should be printed once at the beginning of the report. To add or remove a report title area, select the Report Title/Note command from the View menu.
2. Page header. Used to display data such as column headings, dates, or page numbers printed at the top of each report page. To add or remove a header, select Header and Footer from the View menu. Microsoft Access adds a header and footer at the same time. To hide one of the headers and footers, you need to set its Height property to 0.
3. The data area located between the header and footer of a page. Contains the main text of the report. This section displays the data printed for each of the records in the table or query on which the report is based. To place controls in the data area, use a list of fields and a toolbar. To hide the data area, you need to set the section's Height property to 0.
4. Footer. This section appears at the bottom of every page. Used to display data such as totals, dates, or page numbers printed at the bottom of each report page.
5. Note. Used to output data, such as conclusion text, grand totals, or a caption, that should be printed once at the end of the report. Although the report Note section is at the bottom of the report in Design view, it is printed above the page footer on the last page of the report. To add or remove a report notes area, select the Report Title/Report Notes command from the View menu. Microsoft Access simultaneously adds and removes report title and comment areas.

2.4.6.2. Methods for creating a report

You can create reports in Microsoft Access in a variety of ways:

1. Constructor.
2. Report Wizard.
3. Auto report: to column.
4. Auto report: tape.
5. Chart Wizard.
6. Postal labels.

Rice. 1.

The wizard allows you to create reports with grouping of records and represents the simplest way creating reports. It places the selected fields in the report and offers six report styles. After completing the Wizard, the resulting report can be modified in Design mode. Using the Auto Report feature, you can quickly create reports and then make some changes to them.

To create an Auto Report you must perform the following steps:

1. In the database window, click the Reports tab and then click the Create button. The New Report dialog box appears.
2. Select the Autoreport: column or Autoreport: tape item in the list.
3. In the data source field, click the arrow and select Table or Query as the data source.
4. Click on the OK button.
5. The Auto Report Wizard creates an auto report in a column or strip (user's choice) and opens it in Preview mode, which allows you to see what the report will look like when printed.
6. On the File menu, click on the Save command. In the Save window, in the Report name field, specify the name of the report and click the OK button.

Changing the report display scale

To change the display scale, use the pointer - a magnifying glass. To see the entire page, you must click anywhere on the report. The report page will be displayed on a reduced scale.

Click on the report again to return to a larger view. In the enlarged report view, the point you clicked on will be in the center of the screen. To scroll through report pages, use the navigation buttons at the bottom of the window.

Print a report

To print a report, do the following:

1. On the File menu, click on the Print command.
2. In the Print area, click the Pages option.
3. To print only the first page of the report, enter 1 in the From field and 1 in the To field.
4. Click on the OK button.

Before printing a report, it is advisable to view it in Preview mode, to access which you need to select Preview from the View menu.

If when printing at the end of the report appears empty page, make sure that the Height for report notes is set to 0.

If the intervening report pages are blank when you print, make sure that the sum of the form or report width and the left and right margins does not exceed the paper width specified in the Page Setup dialog box (File menu).

When designing report layouts, use the following formula: report width + left margin + right margin<= ширина бумаги.

In order to adjust the size of the report, you must use the following techniques:

• change the report width value;
• Reduce margin width or change page orientation.

2.4.6.3. Create a report

1. Launch Microsoft Access. Open the database (for example, the educational database “Dean’s Office”).

2. Create an AutoReport: Tape, using a table as a data source (for example, Students). The report opens in Preview mode, which allows you to see what the report will look like when printed

Rice. 2.

3. Switch to Design mode and edit and format the report. To switch from Preview mode to Design mode, you must click Close on the Access window toolbar. The report will appear on the screen in Design mode.

Rice. 3.

Editing a report

To edit a report, you must perform the following steps:

1. Remove the Student Code fields in the header and data area.
2. Move all fields in the header and data area to the left.
3. Change the text in the page title:
   • in the Report Title section, highlight the inscription Students;
   • place the mouse pointer to the right of the word Students so that the pointer takes the form of a vertical bar (input cursor), and click in this position;
   • enter NTU “KhPI” and press Enter.
4. Move the Caption. In the Footer, select the =Now() field and drag it to the Report Header under the name Students. The date will appear below the title.
5. On the Report Designer toolbar, click the Preview button to preview the report

Formatting a report

Report formatting algorithm:

1. Select the heading Students of NTU “KhPI”.
2. Change the typeface, font style and color, as well as the background fill color.
3. On the Report Designer toolbar, click the Preview button to preview the report.

Rice. 4.

Changing the style

To change the style, do the following:

1. On the Report Designer toolbar, click the AutoFormat button to open the AutoFormat dialog box.
2. In the Report - AutoFormat Object Styles list, click Strict and then click OK. The report will be formatted in the Strict style.
3. Switches to Preview mode. The report will be displayed in the style you selected. From now on, all reports created using the AutoReport function will have the Strict style until you specify a different style in the AutoFormat window.
4. Save and close the report.