Subd access forms requests reports. Subd ms access. reports. Creating screen forms and reports in Microsoft Access database
Microsoft Access is a functionally complete relational DBMS. It provides all the necessary tools for defining, processing, and managing data when working with large volumes of information.
DBMS Microsoft Access provides the opportunity to control the definition of the structure and description of your data, work with them and the organization of collective use of this information. DBMS Microsoft Access also significantly increases the capabilities and facilitates cataloging and maintaining large volumes of information stored in numerous tables. DBMS Microsoft Access includes three main types of functions: definition (setting the structure and description) of data, data processing and data management. MS Access is a set instrumental means for creating and operating information systems.
Using Access, you can perform the following operations.
1. Design basic IS objects - two-dimensional tables, with different types data, including OLE object fields. Before filling a table with data, you need to create its layout.
2. Establishment connections between tables, with support for data integrity, cascading field updates and cascading record deletions.
3. Entering, storing, viewing, sorting, modifying and retrieving data from tables using various means of information control, indexing tables and logic algebra (for filtering data).
4. Creation, modification and use derivatives IS objects (forms, queries and reports).
The MS Access processing object is database file, having an arbitrary name and extension .MDB. This file contains the main objects MS Access: tables, forms, queries, reports, macros and modules. Thus, an information system created in Access is a database file.MDB plus the tools of Access itself. The operation of such an IS is the processing of an .MDB document by the Access application.
Information in the database Access data presented in the form of separate tables. In this case, each table column corresponds to a data field, and each row corresponds to a data record.
A data record consists of several fields. The following rule applies: a data record is a group of interrelated fields considered as a single whole. For example, a data record may contain information about a specific customer or product. Each data record in a table contains the same number of fields. Each field contains the same type of information. For example, in each data record of a certain table, the CLIENT NAME field will be for the client's name, i.e. will contain the same type of information.
Exist Various types data. The data type for a particular data field is selected depending on what information will be located in this field. The size of the data field is determined depending on the type selected for it. Descriptions of data types and their characteristics are given in Table 2.
Table 2 - Data types
| Data type | Application | Size |
| Text | Text or a combination of text and numbers, such as an address, and numbers that do not require calculations, such as telephone numbers. item numbers or postal code. | Up to 255 characters. Only characters entered in the field are stored; positions not used in the text field are not stored. To control the maximum number of characters entered, you must define the FieldSize property. |
| MEMO field | Long text or numbers, such as comments or descriptions. MEMO fields cannot be indexed or sorted. For storing formatted text or long documents instead of the MEMO field. you should create an OLE object field. | Up to 65535 characters. |
| Number | Numeric data used for mathematical calculations, excluding calculations involving monetary transactions (for which the monetary type is used). The type and size of values that can be contained in a number field. Can be changed in the FieldSize property. For example, in the field. occupying 1 byte on disk, only integers (without decimal places) from 0 to 255 can be entered. | 1, 2, 4 or 8 bytes. 16 bytes only for replication codes. |
| Date/Time | Dates and times. Storing date and time values in a Date/Time field ensures correct sorting. All changes made to date and time formats in the Language and Standards Panel window Windows management, will be automatically reflected in fields of the Date/Time type. | 8 bytes. |
| Currency | Currency values. The currency type is used to prevent rounding during calculations. Assumes up to 15 characters in the integer part of the number and 4 in the fractional part. | 8 bytes. |
| Counter (AutoNumber) | Automatic insertion of consecutive (differing by 1) or random numbers when adding an entry. To create an incremental counter, leave all property settings at the bottom of the window the same (default), with the FieldSize property set to Long Integer, and the New Values property set to Increment. To create a random number counter, the New Values property must be set to Random. | 4 bytes. For replication codes - 16 bytes. |
| Logical (Yes/No) | Fields. containing only one or two values, such as Yes/No, True/False. On off. | 1 bit. |
| OLE Objects | Objects (for example, documents Microsoft Word. spreadsheets Microsoft Excel, pictures, sounds and other data) created in other programs that use the OLE protocol. Objects can be linked or embedded in a Microsoft Access table. To display an OLE object in a form or report, you must use the Attached Object Frame control. | Up to 1 gigabyte |
| Lookup Wizard | A field is created. allows you to select a value from another table or from a list of values using a combo box. When you select this option in the list of data types, a Wizard is loaded to define them. | The size is the same as the size of the key field, which is also the Lookup Wizard, usually 4 bytes. |
| Hyperlink | The field in which hyperlinks are stored. A hyperlink can be either a UNC type (Universal Naming Convention - a standard file path format with the inclusion of a network server) or a URL (Uniform Resource Locator - the address of an object on the Internet or an internal network with the inclusion of an access protocol type). A hyperlink can consist of four parts: text that appears in a field or control; path to the file (in UNC path format) or page (URL); additional address - location inside a file or page; tooltip - text displayed as a tooltip. | The length of each part of the hyperlink is no more than 2048 characters. |
| Note: Numeric, currency, and Boolean data types, as well as Date/Time, provide standard display formats. To select formats for each data type, you must define the Format property. For all data except OLE objects, you can also create a custom display format. For more information, see the "Field Format Property" section below. |
In addition to tables, Access DBMS works with the following objects:
Forms;
Requests;
Reports;
Macros;
Modules.
The table is base MS Access object. All other objects are derivatives and are created only on the basis of previously prepared tables.
A form is not a standalone Access object. It simply helps you enter, view, and modify information in a table or query. Queries and reports perform independent functions: select, group, present, and print information.
Each MS Access object has Name. The length of the name of any MS Access object (tables, forms, etc.) is no more than 64 arbitrary characters (except for the dot and some service characters). The name may include spaces and Russian letters. Each object is worked in a separate window, and two modes of operation are provided:
1) mode designer, - when a layout, object structure (for example, table structure) is created or changed;
2) operational mode - when information is viewed, changed, and selected in the IS task window.
In addition, the database file includes another document that has its own window: Data schema. In this window you can create, view, change and tear communications between tables. These connections help you control data and create queries and reports.
Form, strictly speaking, it is an optional element of the IS (in principle, you can do without it), but it allows you to simplify the operations of entering and viewing data.
Request is a derived table that includes data from other tables and performs various operations on them. In particular, the request may contain calculated fields, i.e. fields whose values are functions values of other fields (possibly from different tables). In addition, queries allow you to group operations, i.e. operations on a group of records united by some common characteristic (for example, you can sum the quantity for records with the same code). Finally, queries allow you to make selections from tables based on some condition. In such cases, the algebra of logic is used.
Report - this is actually the same request, but formatted so that it can be printed on paper and presented to management (with beautiful headings, subtotals, etc.).
In essence, tables and forms are used to maintain the contents of the database, and queries and reports perform the main function of the information system - retrieving, transforming and presenting information.
Creation derivatives objects - requests, forms and reports - are maintained approximately according to the same scheme. Here are some features:
For derived objects, you must specify the name(s) of the table (query) on the basis of which this object is created;
When using the Wizard, you must check the “Element Panel” checkbox on the toolbar, and a set of icons will appear on the screen (Fig. 14.5), with which you design a form (report, query).
Creating any objects is possible in two ways: using the wizard and in design mode.
MS Access has powerful, convenient and flexible tools visual designing objects with the help of Wizards, and this allows the user, with a minimum of preliminary preparation, to quickly create a full-fledged IS - at the level of tables, forms, query selections and reports.
The masters themselves perform the necessary actions. When you call the Wizard, the corresponding sequence of dialog boxes opens. Having received answers to questions asked, The master does everything necessary actions. Let's say you want to generate a report. The Report Wizard will ask you what the report should look like and what data it should contain. After receiving the answers, the report will be completely ready. Using other Wizards, you can create database tables, queries and forms.
Working with Wizards is simple, reliable and efficient, and allows you to use the full richness of Microsoft Access.
The Builder is an Access tool that makes it easier to complete a specific task. The Expression Builder is extremely useful - it allows you to quickly create a complex expression. Using the Query Builder, you can easily create queries that are used to obtain data samples for a form or report. In addition to those listed, Access has other constructors. This is a macro constructor with the help of which various macros are generated, as well as constructors for menus, fields, colors, codes and others.
1 Launch Microsoft Access:
When Access starts, a window appears on the screen (Figure 1.1), in which you can:
Or create a new database, i.e. a file with the extension .MDB;
Or open an existing database, i.e. a file with the .MDB extension.
Creating a database. After selecting the [File-Create...] command, a standard filer appears on the screen (Figure 1.2), in which you need to specify the name and address created database file. For example, for information system with the name Telephone Directory.MDB.
After creating the file, Access opens an empty database window, and in this window all operations are carried out - creating and manipulating database objects. This window contains 7 objects (Tables, Queries, Forms, Report, Pages, Macros, Modules) and groups.
Opening a database. After selecting the [File-Open...] command, a standard filer appears on the screen, in which you must specify the name and address existing database file. Then the application window opens database window, providing access to already created database objects and the ability to create new objects.
MS Access - multi-window application, however only one database can be open at any time. It is her window that is main document window in the Access application (Figure 1.3), and closing it means closing the corresponding .MDB file. However, this window generates many subsidiaries windows (table, query, form, etc.). You can save an object (for example, a table layout), the window of which is on the screen, and assign it a name in the same way as you do with files: with the [File-Save] or [File-Save As...] command.
Working with tables
So, in the database window there is a list of existing objects DB - tables, queries, etc. You can work with the window of any object (child window) either in operational mode (for example, enter or view data in a table), or in designer(for example, change the table layout).
Before using any object (for example, filling a table with data), you must create this object (for example, create a table layout).
If the Table tab is selected, the tab window displays a list existing tables of this database.
To open an existing table, you need to highlight her name in this list and click the “Open” button.
Figure 1.3 shows the MS Access application window along with the Intercity database window. There are no objects in this file yet.
To be included in the database new tables, you need to click the “Create” button.
In order to fix layout existing table, select its name in the list and click the “Design” button.
The same operations are performed with all other MS Access objects.
Any object (table, query, form, report) can be created either manually (design mode) or using the Wizard.
The Builder is an Access tool that makes it easier to complete a specific task. The Expression Builder is extremely useful - it allows you to quickly create a complex expression. Using the Query Builder, you can easily create queries that are used to obtain data samples for a form or report.
When you call the Wizard, the corresponding sequence of dialog boxes opens. Having received answers to the questions asked, the Master performs all the necessary actions. For example, when generating a report, the Report Wizard will ask you what the report should look like and what data it should contain. After receiving the answers, the report will be completely ready. Using other Wizards, you can create database tables, queries and forms.
Create tables and queries manually(in design mode) ;
- create forms and reports using Masters.
Let's consider the sequence of steps when included in the database new tables. First you need to determine the subject area for which the database will be created. As a subject area, we define intercity telephone conversations, calculation of the amount of payment for long-distance telephone calls for individual subscribers. First of all, the fields that the table will contain are determined. Subscribers" database "Intercity". The required fields are shown in Table 3, with the “Subscriber Number” field selected as the key one. The key represents a field in the corresponding table. It uniquely characterizes each record contained within the table.
Table 3 – Fields of the “Subscriber” table
An ideal primary key is information such as customer number, account number, etc. Using such a key, it is easy to identify the corresponding record. Fields containing the customer name are not suitable for the primary key. It is always possible for several people to have the same name.
After determining the required fields, you can begin to create the actual table in MS Access.
To do this, you need to open the table designer window (Fig. 2.1) by selecting the Table tab and the line “Creating a table in design mode” or clicking the “Create” button.
In the upper part of the window that opens there is a created or modified layout table, which is simply a list of fields indicating the field name, data type and description. Each table field is one line at the top of this window (Figure 2.1).
Each table field should be assigned a unique name, determine the type of data that will be entered into it, and describe the contents of the field, i.e. enter text comments indicating the purpose of this field. The field name can have up to 64 characters. Description is an optional parameter and is used to further explain the field being used.
In the Field Name column (Figure 2.2) type an arbitrary Name. In the next column you must indicate data type for this field. The data type can be selected from the drop-down list that appears in this column (data types are described in Table 3). In addition, as soon as the cursor is in the Data Type column, a message appears at the bottom of the window. properties form(characteristics) of this field.
The properties form (lower part of the window) is a list of properties (on the left is the name of the property, on the right is meaning of this property) with a hint window for each property.
The list of properties changes depending on the data type that is currently displayed in the Data Type column. Clicking the mouse on the field values in the properties form, you can change this value (within the limits allowed for this data type). The field properties are listed in Table 4.
Table 4 – Field properties displayed in the table designer properties sheet
| Property | Purpose |
| Field size | Sets the maximum number of characters to enter in this field |
| New values | Defines how counter values change when new records are added |
| Field Format | Specifies the format for displaying the values of this field |
| Number of decimal places | Determines the number of decimal places used when displaying numbers |
| Input mask | Specifies an input mask to make it easier to enter data into a field |
| Signature | Defines the text that appears as the field label |
| Default value | Allows you to specify a value that is automatically entered into the field when created new entry |
| Condition on value | Defines the requirements for data entered into the field |
| Error message | Allows you to specify the text of the message displayed on the screen if the entered data violates the condition defined in the property |
| Obligatory field | Indicates whether the field requires a value to be entered |
| Blank lines | Determines whether blank lines are allowed in this field |
| Indexed field | Defines an index created on a single field |
| Unicode Compression | Sets compression for fields that use Unicode encoding to store data (MEMO fields, hyperlinks, and text fields) |
Most values are accepted by the system by default; some values can be selected from the drop-down list.
For text and number fields you must specify size fields, and for text this is the permissible length of the value (for example, 20 or 40 characters), and for a number - the representation format in the machine (byte, integer - two bytes, long integer, etc.). Maximum length text field 255 characters. If you need to enter text that is longer than this value, you can use the data type Memo.
For the Date/Time field, you must specify a format so that the system knows how to process the entered data. For example, if you select Short Date Format, the system will expect you to enter exactly dates(in the Russian version - DD.MM.YYYY), and if you select “Short time format”, you will have to enter HH:MM (hours and minutes) in this field.
In the input mask property line, it is possible to create a mask according to which data will be entered. For example, for the field phone number you can set a mask 00-00-00 , according to which phone numbers will be entered.
As the value of the “Condition on value” property, you can specify verification rule, that is, a Boolean expression that must evaluate to TRUE when data is entered into this field. For example, if for the conversation date field you write:
The system recognizes as an error the entry into this field of any value equal to or greater than the current date.
In the following property, you can record an arbitrary error message that will be issued by the system, for example: “This date value is not valid” or “the date is invalid.” In the future, if the date is entered incorrectly, this message will appear on the screen.
The Required Field property can be set to Yes (blank values are not allowed) or No (blank values are allowed).
For primary table key (the “Subscriber Number” field) in the “Indexed Field” property, you need to select: “Yes, matches are not allowed,” and then click on the “Define Key” button in the toolbar (with the key image). This defines a key field, or simple index.
Having completed creating the table layout, you must save the created table and exit the table designer window. If you close the designer window without saving the layout, the system will remind you that you either need to give the table a name or cancel your work. Let's give the created table the name “Subscriber”.
If it is needed change(modify) the layout existing object, you need to select this object in the list of the corresponding tab of the database window and click the button Constructor. A window with a layout of the object will appear on the screen (the same as when creating it). You can correct the layout and save it under the same or a different name.
After creating the layout, data is entered into the table. By double-clicking on the table name, it will open in table mode and you can begin entering data (Figure 2.3). If the first field in the table is a key field of the Counter type, then the contents of this column are automatically increased by 1 when you enter a new line. This ensures that the column values are unique.
Forms– these are database objects designed to view data from tables and queries, to enter data into the database, correct existing data and perform specified actions. The form may contain graphs, pictures, and other embedded objects.
You can enter data from the table without using any forms. But there are several reasons that make forms an indispensable means of entering data into the database:
When working with forms, access to tables (the most valuable thing in the database) is limited;
Different people may have different access rights to information stored in the database. They are provided with different forms for data entry, although data from the forms may flow into one table;
Entering data into a form is easier than into a table, and more convenient, since the form window usually displays one table record;
In most cases, information for databases is taken from paper forms (forms, invoices, invoices, certificates, etc.). On-screen forms can be made exactly like paper forms, thereby reducing entry errors and reducing staff fatigue.
You can create forms based on several tables or queries using the Wizard, using the AutoForm tool, “manually” in Design mode, saving the table or query as a form. A form created in any way can then be modified in Design mode. Let's consider some of the listed methods:
1. Run the command: ribbon tab Creation– toolbar Forms – Other Forms – Form Wizard.
2. In the dialog box Creating Forms select the tables (queries) and fields that will be placed in the form. Click the button Further.
3. In the following dialog boxes select wizard appearance forms, style, set the name of the form. Click the button Ready.
4. Use the Wizard to create forms Employees, Clients, orders, Managers.
5. Open the form Employees in Design mode. This mode is intended for creating and editing forms.
6. Place the elements in a convenient order for you, change the size and color of the text.
7. Add text to the form title Company employees.
8. Add an object to the form note Button(ribbon tab Constructor– toolbar Controls).
9. After you “draw” the button with your pointer, a dialog box will appear on the screen Creating Buttons(Figure 9).
Figure 9. Creating buttons on the form
11. Select a picture or text that will be placed on the button.
12. In the last dialog box Button Masters give the button a name and click Ready.
13. Button Master I wrote a procedure for this button in Microsoft Visual Basic. You can view the event processing procedure using the command Event Handling context menu buttons.
14. Create your own buttons Exit the application, Search for an entry, Delete an entry.
15. Sometimes a form requires several pages containing data from various sources, reference or supporting information. You can use a set of tabs for this purpose.
16. Create a blank form.
17. To add a set of tabs to the form, click the button Tab on the toolbar Controls. At first, only two tabs with formal names will be added Tab 1 And Tab 2.
18. Add another tab.
19. Rename the tab labels so that they display the names of the data that will be located in them: Employees, Managers, Help.
20. Go to the tab Employees and drag the form onto it from the database with the mouse Employees.
21. Place the form in the same way Managers to the tab Managers.
22. On the Help tab, put some tips on working with the database.
23. this form save with name Company employees.
24. You can create button forms in Microsoft Access. They contain only buttons and are designed to select basic actions in the database. To create a button form, you need to go to the ribbon tab Working with databases select a team Button Form Manager.
25. If the button form is not in the database, you will be asked to confirm its creation. Click Yes in the confirmation dialog.
26. will appear in front of you Button Form Manager, in which click on the button Create.
27. In the dialog box Creation(Figure 10) enter the name of the new button form and click OK.
Figure 10. Setting the name of the button form
28. The name of the new button form will be added to the list Button Pages window Button Form Manager(Figure 11). Select the name of the new button form and click on the button Change.
Figure 11. Button form manager
29. In the dialog box Changing a button form page click the button Create. A dialog box will appear Changing a button form element(Figure 12)
Figure 12. Creating buttons on the form
30. In the field Text enter caption text for the first button of the button form, and then select a command from the drop-down list in the field Team. In field Form select the form for which this command will be executed.
31. Add buttons in the same way Clients, Orders, Output.
32. In the dialog box Button Form Manager select the name of your button form and click on the button Default. The text will appear next to the name of the button form "(default)".
33. To finish creating the button form, click on the button Close.
34. The result should be the shape shown in Figure 13.
Figure 13. Main button form
35. Add some kind of drawing to the form.
36. In order for the main button form to appear on the screen when starting the application, you must click on the button in the main menu Access Options(Figure 14). For the current database, set the view form to “push button form”.
Figure 14. Setting Access options
Reports are intended for printing information. Often the data is presented in tabular form. Unlike printouts of tables or queries, a report provides greater ability to sort and group data; it provides the ability to add totals, as well as explanatory text, headers, footers, page numbers, styles, and various graphic elements.
There are several ways to create reports in an Access database:
Using the Report Wizard;
Based on tables or queries;
In Design mode.
1. In the database window, run the command: ribbon tab Creation– toolbar Reports – Report Wizard.
2. Select from the list a table (or query) that will be used as a data source (for example, a Customer Addresses query).
3. In the dialog box that appears Generating reports(Figure 15) move all available fields to the “selected fields” area.
Figure 15. Report Wizard
4. With Report Wizards create a report Birthdays. Use a table as your data source Employees.
5. If you need to print mailing labels, Access provides this option. To do this, select the table Clients and run the command: ribbon tab Creation– toolbar Reports – Stickers.
6. In the dialog box that appears (Figure 16), specify the sticker size, unit system, sticker type and click Further.
Figure 16. Create Labels dialog box
7. In the next step of creating a report, set the font, size, text color and style. Click Further.
8. Select the fields that will appear on the label. For example, Company name, Address, Telephone And Fax. If you want to display specific text on each sticker, enter it into the sticker prototype.
9. If necessary, change the label report name and click Ready.
10. Sometimes reports need to calculate totals, averages, minimum and maximum values, and percentages. To do this, run Report Wizard and specify the query as the data source Order price.
11. In the dialog box Masters
1. Basic information about reports.
2. MS Access DBMS tools for creating reports.
3. Creating a report in Design mode.
3.1. Setting report page options
3.2. Report structure
3.3. Formatting a report
To obtain a printed document in the MS Access DBMS, several options are provided:
Printing a table in table mode (displaying all records or records that meet the filtering conditions in table form);
Print a set of query records in table view;
Print a form in Form mode or Table mode;
Print the report.
1. Basic information about reports
Report– a customizable dialog box that allows you to organize a formatted presentation of data intended for output to a screen, printer, or file. In MS Access DBMS, reports are database objects.
Reports are the best way to present database information in a printed document. Compared to other means of printing data, reports have 2 fundamental advantages:
– provide ample opportunities for grouping, sorting and calculating subtotals and grand totals for large sets records (calculations can be made by group, several groups and for the report as a whole);
– allow you to output data in a form close to standard document forms.
Stages of creating a report.
At the first stage, a layout of the future report is developed on paper. At the same time, it is indicated: from which table or query it is necessary to display data, the levels of grouping and sorting of data are determined, which fields should be presented in the report and which controls will be used to display the data, whether calculated fields are needed, which graphic elements will be used to give professional looking report.
At the next stage, the report is constructed using a DBMS.
2. DBMS toolsMS Accessto create a report
To create a new report in the MS Access DBMS, you need to go to the tab in the database window Reports and click on the button Create . In the dialog box that appears New report(Fig. 10.1) select a report creation tool.
All tools listed in the dialog box list New report, can be divided into three main groups:
– tools for automatic report creation (Autoreport: column, Autoreport: tape);
– report creation wizards (Report Wizard, Chart Wizard and Mailing Labels);
– report creation designer.
Fig. 10.1. New Report Dialog Box
Automatic report creation tools allow you to create a report of the appropriate type based on the selected data source: table or query.
Report creation wizards allow you to create reports in dialogue mode with the user. Using wizards, you can create a data source for a report, select the type of data presentation, define fields for grouping, sorting and summarizing, select summary functions for a group, define the report layout and design style, and set the name of the report.
The report designer is the main tool that allows you to create reports of any complexity manually. The type of report and its content depend on the qualifications of the report developer.
Typically, automatic report creation tools or wizards are used to create a prototype report, and the report designer is used to polish the report to a professional look.
Working with reports can occur in three modes: in mode, in mode and in mode.
The mode is intended for generating a report and displaying all data from the source of records on the screen, the mode is intended for quickly generating a report based on mechanical substitution of data from several records of the data source (selection and sorting criteria are ignored), the mode is intended for creating and changing the structure of the report. To change the mode of working with the report, you can use the button View on the toolbar Report designer or similar menu command View.
3. Creating a report in Design mode
To create a report in the mode, you must perform the following steps:
1. Configure page parameters.
2. Select or create a record source for the report.
3. Determine the levels of grouping and sorting.
4. Place record source fields in the report.
5. Add calculated and free controls.
6. Configure the properties of control elements and report sections.
7. Format the report for a professional look.
3.1. Setting report page options
The main purpose of the report is to print information from the database in a formatted form (close to the form of a paper document). Therefore, before designing a report (placing controls in the report), you need to configure the report page parameters (select a printer, determine the sheet size and its orientation, set the margin sizes, etc.).
If there is no physical printer, you must install the printer programmatically using the command operating system Start | Setting | Printers | Installing the Printer.
To configure report page parameters, you need to run the command File | Page settings …. Dialog window Page settings presented in Fig. 10.2.
Page tab (a) Fields tab (b)
Fig. 10.2. Page Setup Dialog Box
Page settings are configured for each report.
If it is possible to output reports to different printers, then in the dialog box Page settings on the tab Page You can select the type of printer you need. Dialog window Selecting a Printer presented in Fig. 10.3.
Fig. 10.3. Select Printer Dialog Box
Other page parameters depend on the type of printer selected (see Fig. 10.2): maximum paper format and size, minimum margin sizes, paper feed type.
The color of the report depends on the type of printer selected and the settings of its properties: output in color or black and white.
3.2. Report structure
The structure of the report in the mode is shown in Fig. 10.4. Any report has the following sections:
– report title(displayed only on the 1st sheet of the report (title page) and may contain the name of the report, company logo, legal address of the enterprise, etc.);
– page header(displayed at the top of each page and may contain headings, printing date, page number, etc.);
– data area(designed to display fields from a query or table - the source of records for the report);
– footer(displayed at the bottom of each page and may contain headings, printing date, page number, etc.);
– report note(displayed only on the last sheet of the report and may contain final calculations, conclusions on the report, etc.).
Of all the sections listed, the required section is the data area.
The report title and report comment sections are displayed/hidden in the window at the same time. The same principle of showing/hiding applies to the header and footer sections (see Fig. 10.4).
When grouping records, the reports window (see Fig. 10.4) can be added for each group, and in the reports, up to 10 levels of grouping and sorting can be defined, independently the following sections:
– group header(displayed at the beginning of each new group and may contain a field by which grouping is carried out);
– group note(displayed at the end of each group and may contain group totals).
You can change the height of the area of any report section using the mouse by dragging the bottom of the section up or down.
The report width is adjusted based on the selected paper size, sheet orientation, and printed margin sizes.
An example of determining the report width for A4 paper is presented in Table 10.1.
Table 10.1
The width of the report should not exceed the calculated size, since the information displayed in the report will be split over several pages.
You can change the width of the report using the mouse by dragging the right border of the report to the left or right.
Just like in the form, you can insert various elements controls: attached, calculated and free. Expressions in calculated report fields are formed according to the same rules as for forms:
= [Price] * [Quantity]
SUM([Cost]
Last Name & " " & Left(First Name; 1) & "."
Using a control element Subform/report You can embed subreports into the main report.
Properties can be specified for each control, section, and report as a whole.
Generated report Order form in mode and mode is shown in Fig. 10.5.
Fig. 10.5. Order Form report in Design mode (a) and Preview mode (b)
Fig. 10.6. Properties window for the SequentialNumber field
To display each group (order form) on a separate page, you need to set the section properties window Note from the "Order Code" group on the tab Layout property value End of page: After the section(Fig. 10.7).
Fig. 10.5. Properties window for the NoteGroups1 section
3.3. Formatting a report
One of the elements of the report window is the toolbar Form/report format, containing buttons and drop-down lists that make it easier to select formatting options (color, font, line thickness, alignment method, etc.).
Formatting also means placing controls in report sections, aligning controls, changing the size of controls, and setting spacing between controls. To perform these operations you can use the mouse or use menu commands Format (Align..., Size …, Vertical spacing... And Horizontal spacing...).
To quickly perform report formatting operations, you can customize the toolbar Form/report format by copying the buttons from the menu onto it Format.
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 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 different icons:
You can change the representation of objects in the Transition Pane to the familiar one used in earlier versions 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 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 (there can be up to 20 digital or letter designations). 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 and etc.
In practice, the following topics were studied and described:
1. Create a presentation
