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 done very simply, in 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.

In order to fix general form 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.

In this article we will talk about calculated fields in Access queries. A query, like a table, can perform calculations on numeric, string, or date values ​​for each record using data from one or more fields. The result of the calculation forms a new calculated field in the query table. Unlike calculated table fields, calculated fields in source database tables do not create new fields. Each time the query is executed, calculations are made based on the current field values.

Calculated field expressions can use constants and functions in addition to field names. As a result of processing an expression, only one value can be obtained.

Task 1. The PRODUCT table has the PRICE and RATE_VAT fields. Calculate the price including VAT and compare it with the price obtained in the calculated field of the Price including VAT table.

1. Create a select query in design mode for the PRODUCT table. Drag the fields NAME_TOV, PRICE, RATE_VAT and Price with VAT into the request form (Fig. 4.6).
2. To calculate the price including VAT, create a calculated field by writing the expression [PRICE]+[PRICE]*[RATE_VAT] in the empty cell of the Field row.
3. To select records with a value greater than 5000 in the calculated field, enter > 5000 in the Criteria line
4. After entering an expression, the system by default generates the name of the calculated field Expression 1, which becomes the column heading in the table with the results of the query. This name will be inserted before the expression [PRICE]+[PRICE]*[VAT_RATE]. For each new calculated field in the query, the expression number increases by one. The name of the calculated field is separated from the expression by a colon. To change the name, place the mouse cursor in the calculated field of the request form and click the right mouse button. From the context-sensitive menu, select Properties(Properties) field and in the Caption line enter a new field name - Price including VAT1. Now in the table with the results of the query, this name will be displayed in the header of the calculated column. The field name can also be corrected directly in the request form.
5. To display the result of the query, click on the button Execute(Run) in the group results(Results). The calculated field of the table and the query have the same values.
6. Change the price of the product in one of the request records. The values ​​in both calculated fields will be instantly recalculated.
7. To form a complex expression in a calculated field or selection condition, it is advisable to use the expression builder. The builder allows you to select the field names needed in the expression from tables, queries, operation signs, and functions. Remove the expression in the calculated field and use the builder to generate it.
8. Call the Expression Builder by clicking the button Builder(Builder) in the group Query setup(Query Setup) ribbon Design, or by selecting Build(Build) in the context-sensitive menu. The mouse cursor must be previously positioned in the expression input cell.
9. On the left side of the window Expression Builder(Expression Builder) (Fig. 4.7) select the PRODUCT table on which the query is based. A list of its fields will be displayed on the right. Sequentially select the required fields and operators by double-clicking them into the expression. The expression will be formed at the top of the window. Please note that the builder indicated the name of the table to which it belongs before the field name and separated it from the field name with an exclamation mark.
10. Complete the process of constructing an expression in a calculated field by clicking the OK button.
11. Save the request under the name ― Price with VAT and close it.
12. Execute the saved file by selecting it in the navigation area and selecting Open from the context menu.

Task 2. You can use built-in functions in calculated fields and filter conditions. Access has over 150 functions defined.
Let it be necessary to select all invoices for which shipment was made in a given month. In the INVOICE, the shipment date is stored in the DATE_OTG field with the Date/Time data type.

1. Create a select query in design mode for the INVOICE table. Drag the fields NOM_NAKL and CODE_SK into the form (Fig. 4.8).
2. Create a calculated field in an empty row cell Field(Field) by writing one of the expressions there: Format([INVOICE]![DISPOSITION_DATE];"mmmm") - this function will return the full name of the month
   or Format([INBOOK]![DATE_DATE];"mm") - this function will return the month number.
3. To select invoices issued in a given month, in the calculated field in the Selection Condition (Criteria) line, enter the name of the month, for example March (Fig. 4.8), or the month number, for example 3 in accordance with the parameter in the Format function.
4. Complete your request by clicking the button Execute(Run) in the group results(Results) on the ribbon tab Working with requests | Constructor(Query Tools | Design).
5. Write the function Month(INVOICE!DATE_OTG) in the calculated field and make sure that this function returns the month number extracted from the date.
6. To select all rows related to the second quarter, in the Criteria row, enter the operator Between 4 And 6, which determines whether the value of the expression falls within the specified interval.
7. Write the expression MonthName(Month(INVOICE!DATE_OTG)) in the calculated field and make sure that the MonthName function converts the month number to its full name.

To reinforce this, watch the video tutorial.

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 these elements to your report.

In this article

Types of aggregates that can be added to a report

The following table shows the types aggregate functions in Access that you can add to your report.

Calculation	Description	Function
	Sums the elements in a column.
	Determines the average of all elements in a column.
	Counts the number of elements in a column.
Maximum value	Returns the element that has the largest (numeric or alphabetical) value in the column.
Minimum value	Returns the element that has the smallest (numeric or alphabetical) value in the column.
Standard deviation	Shows how much the values ​​in a column deviate from the average.
Dispersion	Calculates the variance for all values ​​in a column.

Add a sum or other aggregate in layout view

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

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.

For more information about creating grouping levels in reports, see Create a grouping or summary 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 footnote 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 =[Order Amount].

Practical work No. 16

Creation Access queries in design mode. Sample requests. Calculated fields in queries.

Creating Access reports. Report Wizard. Working with a report in design mode.

1. Open the Student database created in practical work 15.

2. Enter query designer mode: on the Create tab, in the Queries group, select the Query Builder button. In the constructor, queries are created manually. The data source for a query can be one or more tables or queries. Add a table Students as a data source. Examine the request form window and tools. Data sources are displayed at the top of the request form. Bottom part The form is intended:

· to set the fields displayed in the request (Field line and Table Name line);

· to set the method of ordering data in the request (Sorting line);

· to set selection conditions (Selection condition lines);

· to set the method of grouping data in a request;

· to set calculated expressions in query fields.

3. Include all table fields in your query Students. Enable sorting by last name and run the execution request (button in the toolbar).

Give a name when saving Request Layout. The query will display all the data in the source table. Review your request.

4. Sample requests. Create selection queries. The construction of each new request is performed in the constructor (as in point 3). The layout can also be copied many times, saving each request with a name that reflects the contents of the request. Set the Selection Conditions listed below one by one, view the results of the constructed queries, and save each of them under the appropriate names. So, choose:

· Students whose last names begin with the first letters of the alphabet (Like "[A-I]*") (name First);

· Students who study in the same group (name Group);

· Students, except those who study in a specific group (for example, Not I-105) (name Besides the group).

5. Requests to select from logical operations . Create selection queries by placing conditions on exam grade fields. The data source for queries will be tables Students And Session. Save with specified names. So, choose:

· students with only excellent marks (name Excellent students).

· students with good and excellent marks (4 Or 5) (name Four and five).

· students with at least one C grade (name C students).

6. Select queries with a parameter. If instead of a selection condition in a line, you specify a text prompt enclosed in square brackets, for example [Enter last name], then when you run the query, you can specify the parameter. A parameter is any value by which all records with the specified field value will be searched (in the example, this is the last name). In new queries, select by parameter values:

· By the student's last name (first name Surname).

· By student code (Name Code).

7. Note that parameter searches use an exact match to the parameter value.

8. Calculated fields in a query. In a new request:

· A calculated field called GPA to calculate each student's GPA:

Request name – Average score.

· A calculated field called Total Points to calculate the total points of each student. Request name – Sum of points.

9. Group operations in queries. Grouping is activated by the button on the “ tab Working with requests» - « Constructor» (Group Operations). In the “Group operations” line of the form, you can specify the fields by which grouping is performed, and functions that are called group and are used to summarize a group of data. Check out the list of group functions.

· Find the average grades in subjects for each group. The request must include the fields Group and Exam1, Exam2, Exam3. The last name field is not needed in this request! Use grouping in the Group Operation field and the Avg() group function in all rating fields. Save with a name Group averages.

· Find the average grades in subjects for the entire student population. In this query, include only rating fields with Avg() grouping on all rating fields. Save with a name Subject averages.

10. Reports are designed to display data from tables and database queries in a “paper” version. They differ from forms in that they make it easy to group data and summarize results.

11. Creating reports using wizards. On the Create tab in the group " Reports» select Report Wizard. Select table as data source Students. Select the fields Group, Last Name, First Name, Address, Phone in that order to include in the request. Without adding grouping levels, sort fields in ascending order of group number, then in ascending order of last name, then first name. Select Tabular layout, any style, set a name Students.

12. The report opens in viewing mode. Pay attention to the contents of the preview tab (it's called "Preview").

13. Enter report design mode and familiarize yourself with the contents of the report form. Find the headings, notes, footers areas. Notice that the Report Designer tool tabs have appeared. Creating a report is similar to creating forms. The header area contains the general title of the report, the header contains the field headings; they will be repeated on each page of the report. In the footer area - the current date and page numbers of the report, these will also be repeated on each page. Look at what type these elements are. For headings the element is used Inscription, to display the data element Field. Element Line separates the header area.

14. Grouping data. To combine records into groups, use the command Grouping on the " Report Designer Tools - Designer" In the report Students Student records should be grouped by group numbers. Use grouping to add a Group Header area to your report for group numbers, and set the sort order to ascending. Move the Group header inscription to the Group Header area, and the Group field from the data area. Separate the information about the different groups with a thin line at the top.

15. View the result in preview mode.

16. Create reports using wizards. Grouping data. Use the Report Wizard to build a report for tables Students And Session. Select to include the fields Group, Last Name, First Name, and grades for the three exams in the query. Add grouping level(by group), sort by ascending last name, then first name. Select a layout Stepped, any style, set the name Session.