Database reports, Automation of database processing...

Database reports

Reports in the Access DBMS are single- and multi-table (composite), are created on the basis of tables and/or queries and can contain additional controls. The Reports toolbar contains a number of buttons:

Report is a quick way to create a report based on a table or query.

Report Wizard - the ability to create multi-table reports with methods for grouping and sorting rows.

Stickers - Create standard size labels.

Empty report - report from scratch .

Report Designer is a tool for detailed configuration of sections and report elements.

The layout of the report is divided into sections: report header, header, group header, data area, group note, footer, note report, in which three controls are placed types: attached (table/query fields), free (not related to the data source), and calculated (based on formula expressions) (Figure 10.42).

Controls for Reports>

Fig. 10.42. Reporting controls

Reports can be printed, sent as an e-mail message (the following extensions are possible for the message: .htm, .pdf, .rtf, .snp (snapshot) , .txt and , χps ).

Let's look at some examples of how to create reports.

1. One-tab report RESOURCE GUIDE (Figure 10.43).

To prepare a report, you must perform the following actions:

• Go to the Create , toolbar Reports, button < strong> Report Wizard. Select the RESOURCE table, all fields. Add grouping level by field - Resource type. Sort the report rows by the Resource name field. Layout - Structure, Orientation - Landscape. Style - ASPECT, report title - RESOURCE MANAGEMENT, Edit report layout.

• Place the cursor on the group header Resource Type & quot ;, remove the Resource Type field, insert the calculated field, field properties:

Data = IIf ([Inventory type] = "M"; "Material resources"; IIf ([Resource type] = "0"; "Equipment resources"; "Labor resources"))

• Click the Grouping and Sorting button, the More - icon set With the note section & quot ;. Place in the reminder area of ​​the Resource Type calculated field, field properties:

Data: = Sum ([Stock] * [Resource Price]),

Money format, The number of decimal places 2,

Inscription - Resource Value Reserve.

• Place the calculated field in the note note area of ​​the report, field properties:

Data: = Sum ([Stock] * [Resource price]), Currency format, Number of decimals 2, Inscription - Total reserve of resources. "

Single-table report with line grouping

Fig. 10.43. Single-table report with line grouping

2. Multi-table report Product Information (Figure 10.44).

To prepare, follow these steps:

• Go to the Create toolbar Reports, button Report Wizard . Select the table PRODUCTS, all fields; table NORM, fields: Resource code, Unit of valuation, Rate of consumption, RESOURCE table, fields: Name of resource, Type of resource, Price, Unit of measure. Select the type of data representation - PRODUCTS. Add a grouping level - the Resource Type field. Name of the resource. Layout - STRUCTURE, Orientation - Landscape. Style - INITIAL, report title - Product information, Edit report layout.

• Delete in the header area of ​​the group Resource Type homonymous field. Add a calculated field, properties:

Data: = IIf ([Inventory type] = "M"; "Material resources"; IIf ([Resource type] = "About"; "Equipment resources"; Labor resources ))

Multi-table report

Fig. 10.44. Multi-table report

Automating Database Processing

Macro is one of the ways to automate data processing, used as a method for handling predefined events in a form, report, or control. Highlight three types of macros : isolated ; embedded and groupware. The group macro combines several related macros into a single macro object. Within a group macro, each individual macro has its own name. Isolated macros are stored as standalone objects and run from the Macros by Name section. Embedded macros are stored in event properties in forms, reports, or controls that do not appear as objects in the Macros section and can not be converted to Visual Basic modules. A macro is created using the Macro Builder, it contains macros that are selected from a fixed set. To execute macros, you specify logical conditions, arguments (actual parameters).

The VBA program module is created as a subroutine (Sub) or function (Function) used in event processing procedures.

The following are examples of macros.

1. Macro of the cost price and product profit when the price of a resource changes.

To create a macro, proceed as follows:

• Go to the Create mode, the Other toolbar, the Macro button, the Macro.

• Click the Show all actions button to display the full list of macros, the Conditions button to set the conditions for executing macros, and the Arguments button to display the arguments used. Enter a list of macros with parameters (Table 10.5).

Table 10.5

Macro macro 1




[Forms]! [RESOURCE]! [Resource Price]. [OnDirty]

Output to a Screen

No; The macro is working

Install Messages


Open Zairos

New table; Table; Change

Open the Query

New production costs and profit; Table; Change

Open the Query

Delete COST; Table; Change

Output to a Screen

Yes; Macro completed

Install Messages


• The condition for executing macros is created using Expression Builder - the context command Build , set the condition: [Forms]! [RESOURCE]! [Resource Price]. [OnDirty] - change the price of resources.

• Save the macro as Macro 1.

• Implement Macro 1 macro in the RESOURCE form: open the RESURS form in the Designer, specify the event - Close the form, launch Macro 1.

2. Creating a program module from a macro.

To create a macro, you must do the following:

• Select Work with database .

• Select macro Macro 1, click the Convert Macros button. Specify, add an error handling program, macro notes.

• The text of the software module.

Also We Can Offer!

Other services that we offer

If you don’t see the necessary subject, paper type, or topic in our list of available services and examples, don’t worry! We have a number of other academic disciplines to suit the needs of anyone who visits this website looking for help.

How to ...

We made your life easier with putting together a big number of articles and guidelines on how to plan and write different types of assignments (Essay, Research Paper, Dissertation etc)