Summation in reports. Calculated fields in Access queries To create a report

Form calculations are used:

• when creating calculated fields, that is, those controls whose values \u200b\u200bare calculated based on the values \u200b\u200bof other controls or built-in functions;
• to calculate the default value of form controls;
• in expressions that set some properties of controls, for example, a condition on a value, conditions for selecting records in a form, and a number of others.

We have already described the rules for composing expressions in Access (see the section "Using Expressions in Queries", Chapter 4). Therefore, here we will focus primarily on calculated form fields and describe where they are needed and how to create them.

Attention
Formulas for calculations in Access are written using a special syntax that dictates how formulas are written in both Access expressions and VBA procedures. This syntax is somewhat different from that accepted in mathematics, but in many ways it is similar and intuitive. A description of the syntax of computational formulas is given in section. "Expressions in Access" chap. 4.

Creating calculated fields

The simplest calculated field is the field that should display the current date. To create such a field:

1. Create a loose control like Field (Text Box) in the right place on the form.
2. Enter the expression directly into the field \u003d Date ()... The equal sign is required.
3. Open the window Properties (Properties) for this item and set the property Field format (Format) the desired date format like Long date format (Long Date).
4. Set the length of the field to fit the required number of characters.
5. Change the label name of this text box. The created field will look as shown in Fig. 9.47.

Figure: 9.47... Calculated field

Report generation

Reports give you the ability to present the components of your database in an easy-to-read format for printing. Access 2010 lets you create reports from tables and queries.

To create a report:

Like tables and queries, reports can be sorted and filtered. Just right-click the field you want to sort or filter, then select the sort or filter function you want.

Removing fields

You may find that your report contains some fields that you really don't need. For example, our report contains a Zip Code field that is not required in this order list. Fortunately, you can delete fields in reports without affecting the table or query where you pulled your data.

To remove a field from a report:

1. Click any cell in the field you want to remove.
2. Press the Delete key.

When you remove a field, be sure to remove its header. Just select a title and press the Delete key.

Printing and Saving Reports in Preview Mode

While you can print reports using commands in Backstage view, you can also use Print Preview. The preview shows how your report will appear on the printed page. It also allows you to change the way the report is displayed, print it, and even save it as a different file type.

To print a report:

Saving reports

You can save reports in other formats so that they can be displayed outside of Access. This is called exporting a file, and it allows you to view and even modify reports in other formats and programs.

Access offers options to save your report as an Excel file, text file, PDF, XPS file, email attachment, text file, or HTML document. Experiment with different export options to find the one that best suits your needs.

In any Access report that contains numbers, you can use totals, averages, percentages, or stacked amounts to make the data easier to understand. This article explains how to add these items to your report.

In this article

Types of aggregates that can be added to the report

The following table lists the types of aggregate functions in Access that you can add to a report.

Add a sum or other aggregate in layout mode

Layout mode provides the fastest way to add totals, averages, and other aggregates to a report.

Data (Control Source) expression that performs the desired computation. If the report has grouping levels, Access also adds a text box that performs the same calculations on each section of the group note.

For more information on creating grouping levels in reports, see the article Create a grouped report or pivot report.

Add a sum or other aggregate in design mode

The designer allows you to fine tune the placement and appearance of totals. In grouped reports, you can place totals and other aggregates in the header or note of each group. Report-level aggregates can be placed in the header or footer of a report.

Access adds a text box to the report header and sets its property value Data (Control Source) expression that performs the desired computation. If the report has grouping levels, Access also adds a text box that performs the same calculations on each section of the group note. If for property Accumulated amount (Running Sum) set to For everything, then the grand total can be repeated in the report header. Create a field in it and set its property as the value Data (Control Source) the name of the field in which the accumulated amount is calculated, for example \u003d [Order Amount].

Report Is a formatted representation of data that is displayed on a screen, print, or 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 generalizing and analyzing data. When tables and queries are printed, information is displayed practically in the form in which it is stored. It is often necessary to present data in the form of reports that are traditional and easy to read. A detailed report includes all the information from a table or query, but contains headers and is paginated 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 it that make it easier to read. These elements include:

Title. This section prints only at the top of the first page of the report. It is used to display data, such as the text of the report title, the date or the ascertaining part of the document text, which should be printed once at the beginning of the report. To add or remove a title area for a report, select the Report Title / Note command from the View menu.

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

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

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 page of the report.

Note. Used to display data such as conclusion text, grand totals, or signature, which should be printed once at the end of the report. Although the Note section of the report 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 note area, select the Report Title / Note command from the View menu. Microsoft Access simultaneously adds and removes the title and notes regions of the report

Ways to create a report

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

Constructor

Report wizard

Auto report: to column

Auto report: tape

Chart Wizard

Postal stickers

The wizard allows you to create reports with grouping of records and is the simplest way to create reports. It places the selected fields on the report and offers six styles of presentation. After completing the wizard, the resulting report can be finalized in the Design view. Using the Auto Report function, you can quickly create reports and then make some changes to them.

To create an Autoreport, do the following:

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

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

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

Click on the OK button.

The Autoreport Wizard creates an autoreport in a column or ribbon (user-selected), and opens it in Preview mode, which allows you to see how the report will look in printed form.

When developing report layouts, be guided by the following formula: report width + left margin + right margin<= ширина бумаги. In order to adjust the size of the report, you need to use the following techniques:

change the value of the report width;

reduce margins or change page orientation.

Generating a report

1. Start the Microsoft Access program. 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 how the report will look in printed form

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 toolbar of the Access application window. The report will appear on the screen in Design mode.

Style change:

To change the style, do the following:

On the Report Designer toolbar, click the AutoFormat button to open the AutoFormat dialog box.

In the Styles list for the "report - autoformat" object, click the Strict item and then click the OK button. The report will be formatted in the Strict style.

Switch to Preview mode. The report will be displayed in the style of your choice. From now on, all reports created using the Autoreport function will have the Strict style, unless you specify a different style in the AutoFormat window.