Access reports calculation of totals. Summarization in reports. Types of aggregates that can be added to a report

IN Access calculations are performed in queries and reports. All calculations using formulas are carried out only in queries using the line Group operation. The most commonly used functions of this line are:

· Expression. Perform calculations in a field using a formula.

· Summation (Sum). Sums all values ​​of the selected field.

· Average value (Avg). Calculates the average of all numbers in the selected field.

· Minimum (Min). Determining the smallest number in a field.

· Maximum(Max). Determining the largest number in a field.

Fig. 19. Open a window with the line Group operations

To perform calculations using the data placed in the request fields, first of all, check which columns of which tables contain the numbers necessary for the calculations. Include all these columns (fields) in the request. Then follows by left-clicking in the toolbar on the button Constructor(Fig. 19), add the line to the query window group operation, which is hidden by default. The window shown in Figure 20 will open. In some versions Access on the menu View there is a line group operation, which can be used for this purpose.

Fig.20. Line Group operation

Line Group operation should appear automatically. In some versions Access This line does not appear automatically. In this case, you need to click the button with the ∑ symbol in the toolbar (Group operation ), which is highlighted in the figure. To write a formula, select an empty field. In line Group operation All request fields contain the default value Grouping. To enter a formula in the selected field of this line, you must place the value Expression, what can be done like this:

· Click on the line Group operation in the selected field.

· Click on the button with a triangle that appears in the field.

· In the drop-down list, select the line Expression(Fig. 21).

· In line Output on display checkbox must be checked.

Rice. 21. Selecting a group operation "Expression"

The formula is written in the line Field following the same rules as in Excel, but instead of cell addresses, field names enclosed in square brackets are used. The field name in the formula should be entered case-sensitive, manually, do not copy! The formula can include numbers (constants), but percentages must be converted to decimals. For example, if you want to calculate the selling price of a product by increasing the wholesale price by 25%, in the line Field the formula should be written:

=[Price]*1.25

After entering the formula, click outside the finished expression, perhaps in another line. Instead of an equal sign Access will add text to the beginning of the formula Expression1:, which will become the name of the field. This text can be replaced later. If another formula field is added to the query, it will be named Expression2: etc. Open the request and check for a new title field Expression 1. To view the query in table form, select the command from the menu View, in the drop-down list - Table mode. To change the field title, click on the button in the toolbar Constructor(Fig. 19), then in the desired column of the line Field(Fig. 20) delete the previous name and enter a new one.

Generating reports

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

To create a report:

Like tables and queries, reports can be sorted and filtered. Simply 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 don't really need. For example, our report contains a Zip Code field, which is not required in this order list. Luckily, you can delete fields in reports without affecting the table or query where you pulled your data.

To delete a field in a report:

1. Click any cell in the field that you want to delete.
2. Press the "Delete" key.

When you delete a field, be sure to delete its title. Simply select the title and press the Delete key.

Print and save reports in preview mode

Although you can print reports using commands in Backstage view, you can also use 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 they can be displayed outside of Access. This is called file export, and it allows you to view and even modify reports in other formats and programs.

Access offers options for saving the report as Excel file, text file, PDF, XPS file, attachments Email, 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 cumulative sums to make the data more understandable. This article explains how to add such elements to a report.

In this article

Types of aggregates that can be added to a report

IN following table lists the types of aggregate functions in Access that can be added to a report.

Add a sum or other aggregate in layout view

Layout mode provides the most fast way adding amounts, averages, and other aggregates to a report.

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

For more information about creating grouping levels in reports, see Create a Grouped or Pivot Report.

Adding a sum or other aggregate in design mode

The constructor allows you to more accurately customize the placement and appearance total values. In grouped reports, you can put totals and other aggregates in the header or footer 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 calculation. If your report has grouping levels, Access will also add a text box that performs the same calculations in each group note section. If for property Cumulative amount(Running Sum) value set For everything, then the overall total can be repeated in the report footer. Create a field in it and set its property value as Data(Control Source) name of the field in which the cumulative sum is calculated, for example =[OrderAmount].

REPORT IN MS ACCESS.

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 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.

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.

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.

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.

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 the title and comment areas of a 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 labels.

The wizard allows you to create reports with grouping of records and represents the simplest way creating reports. It puts the selected fields in the report and offers six report styles. After completing the Wizard, the resulting report can be finalized in Design mode. Using the Auto Report feature, 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 Create button. The New Report dialog box appears.

Select AutoReport: To Column or AutoReport: To Ribbon from the list.

In the data source field, 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 column or ribbon (user choice) and opens it in Preview mode, which allows you to see what the report will look like when printed.

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 the enlarged view. In the enlarged report mode, the point you clicked 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:

On the File menu, click on the Print command.

In the Print area, click the Pages option.

To print only the first page of the report, enter 1 in the "from" field and 1 in the "to" field.

Click on the OK button.

Before printing a report, it is advisable to view it in the Preview mode, to switch to which, in the View menu, select Preview.

If a blank page appears at the end of the report when you print, make sure that the Height setting for report notes is set to 0. If you print blank report interstitial pages, make sure that the sum of the form or report width plus the left and right margin widths does not exceed the width of the paper 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.

CALCULATIONS IN MS ACCESS.

Very often, when creating a set of records, it is necessary to perform calculations on the data (determine the age of the employee, the cost of the order, the percentage of sales, highlight part of the item code, etc., i.e., display information that is not stored in the database) or perform certain operations for direct processing of selected data.

MS Access provides such capabilities through calculated fields and group operations.

A calculated field is an expression consisting of operators (arithmetic, comparison, logical, concatenation) and operands. The operands can be constants, built-in or user-defined functions and identifiers, for example

Cost: Goods! Price * Quantity * (1-Discount)

Number of Men: Sum (IIf(Gender = "m"; 1; 0))

Full name: Last name &" "& Left (First name; 1) &". "& Left (Middle name; 1) &."

Cost, Number of Men and Full Name are the names of calculated fields and are displayed in the table view in the column header; the colon character acts as a separator between the name of the calculated field and the expression.

If the name of a table or field contains spaces, then its identifier must be enclosed in square brackets in the expression, for example

Cost: Price*[Quantity of goods]

A calculated field is created directly in the QBE form by entering an expression in the Field cell of any free column. The results of calculations displayed in the field are not stored in the base table. The calculations are performed again whenever a query is executed, so the results always represent the current contents of the database.

It is not possible to update calculated results manually.

To build complex expressions, the MS Access DBMS includes a utility called the Expression Builder.

The expression builder can be launched by clicking on the key Build on the toolbar Query Builder or by selecting the menu command in the context menu of the QBE request form field Build....

Rice. 9.

The MS Access DBMS contains more than 100 built-in functions that can be used when creating a calculated field or when setting a selection condition.

Rice. 10.

All built-in functions in the Expression Builder are grouped by functionality. When you select a function in the right Builder window, the lower pane of the Expression Builder window displays the syntax for that function. For more detailed help, click on the button.

When analyzing data, one is often interested not in individual records, but in the total values ​​for groups of data, for example:

Number of transactions with Partners over a certain period of time;

Average sales volume for each month for the previous year.

The final query provides answers to such questions.

To calculate the total values, you must click the button Group operations on the toolbar Query Builder so that the line appears in the QBE form Group operation(after the table name).

By default, for each field entered in the request form, the value is set to Grouping(results are not summed up).

To summarize, it is necessary to replace the installation Grouping to a specific final function. The MS Access DBMS provides 9 functions that ensure the execution of group operations.

Group operation installation available Expression. This setting is applied when in the expression (line Field) several summary functions are used.

String dropdown Group operation installation available Condition. This setting is used when the selection condition is written in the Selection Condition line, but the column (field) data should not participate in the group operation.

To solve more complex statistical problems, the MS Access DBMS provides a special type of query - cross-query.

Cross request is a special type of group query that reflects the results of statistical calculations based on the value of one table field.

To build a cross-query, three fields of the base source are enough. Based on the repeating values ​​of one field, the names of the row headings of the final (pivot) table are formed (Fig. 6.4). Based on the repeating values ​​of another field, the names of the column headings of the final (pivot) table are formed. The results of statistical processing for the third field are displayed in the cells of the pivot table (values ​​area).

The row header area allows multiple fields, while other areas can only accommodate one field at a time.

It is allowed to specify selection conditions in a cross-request. Sorting can only be done by fields located in the row header area.

Access has a wide range of features and tools for working with databases. Users using this program may be given a variety of tasks that they need to be able to solve. In this article we will figure out how to create a report in Access, what it is and what it is needed for. Let's get started. Go!

Reports in Microsoft Access are used to display or print information from a database. Any report can be created based on a table or based on a query. This is very easy to do, just a couple of clicks.

To begin, you must have a ready-made table whose data you will work with. Then select the desired table in the “All Access Objects” window located on the left, go to the “Creation” tab and click the “Report” button in the section of the same name. All is ready. You will see a report generated by the program based on the information contained in the table.

After that, you can go to the Design tab to edit fields, sections, and more. To exit design mode, right-click on the table tab and select Report View. Don't forget to save before exiting.

For more complex cases, use the “Report Wizard”, the corresponding button is located in the same “Creation” tab; this tool is useful if you need to operate with data from several tables at once.

In the window that opens, select from the list those tables and queries from which the fields will be taken (they must also be specified). When finished, click the "Next" button. In the next window you will be asked to select the appropriate type of data presentation. You can then group the selected fields as you wish. Simply select the desired field and click on the arrow button. Next, a window for sorting records will open. Sorting is optional, so you can skip this step. In the next window, select Layout (staggered, block, outline) and Orientation (portrait, landscape). Leave “Adjust margin width to fit on one page” checked. After this, all you have to do is enter the name and click the “Finish” button.

To fix the general appearance and incorrectly displayed data, go to design mode. There you can select individual fields and move them using the arrows on the keyboard or the mouse. You can remove large gaps between columns without going to the “Designer”. Just move the borders with the mouse cursor. If some of your data is displayed in the form of “#” bars, simply increase these fields and the information will be displayed correctly. In design mode, you can change the color of text, background, and headings.

It is possible to create specific cells in which the sum, difference or product will be calculated based on data from tables or queries. To do this, select the desired field and click the “Totals” button located on the toolbar in the “Grouping and Totals” section. In the menu that appears, select “Number of records” or “Number of values” depending on the tasks you are faced with.

Access reports automatically display gray bars, which some users may find unsatisfactory. To remove them, go to design mode, select “Data Area”, right-click and select “Properties” from the list. A window will appear on the right in which you need to find the line “Alternation background color”. Copy "Background 1" from "Background Color" and paste into "Alternate Background Color." Repeat the same with the Group Notes section.

Another way to create is “Report Designer”. By clicking this button, an area will appear in front of you where you need to add fields using the corresponding button in the toolbar.

Now you will know how to make a report in Microsoft Access. This will allow you to work better with databases. Leave your opinion about this article in the comments: whether it was useful, and ask if anything remains unclear.