appear on the form Form title And Form Note:

In the header of our form there is an inscription with the name of the form, and in the note the name of the student who will do the work. To do this, select the icon on the control panel Inscription,

place the cursor over the form area and click left button mice. Then enter the text:

Next, let's change the background to Form title And Note form. To do this in the window Property forms you need to select a section Form Header (NoteForms). After tab All and in the field Background color select the desired color:

In the same way we change the background for Data areas:

In the appeared List of fields select the required fields and drag them onto the form and place them in the desired place:

Select the “appearance” of our button and click Further:

Set the name of the button and click Ready:

Create a button in a similar way Close form, just change the action for the button:

Then we set the shape of the button and place it in the right place on the form:

Open the form in "Form mode" the result of our work on the screen:

Table “Payment Purposes” before adding data:

Table “Payment Purposes” after adding data:

Subsequent forms are created in a similar way!

1.3 Create a ribbon shape for input and data output about payments of many students (the data source for the form is a request containing fields Surname, Name, Surname from the "Students" table and field Payment Amount, Payment Date, Payment Purpose from the "Payments" table).

Solution to Problem 1.3:

We create a request in the mode Constructor containing fields Surname, Name, Surname from the "Students" table and field Payment Amount, Payment Date, Payment Purpose from the "Payments" table:

To create a ribbon shape, use Form Wizard:

In the window that appears Creating forms select the data we need and click Further:

Select the type of data presentation and click Further:

Set the name of the form and click Ready:

Tape form in mode Constructor:

Ribbon form in “Form Mode”:

1.4 Working with forms in which subordinate forms are embedded (development of complex forms)

Create a complex form to display payment data for the selected student. Sequencing:

1) Create a simple “Student Payments” form with the following fields: Student Code, Surname, Name, Surname from the "Students" table as the main form.

2) Create a tabular form “SubordinateFormStudent Payments” with the fields: Student Code, Payment amount, Date of payment, PurposePayment from the Payments table as a subform.

3) Embed the second form into the first and link them by field Student Code.

Solution "Problem 1.4":

Create a simple form in the mode Constructor. IN Form title And Form Note We enter the necessary information. Change the color and font of the text. Changing the background for the form. Next, add the fields to the form Student Code, Surname, Name, Surname from the “Students” table (this is the main form):

and place the subform on the main one. In the window that appears, select our subform:

Form in mode Constructor:

Complex shape in “Form Mode”:

1.5 Controltask (option 13)

Create complex forms containing information specified in a table (if necessary, use queries rather than tables as a data source for subforms):

Solution of the control task:

We create a table form and enter the required fields (from the request) on the form (subform):

Subform in mode Constructor:

Complex form in mode Constructor:

Form in “Form Mode”:

Conclusions: learned the basics of working with screen forms and reports in the MS Access DBMS. Learned how to create simple shapes, ribbon forms, subforms

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

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

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. 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 the mode Preview, which allows you to see what the report will look like when printed.

information about the company, and on Footer in the object field Field the date the form was printed. To place a date, select the Data/… command in the list of properties of this object and, opening the window Expression Builder(see Fig. 19), place the Date() function from the list of built-in functions in it. Edit the properties of the margins placed on headers and footers and the sizes of header and footer areas.

View the form in Preview and select the size of all areas of the form such that there is only one copy of it on one page. Make other changes to the form interface that you think are appropriate.

Control questions

1. Purpose of forms.

2. For what objects are forms built?

3. Types of forms.

4. Subordinate forms.

5. Controls.

6. Attached object frames.

7. Form creation modes.

8. Procedure for creating a form based on Wizards Autoform.

9. Procedure for creating a form based on Masters of Forms.

10.Procedure for creating a form by the wizard Diagram.

11.Editing forms with diagrams.

12.Purpose of the Form Builder.

13.Functional areas of the form in Design mode.

14.Editing the location of controls on the form.

15.Placement of drawings on the form.

16. Placement of controls in the Header and in Note form.

17.Changing the properties of a control on a form.

18.Using header and footer areas on a form.

6. REPORTS IN THE ACCESS DBMS

Reports in Access are used to present data in an easy-to-understand and expressive way and are intended primarily for printing rather than displaying on screen. Typically, reports are the final products of working with a database. As with creating forms, creating a report uses data from tables and queries (sometimes also forms, but this case no longer applies to simple reports).

When designing reports, much of the same technology is used as when designing forms, however, unlike forms, the user is not allowed to change the data in the reports. When re-

When editing reports in the Design mode (see Fig. 42), the same areas of the interface are used as when editing the form - editing

Header and Note areas, Header and Footer areas, Data area.

Rice. 38. Window for selecting report creation modes

The following modes are used to create reports: Design,

Report Wizard, Chart Wizard, Postal Labels, and Auto Reports: Column and Ribbon (see Figure 38).

As in the case of forms, a fully finished report created using AutoReports is obtained immediately after specifying the source table or query. Master Postal labels although it works in several steps, it does not require much explanation. Based on the use of the Report Wizard, most reports in Access are created, which can have a fairly complex structure. Below we will discuss in detail how to work with this Master.

It should be noted, however, that all of the reports listed above are called annexed(to the data source), since all of them, like forms, use tables or queries for their construction. The most complex structures are those of reports created “from scratch” by the user himself in Design mode. Reports that can contain subordinate reports, use special sortings not provided by Wizards, and also use various Access functions, etc., are referred to as so-called. free reports. However, creating complex reports using the Designer requires additional knowledge and is not covered here.

Based on the Report Wizard in Access, a report can be prepared in which data from a source table or query is combined according to certain criteria. These reports are the most commonly created because they allow you to calculate totals for groups of data and provide information in an easy-to-use format. For

To create such summary reports, use the Grouping... command (see Fig. 39) and the corresponding calculations for grouped data in the Summary dialog box (see Fig. 41). In this case, you can specify up to four fields by which the data will be grouped.

Using the Report Wizard, create a report for the “Contracts” table in which the data will be grouped by the “Product” field. Within the group, sort the data by date (see Figure 39). At the same time, using the Grouping... command, set your grouping intervals for the data in the grouped fields (see Fig. 40).

Rice. 39. Dialog box for creating a report with grouping

If the report contains numeric fields, then for each group interval numerical values The wizard automatically calculates the sum of the values ​​contained in these fields. However, using the commands of the Summary dialog box (see Fig. 41), you can calculate the average (Avg), minimum and maximum (respectively Min, Max) values ​​of grouped fields, as well as percentages.

Rice. 40. Selecting grouping intervals for grouped fields

Rice. 41. Window for specifying calculations when creating a report with totals

You can always edit a created report in Design mode (see Figure 42). To define or change the properties of individual report areas, click on the name bar of the corresponding area and select the Properties command.

Note that in reports, some of the controls contain Expand and Compress properties. If you set the value for them to True , then when printing Access will automatically adjust the size of the corresponding element, so that long text will not be cut off by the element's window, and short text will not shrink the element's window so that it does not take up unnecessary space when printed.

To conclude our work with the database, we will demonstrate a simple and useful feature that the Access DBMS provides due to its compatibility with other software applications package Microsoft Office(MS), – we will prepare the distribution of serial letters in the test Word editor using information stored in database tables.

Prepare in text editor Word template of a serial letter thanking customers for their active participation in concluding contracts for the supply and ordering of goods in your warehouse.

Create a new “Thanks” request, in which you include the names of the organizations that took the most active part in concluding contracts with your company, defining a sample condition based on the amount of concluded contracts for the supply of goods (the sample should include three best companies). Based on the generated query, start creating a new report using the Merge with MS Word wizard, for which you need to sequentially execute the commands Office Links/MS Word Links.

After this, a dialog box will open Compound Document Wizards. You can work with a previously prepared MS Word document, or create a new one by selecting the appropriate command in the dialog box. When you create a new document, Access launches Word, establishes a DDE connection between Word and Access, and opens a new text document in which you can compose a new letter.

Rice. 42. Constructor window for editing a report

Prepare a serial letter with the content shown in Fig. 43. The field names highlighted in quotation marks are entered into the sequentially corresponding place in the text of the letter from the list, which must be opened by clicking on the icon. Add a merge field(see Fig. 43). The list should contain all the fields of the created “Thanks” request, including the fields we need “Customers” and “Products”. After clicking on the Fields/Data icon and using the buttons to scroll through field values ​​from the database query table, you can preview letters prepared for printing.

Rice. 43. Connecting Word and Access

When printing letters, they are replaced with data from the corresponding database table. The number of letters is determined by the number of records in the table, from which values ​​for control fields in the letter are inserted one by one for each record.

After saving and closing the text document, return to Access. Thanks to DDE communication when changing data in a table Access data in the serial letter will be updated automatically. Note that when you open a file with a serial letter in the Word editor, Access with the required database is automatically loaded.

Control questions

1. Purpose of reports.

2. Report structure.

3. Report generation modes.

4. Report Wizard.

5. Calculations in reports.

6. Report designer.

7. Creating joint Access and Word documents.

LITERATURE

1. Computer Science/Ed. S.V.Simonovich. St. Petersburg: Peter, 1999.

2. Veiskas D. Effective work with Microsoft Access 2.0, 1996.

3. Dwayne G. Access 97. User Encyclopedia. Kyiv: DiaSoft,

4. Jennings R. Using Microsoft Access 2000. Moscow: Williams, 2000.

5. Novikov F., Yatsenko A. Microsoft Office 2000 in general. SPb.: BNV,

6. Zmitrovich A.I. and etc. Information Technology in economics. Minsk: Vedas, 1998.

Introduction
	Relational Database Design
	1.1. Database types
	1.2. Normalization of relations in the RBD
	1.3. Types of connections and keys in the RDB
	Control questions
	Creating databases and tables in Access DBMS
	2.1. Creating a database in Access DBMS
	2.2. Creating tables in Design mode
	Organizing relationships between tables and filling tables
	Control questions
	Queries in the Access DBMS
	4.2.Creating a query in Design mode
	4.4. Final queries
	4.6. Cross requests
	Control questions
	Forms in Access DBMS
	5.1. Creating forms based on the Form Wizard
	5.2. Building charts
	5.3. Form Builder
	Reports in Access DBMS
	Control questions
Literature

WORKING WITH DATABASES IN ACCESS DBMS

Compiled by Gurin Nikolay Ivanovich

Editor M.F. Murashko. Proofreader T.E. Bekish Signed for publication on April 10, 2002. Format 60x84 1/16.

Offset printing. Conditional oven l. 4.5. Conditional cr.-ott. 4.5. Academic ed. l. 3.9. Circulation 200 copies. Order.

Educational institution "Belarusian State Technological University".

License LV No. 276 dated 04/15/98. 220050. Minsk, Sverdlova, 13a. Printed on the rotaprint of the Belarusian State Technological University. 220050. Minsk, Sverdlova, 13.

Databases: Creating reports in DBMS MS Access 2007

The workshop is aimed at developing the student’s competence to manipulate information resources using DBMS tools and present the content of applied and information processes.

Basic Concepts

Database(DB) is a named collection of interrelated data that reflects the state of objects and their relationships in a certain subject area, and is used to meet the information needs of users. Databases in the MS Access DBMS belong to the type of relational databases. In such databases, information related to various issues is stored in separate tables, between which relationships are established.

Database– a file consisting of tables and other information objects.

Table– an information object consisting of records. A table is a fundamental data structure, the main information object in a DBMS.

Record is a complete set of data about a specific real-world object: client, book, event, etc. The record is represented as a string. The structure of all table records is the same. The number of records in the table is variable.

Field– this is a set of data of the same type in a table, for example, the names of all employees. A column is used to represent a field. A field has a name and values.

Report– an information object that stores table or query data in the form of a formatted document ready for printing.

Composite report– a report that displays related data using a subreport or subtable.

Control in a report– an object in a report that improves the user interface when working with data, for example, a button, switch, tab, etc.

Attached control– a control whose data source is a table or query field. The attached control is used to display the values ​​of database fields.

Free control– a control that does not have a data source (for example, a field or expression). Free controls are used to display information, lines, rectangles, and pictures. An example of a free element is a caption that displays a title in a report.

The workshop contains step by step instructions creating various types of reports, performing operations simple search data, filtering and sorting data, changing the structure of a report, inserting additional data controls into reports, and also includes DIY tasks.

Beginning of work

To get an idea of ​​the database reports, it is suggested to open the Northwind 2007 training database in the MS Access DBMS.

Opening a Database

To open the Northwind 2007 database you need to:

1. Launch Microsoft Access by clicking the Start button.

2. Select All Programs, then Microsoft Office, then Microsoft Access 2007. The Microsoft Access 2007 window will open.

If the “Borey 2007” database is opened on your computer not for the first time, then on the screen you will see the “Borey 2007” database splash screen window;

If this is the first time, then MS Access will download it first. To start downloading, you need to click the “Download” command button and follow the instructions.

The Northwind 2007 database window opens.

The database is closed in the usual way for MS Office: in the system menu containing commands for working with the file, select “Close database”.

Exercise 1. Launch MS Access 2007. Open the Northwind 2007 database. Close it. Open the Northwind 2007 database again.

Features of the MS Access 2007 interface

In MS Access 2007 different from previous versions interface. The menu and toolbars are replaced by a menu ribbon with different tabs: Home, Creation, External Data, Working with the Database.

Tabs contain tools or groups of tools (the group is marked with a triangle icon) that you can join.

Rice. 1. MS Access 2007 menu ribbon with tabs

Also, depending on what database object you are working with, contextual tools automatically appear, for example, when working with forms, contextual tools for the form appear.

Below the ribbon are the Transition Area on the left, and the Editing Window on the right, which displays the object being edited.

Rice. 2. “Borey 2007” database window

The Navigation Pane contains all Access objects (tables, forms, queries, reports, etc.). You can select the required object from the All Access Objects list. When you double-click on an object's name in the Navigation Pane, that object will appear in its own tab in the Edit Window.

Database Objects

The database contains data in the following types of objects: table, query, form, report, macro and module. The fundamental structure for storing data is a table. All other types of objects are derived from the table:

The query stores table data that satisfies some criterion specified by the user when composing a command to retrieve data.

The form stores table or query data as a collection of screen cards, each of which displays the contents of one table or query record, which is useful when updating data in tables.

The report stores data from tables or queries in the form text document, suitable for printing.

A macro stores a set of data processing operations that can be launched with one press of the Enter key, which is convenient if you need to frequently perform the same standard operations.

The module stores a data processing program written in a programming language, most often in the language built into MS Access, Access Basic.

The database objects in the Navigation Area are grouped, and you can see the names of the groups. To see the contents of a group, you need to click on the group name. A second click will collapse the group list to its name.

Task 2. Collapse and expand group lists in the Navigation Pane.

Due to the fact that tables are the main structure for storing data, in order to avoid their damage and to ensure ease of working with data, the user works with derived types of objects. In the Northwind 2007 database, the forms “Customers and Orders”, “Inventory and Purchases”, “Suppliers”, “Delivery”, “Reports”, “Employees” are explicitly presented in the Transition Area.

Tables and other types of objects are hidden in the Navigation Pane in the Helper Objects group.

The “Unassigned objects” group stores objects that are additionally created by the user. For example, here you can save the queries you created.

Please note that different types of objects are indicated by different icons:

You can change the representation of objects in the Navigation Pane to the familiar one used in earlier versions of MS Access. To do this, click on the list icon (▼) in the Navigation Pane header to expand the list of object filters and select “All Access objects”.

Task 3. Review the contents of the Auxiliary Objects group. Open different types of objects and view their contents. Close the Auxiliary Objects group. Change the presentation of objects in the Navigation Pane to group objects by type: tables, queries, forms, macros, modules.

You can collapse and expand the transition area by clicking the double arrow button (<<) или (>>) in the upper right corner of the panel.

Task 4. Collapse and expand the Transition Area.

The editing window is currently occupied by the splash screen. The screensaver can be removed by clicking on the button (X) to the right of the “Screenaver” shortcut.

You can display it in the Transition Area from the “Auxiliary Objects” group, then the “Splash Screen” form.

Task 5. Close the Northwind 2007 database splash screen.

All database tables are interconnected. You can see the relationships between tables if you open the data schema by selecting “Data Schema” on the “Working with Databases” menu tab.

You can close the data diagram by clicking the “Close” button on the “Design” menu tab.

Task 6. Open and close the data schema.

Help system MS Access 2007

Like any MS Office application, the MS Access 2007 DBMS has its own help system. In order to open it, you need:

1. On the right side of the menu bar, click on the button (?).

2. In the “Help: Access” window, enter the desired help section at the bottom of the window or enter the search context in the search bar and press Enter.

Task 7. Open the MS Access 2007 Help system. Open the Forms and Reports section. Review the contents of the section. Close the help system.

Opening a report and viewing data

A report is understood as a specially structured presentation of printed data.

The report design mode interface is similar to the design mode for screen forms.

Creating and formatting a report can be done both in Report Wizard mode and in Design mode.

You can insert diagrams and illustrations into the report, and in the report, data can be sorted and grouped by the contents of the fields.

1. Creating a report using the Report Wizard:

Select the Create tab and then Reports, then the Report Wizard.

From the list, select a table or query on the basis of which the report will be created.

After opening the Report Wizard window, define the fields;

Define grouping and sorting fields;

Determine layout and orientation;

Selecting the desired style;

Enter a name for the report; by default, the name of the base table is assigned.

2. Creating a report in design mode.

The report structure consists of several sections:

1. At the very top of the report is the Report Title section. It contains an inscription with the name of the report.

2. Header. Contains labels that correspond to the controls in the Data Area section.

3. Section Data area. Contains controls associated with fields included in the report from one or more tables.

4. At the bottom there is a Footer section. Contains formulas that are designed to display the current print date and report page number.

5. At the very bottom is the Report Note section. This section is usually empty, but you can use it to insert a final formula or other calculations to perform on the data displayed in the Data Area section.

25. Concept of algorithm, properties of algorithms

Algorithm – an exact final system of rules described in a certain language that determines the content and order of actions on certain objects, the strict implementation of which provides a solution to the problem.

Any algorithm does not exist on its own, but is intended for a specific performer (person, robot, computer, programming language, etc.). The set of commands that a given executor can execute is called the executor’s command system. The algorithm is described in the commands of the performer who will implement it.

The algorithm is characterized by the following properties:

Discreteness (discontinuity) is a property of an algorithm that characterizes its structure: each algorithm consists of individual completed actions, they say “Divided into steps.”

Efficiency – a property consisting in the fact that any algorithm must be completed in a finite (maybe very large) number of steps.

Certainty (determinism, accuracy) – a property of the algorithm, indicating that each step of the algorithm must be strictly defined and not allow for different interpretations; the order in which the individual steps are performed must also be strictly defined.

Mass character – applicability of the algorithm to all problems of the type under consideration, for any initial data.

Formality – this property indicates that any performer capable of perceiving and executing the instructions of the algorithm acts formally, i.e. is distracted from the content of the task at hand and only strictly follows instructions. Reasoning “what, how and why?” The developer of the algorithm must do it, and the performer formally (without thinking) one by one executes the proposed commands and obtains the required result.