The ms access database report may contain. Create reports as Access database objects. Database management systems and expert systems

Shape is a database object used to view, enter, or change data. Compared to manual data entry, a form-based interface allows you to automate the process by adding functional elements to the form, introducing data validation, customizing the presentation of data for viewing.

Forms are created based on designed databases. Access provides a variety of ways to create forms, depending on the functionality and complexity needed. The easiest way to create a form is to use masters of forms. Using the Form Wizard allows you to create quite flexible forms using fields from different tables, different kinds forms and design styles. In Fig. Figure 8 shows a complex form created from a form based on the child table “Automatic coupler repair list”, inside of which a form based on the parent table “Customer Information” is embedded.

Rice. 8. Complex form in design mode

Controls are user interface objects that allow you to enter or display data. The controls are on the tab Constructor in Group Controls.

The use of controls in forms allows you to significantly expand the functionality of the forms, as well as add the display of the necessary data. In mode Designer forms, you can provide the designed form with a title or explanatory text using the tool Inscription. Some fields can be highlighted using controls − Line or Rectangle. When printing data it is always useful to know when the data was entered, you can set these parameters using the button date and time.

Another control when designing forms is Combo box. This control is used when it is required that the value of a field in the table is not entered manually, but is selected from famous list values. As a rule, these lists are various kinds of reference data.

Combo box is created as follows. In form designer mode, select the right rectangle of the field that will be equipped with a control Combo box and, click right click mice. A menu will open, from the proposed list you should select Convert element> Combo box. Next through the option properties need to call Properties window and being on the tab Data install Row Source Type – List of Values(Figure 9).In the property Row source specify a list of values ​​in the following format – 1001; 1002; 1003; 1004; 1005.

Rice. 9 – Field properties window with the “Part code” combo box

Created element Combo box allows you to change the part code while in the form viewing mode with automatic change its values ​​in linked tables.

If, while working in a form, you need to display data from another form or enter it into another form, you can create a command button in the original form that opens the fields of another form. To create such a button, you need to go to the tab Creation and click on the control Button. While holding down the mouse button, draw a rectangle in the place where the command button should be located. A menu will open Creating Buttons. Next, you should select the action that will be performed when the button is pressed, for example, to open a form, select from the list Categories – Working with the form on the list Actions–Open the form. Then click on the button Further.

The next dialog box prompts you to select the form to open from among the previously created forms. The form you open must be based on a related table that has a key field that is related to the table key field of the original form. Next you should set the option Text, enter the text that will be on the button and press the button Ready.

The command button works as follows. After entering its value into the key field of the original form, clicking this button displays a subform with data related to the key field. A subordinate is a form inserted into another form. A subform is created using a button Subform, which is located on the tabs Constructor in Group Controls or by dragging the required form from the navigation area to the main form.

Report, unlike a table, presents data with summary calculations; data of the same type can be grouped with subtotals displayed for each group. With a header, header, footer, and calculated totals, a printed report provides more information than a table. Based on the table, the report can select from it only the necessary data for a certain period.

To create a report in Access, you need to go to the tab Creation in Group Reports click Report Wizard.

Report Wizard is an Access tool that helps you create a report based on the answers received to questions asked by the user.

In the field of this window Selecting a table/query you should select the desired table on the basis of which the report will be created and click on the button Further. Then the report creation wizard offers a series of dialogs with which you can select the type of report (for example, table or column), the fields to be displayed (all or only some), sort the data in the fields (in alphabetical order or in ascending order), select report style (for example, strict). When answering in dialogues, you need to click on the button Further until this button becomes unavailable - that means all the questions are over and all you have to do is click on the button Ready.

The finished report can be viewed (active View Mode). You can modify the report type in the mode Report designer, where you can go from the window Database by clicking on the button, Constructor.

To switch to design view, right-click the report name in the Navigation Pane and select Constructor.

You can change the properties of the report, its sections, and its included controls in the properties window. You can add fields from a base table or base query from the area to a report layout List of fields. You can then add fields to the report by dragging them from the area List of fields.

While in mode Report designer, you can provide the designed report with a title (it should be placed in the area Report title), header, which displays the column headings at the top of each page, (placed in the Page header), a footer that displays summary information at the bottom of each page, (area footer usually contains the current page number), a notes area where the report total is located. In Fig. Figure 10 shows the report in design mode with the organization of a summary field that counts the number of entries in the “Types of faults” field.

Rice. 10. Report viewing window in design mode

Saving changes. By saving the report layout, you can run the report as needed in the future. The layout will remain unchanged, but the report itself will include current data each time it is printed. You can change the report layout or create another, similar report based on it.

Saving the report layout. To save the report layout, click the button Microsoft Office and then select the command Save or press the keyboard shortcut CTRL+S. You can press a button instead. WITH guard in the window Panel quick access . If the report has not yet been given a name, you must enter one in the Report name field, and then click the button OK.

Save the report layout under a different name. To save the report layout under a different name, click the button Microsoft Office and then select the command Save as. In the dialog box Preservation enter name in the field Saving an object to and then into the field How select item Report and press the button OK.

Print the report. The report can be printed from any mode, even if it is not open. Before you print, you should carefully check page settings (such as margins and orientation). In Access, page settings are saved with the report, so you only need to configure them once. These parameters can be changed in the future if necessary.

62. Forms, reports, queries in the Access database, their purpose, creation methods.

Requests. These objects are used to extract data from tables and present it to the user in a convenient form. Using queries, operations such as data selection, sorting and filtering are performed. Using queries, you can perform data transformations according to a given algorithm, create new tables, automatically fill tables with data imported from other sources, perform simple calculations in tables, and much more.

The peculiarity of queries is that they draw data from base tables and create temporary data based on them. resulting table(snapshot) – an image of fields and records selected from the base tables. If they want to emphasize the fact that this table is “temporary,” then it is also called a snapshot. When we work with the main database tables, we physically deal with hard drive, that is, with a very slow device. When, based on a query, we receive the resulting table, we are dealing with a spreadsheet that has no analogues on the hard drive - this is an image of the selected fields and records. The basic principle is that no ordering is required from the base tables. All

records in the main tables are entered only in the natural order as they arrive, that is, in an unordered form. Working with an image is faster and more efficient than working with tables stored on the hard drive.

The database can also be updated using a query. All data is entered into the base tables in the order they are received, i.e. they are not ordered. But with the appropriate request, you can get data sorted and filtered as needed.

Forms. If queries are special tools for selecting and analyzing data, then forms are tools for entering data. Their meaning is the same - to provide the user with the means to fill out only those fields that he is supposed to fill in. At the same time, you can place special controls on the form (counters, drop-down lists, switches, checkboxes, etc.) to automate input. The advantages of forms are revealed especially clearly when data is entered from completed forms. In this case, the form is made by graphic means so that it repeats the design of the form - this significantly simplifies the typesetter’s work, reduces his fatigue and prevents the occurrence of printing errors.

Reports. In their properties and structure, reports are in many ways similar to forms, but are intended only for data output, and for output not to the screen, but to the printer. In this regard, reports differ in that they take special measures to group the output data and to display special design elements characteristic of printed documents.

63. Characteristics, purpose of modern subdivisions.

Microsoft Access 2003, a 32-bit relational DBMS running in Windows environment 2003 /T/XP and later. This software product is part of an integrated Microsoft package Office Professional is relatively easy to learn and use, suitable for beginners and professionals alike. The main characteristics of the Access DBMS include:

Graphical multi-window interface that allows you to interactively create tables, forms, queries, reports and macros

Availability of developed interactive design tools (tables, forms, queries, reports, macros)

Availability of special tools that automate work, called wizards

Availability of data integrity controls at the database level

Availability of data security tools (password protection, access level restrictions and the ability to work in multi-user systems)

Using OLE object technology to introduce objects of various natures (texts, spreadsheets, pictures) into the database

Having your own language Visual programming Basic, an object-oriented language for developing user applications that is independent from the DBMS and portable to other MS Office applications

Full SQL query language support

Possibility of integrating data from different DBMSs.

We should take a closer look at Microsoft software products. The most interesting feature of these packages is their great integration, collaboration and data usage capabilities, since these packages are products from the same manufacturer and also use similar data exchange technologies.

Visual FoxPro is different high speed, has a built-in object-oriented programming language using xBase and SQL dialects (These dialects are built into many DBMSs). Has a high level of object model. When used in computer networks, it provides both exclusive and separate user access to data.

Access included in the most popular MS Office package. Main advantages: familiar to many end users and has high data stability, easy to learn, can be used by a non-professional programmer, allows you to prepare reports from databases of various formats. The Access software package is designed for creating free-form reports based on various data and developing non-commercial applications.

Visual Basic is a universal object-oriented programming language whose dialects are built into Access, Visual FoxPro. Advantages: versatility, ability to create OLE components, low requirements for computer hardware resources. It is used to create medium-power applications that are not associated with high data processing intensity, development of OLE components, integration of MS Office components.

VisualC++- the most powerful object-oriented programming language, has unlimited functionality. This language is designed for creating application components to perform speed-critical operations.

SQL Server- database server, implements the client-server approach and interacts with the specified packages. Main advantages: high degree of data protection, powerful data processing tools, high performance. Scope of application: storage of large volumes of data, storage of highly valuable data or data that requires confidentiality.

These software products have the ability to visually design the user interface, that is, the developer creates interface elements from ready-made fragments and programs only their changes in response to any events.

The technology for creating simple, detailed, composite database reports in the MS Access 2007 DBMS is described.

* * *

The given introductory fragment of the book Databases: Creating reports in DBMS MS Access 2007 (Team of authors, 2013) provided by our book partner - the company liters.

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 as usual for MS Office way: 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.

note that different types objects are marked 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.

Reports, like forms, are derived Access objects that are based on tables or queries. Forms are a convenient tool for quickly viewing data, but their main purpose is to enter and edit data. After entering data into tables using forms that make it easy to enter, you may want to display the data in an organized manner. Reports are used to present data in a convenient and easy-to-understand form. Outputting data using reports. General provisions. Like any Access object, you can create a report manually or using the Report Wizard (recommended for creating a draft report). The report is created based on a table (several tables) or a query. The process of creating a new report is similar to creating any Access object, i.e. you just need to select the database object Report – Create and then in the New Report dialog box select the report creation mode.

To protect the Access database, it uses the workgroup file system.mdw (a workgroup is a group of users who share network resources), to which databases on workstations are connected by default. The workgroups file contains Accounts users and groups, as well as user passwords. Accounts access rights to the database and its objects can be granted, while the access permissions themselves are stored in the database. To ensure protection of the Access database, you must create working group, using the file - workgroup administrator wrkgаdm.exe. When creating a unique workgroup, you specify the user name, organization name, and workgroup code. The MS Access workgroup file contains the following built-in accounts: 1. Admins - standard user account. These records are the same for all instances of Ms Access; 2. Admin - the administrator group account - is unique in each workgroup file; 3. Users - contains user accounts. To create a workgroup file, you need to exit Access and find the workgroup file in the system or system32 folder in the windows directory and create a new workgroup (can be up to 20 numbers or letters). The Admins group can contain an arbitrary number of users, but there is always one owner of the object (the owner of the object can be the account that created the object or to which the rights to use it were transferred). Since reading the Admin record is possible for all workgroups and these accounts are the same, the ADMIN user must be removed from the administrators group, for which you must create a new administrator account and set a password for his accounts and for the owner accounts. Only its owner and members of the Admins group can encrypt and decrypt the database. For encryption, Jet uses the RSA algorithm (named after the first letters of the last names of its inventors: Rivest, Shamir, Adelman) with a key based on the workgroup ID. Database encryption has two negative side effects. Firstly, its performance decreases - according to Microsoft estimates, by 10-15 percent. Secondly, an encrypted database cannot be compressed by programs such as PKZip, LHA, Stacker and DriveSpace. More precisely, you can compress it, but there is no point in doing so - its size will decrease slightly. Differentiation of user access rights Access permissions are called explicit if they belong to or are assigned to a user account.

An example of creating data protection in MS Access:

Fig.4 Creating data protection

Creating reports in MS Access DBMS. Preparation of reporting documentation using MS Access. Protection of projects using MS Access DBMS.

The database security system must ensure the physical integrity of the database and protection against unauthorized intrusion to read the contents and change the data. Database protection is carried out at two levels: - at the password level; -at the level user (protection of user accounts and identified objects). To protect the Access database, it uses the workgroup file system.mdw (a workgroup is a group of users who share network resources), to which databases on workstations are connected by default. The workgroups file contains user and group accounts, as well as user passwords. Accounts can be granted access rights to the database and its objects, while the access permissions themselves are stored in the database. To ensure protection of the Access database, you need to create a workgroup using the file - workgroup administrator wrkgаdm.exe. When creating a unique workgroup, you specify the user name, organization name, and workgroup code. The MS Access workgroup file contains the following built-in accounts: 1. Admins - standard user account. These records are the same for all instances of Ms Access; 2. Admin - the administrator group account - is unique in each workgroup file; 3. Users - contains user accounts. To create a workgroup file, you need to exit Access and find the workgroup file in the system or system32 folder in the windows directory and create a new workgroup (can be up to 20 numbers or letters).

Example of creating reports in MS Access:

Fig.5 Creating reports

CONCLUSION

During educational practice UP 04.01. And UP 04.02. I consolidated my knowledge in the following disciplines: MDK 04.01. “Working with computer and PC hardware” and MDK 04.02. "Work with software Computers and PCs." I repeated such material as: external computer architecture, connecting devices, setting up programs and interfaces, working with editor programs, etc.

In practice, the following topics were studied and described:

1. Create a presentation

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