Select queries in Access. How to set conditions for selecting records in a query? Selection conditions in access requests are all except

Most modern DBMSs have their own version of QBE, which differs slightly from the first description of QBE, proposed in the late 70s of the twentieth century. Let's look at some of the capabilities of the QBE DBMS MS Access.

Let's use as an example a database table that relates to trading (Fig. 3.10). Table name TYPE (types of products). It has columns: product - product names; color – its color; cost – the cost of the product.

Data sampling can be carried out according to the following options:

1. A simple selection, for example: “Get green products from the TYPE table.”

2. Simple sampling with ordering.

3. Sampling with qualifiers (conditions). The selection of records from the source table can be based on: a) exact match; b) partial coincidence; compared.

Queries allow you to obtain resulting tables whose fields satisfy certain conditions (criteria). These conditions are specified in the request form in the line Selection conditions. The selection conditions are logical expressions consisting of operators and operands. Comparison operators =,< , >, <>(not equal), Between, In, Like and logical operators And, Or, Not. Templates with wildcard characters are allowed.

Rice. 3.10. Example database table

If the exact value is not known or it is necessary to enter the value incompletely, then it is convenient to use template (sample) with wildcard characters (signs). Examples of wildcards:

* - matches any number of any characters. Example: 77* - to find all phones with numbers starting with 77.

? - matches one text character. Example: 77-4?-0? - to find all telephones with numbers containing the four specified digits.

Templates are used in conjunction with the operator Like . This operator allows you to create patterns that use wildcard characters when searching text fields. For example, the employee's last name is not known accurately. This could be Petrov, Petrovsky, Peotrovsky, etc. Then should be used to select in line Condition record Like "Pe*".

It is known that the name consists of 4 letters. Then the entry Like "????" will do.

Operator Between specifies the range of values. For example, Between 1 And 5

(the specified edges of the intervals are included in the sample).

Operator In checks for equality to any value from the list specified in parentheses. For example, In("pen","perfume").

Logical operations AND, OR can be specified explicitly in a condition expression using the operators AND And OR . For example, “perfume” OR “pencil”.

As operands Queries can use literals, constants, and identifiers (links).

Literals are specific values ​​perceived by the system as they are written. A literal can be a number, a date, or a string. For example, 1146, #31.01.02 #, "Lipetsk".

Constants are constant values ​​that are defined in Access. For example, True, False, Null, Yes, No.

Identifier makes a reference to a field, control, or property. Identifiers can be names of fields, tables, forms, and so on. They are enclosed in square brackets. A reference to a specific value must indicate its location in the hierarchy of objects in the database. A link to a field in a table looks like [Table Name]![Field Name]. For example, [Employees]![Last Name].

It is advisable to create the selection condition using the expression builder. To do this, open a window Expression Builder, by clicking the button Build on the toolbar or by selecting the command Build in the context menu. You must first place the mouse cursor in the condition input cell.

The condition for an exact discrepancy between the values ​​of one of the fields. If you need to find records in a table whose field values ​​do not satisfy a certain condition, then use the operator Not . Not operator or<>is entered before the value being compared. Example. Select all records of the TYPE table, except for the “pencil” records in the field Product. To do this, in the request form in the field column Product in line Selection condition Not "pencil" is entered.

Imprecise match condition. Selecting records based on inaccurate conditions

value matches can be achieved using the Like operator. This operator allows you to find the required records, knowing only the approximate spelling of the text value. The Like operator can use wildcard patterns, which expands the ability to search for records when conditions are not specified precisely. Example of a selection condition: Like “[d-k]*”. Here - (minus) matches any character from the range. The range must be specified in ascending order (d-k, not k-d).

Select records by range of values. To specify a range of values ​​in the query designer window, use the operators >,<, Between, Like. Их можно применять с текстовыми, числовыми полями и полями типа даты. Примеры: в строке Selection condition possible to enter: >100.00 AND< 500.00; Between # 01.01.97 # AND #31.03.97#; Like “*”. Напомним, что символ # применяется для данных типа «дата/время».

Example 1. A query with an exact mismatch in one field and a comparison condition in another field. A request to select from the TYPE table all non-red goods whose price is more than 5 is shown in Fig. 3.11 (request created in mode Designer).

There are four types of change requests: to delete, to update and add records, and to create a table.

Removal request deletes a group of records from one or more tables. For example, a deletion request allows you to delete records of products that are no longer in stock or for which there are no orders. With a delete request, you can only delete the entire record, not individual fields within it.

Request to update records makes general changes to a group of records in one or more tables. For example, prices for all dairy products rise by 10 percent or the salaries of employees in a certain category increase by 5 percent. A record update query allows you to change data in existing tables.

Request to add adds a group of records from one or more tables to the end of one or more tables. The add request is also useful when doing the following:

Adding fields based on selection conditions;

Adding records if some fields from one table do not exist in another. The append query will add data to the matching fields and skip the rest.

Query to create a tableWith Creates a new table based on all or part of the data from one or more tables. A create table query is useful for doing the following:

Creating a table for export to another Microsoft Access database;

Creating reports containing data from multiple tables;

Create a backup copy of the table.

Creating an archive table containing old records;

Improved performance of forms and reports based on multi-table queries or SQL expressions.

Selecting data from one table

In table mode, you can perform various operations with the data of this table: viewing, sorting, filtering, etc. One of the advantages of queries is that they allow you to quickly select the necessary data from several related tables. Moreover, all the techniques used when working with one table are also suitable for complex multi-table queries.

After running a select query, Microsoft Access creates a recordset containing the selected data, which you can work with in the same way as a table.

The easiest way to create a query based on one table is this: open the database window, select the tab in the database window Requests, press the button Create, select mode in a new window Constructor And Ok. In the next "Add Table" window, select the desired table, and then click the buttons Add And Close.

The query designer window (Fig. 10.1) is divided into two parts. At the top there are lists of table or query fields based on which a new query is created. At the bottom there is a QBE form (query by sample), in which the work of creating a request is performed. Each form column represents one field used in the request.

First line The query form is used to select fields that can be given names that are used when displaying query records. In the second line The query form displays the name of the table from which the field was selected. IN third line form, you can specify which columns you want to sort. Checkboxes in the form line Output on display are responsible for displaying the fields in a recordset. By default, all fields included in the request form are displayed. To enter a condition for selecting records, use the line Selection condition.

Fig. 10.1. Query Builder Window

Including fields in a request. To include a field in the request form, you need to select it in the table and drag it with the mouse into the corresponding field of the request form.

Setting field properties. In general, fields in a query have the same properties as in the table from which they are transferred. However, you can set other property values. To do this, click on any cell of the corresponding column in the request form and press the button Properties on the toolbar. After this, the field properties are entered.

Entering selection conditions. If you need to select records with a specific field value, you need to enter it in the cell Condition selection this field. The text value as a condition is enclosed in quotation marks. When specifying a selection condition, you can use relation signs < , >, >=, <=, =,< > and logical operations or, and.

In addition, Access provides special operators for selecting data output in a query:

between- defines the range of values. Between 10 and 20 means the same as the expression >=10 and <=20 ;

in- specifies the list of values ​​used for comparison. Expression in(“ wa”,” ca”,” id”) means the same as the expression “ wa ” or “ ca ” or “ id ” ;

like- this operator allows you to use the following symbols when selecting text fields: ?, *,# . Symbol # indicates that a given position must contain a number, symbols ? And * have the same purpose as in OC MS DOS file names.

For example, like “ B*” - means you need to select fields starting with a letter IN.

Access processes selection conditions for dates and times in any format. When entering, the date or time must be surrounded by # symbols. For example, #15 April 1998#, #15/04/98# define the same date.

Access provides several functions that you can use when setting filter criteria for dates and times:

day(date of)- returns the value of the day of the month in the range from 1 to 31. If you need to select records with specific days of the month, set a calculated field, for example, day([Order_date]) and enter a selection condition, for example, >10. In this case, all field records whose calculated field is >10 are selected;

month(date of)- returns the value of the month of the year in the range from 1 to 12;

year(date of)- returns the year value in the range from 100 to 9999;

weekday(date of)- returns an integer from 1(Sunday) to 7(Saturday), corresponding to the day of the week;

date() - returns the current system date.

Calculated fields. You can perform calculations on any table fields and make the calculated expression a new field in the recordset. In this case, you can use any functions built into Access and perform arithmetic operations on table fields using the operators: +, -, *, /, \, ^, mod, &. For example, let's say there is a field name called “ Quantity”, where the number of product units and the field “ Price” , where the cost of a unit of goods is recorded. Then, to calculate the cost of the goods, you need to enter the expression in the empty field of the request form Quantity*Price and the values ​​of these fields will be multiplied.

Specifying Calculated Field Names. When you create any expression in a query form, Access places the default field name “Expression1:". You can change or assign field names, which is important if you need to use them in a report or other queries. This is done using the properties window. To do this, click on any cell of the corresponding column and press the button Properties on the toolbar and select Signature.

Parametric queries. Request conditions can be included directly in the request form, but in order to make it more universal, instead of a specific selection value, you can include a parameter in the request, i.e. create a parametric query.

To do this, enter a phrase in square brackets in the “Selection condition” line, which will be displayed as a “hint” during the dialogue, for example [Enter last name]. There can be several such parameters, each for its own field, and the name of each parameter must be unique.

Sorting data. Access typically displays records in the order in which they are retrieved from the database. You can change the sequence of data output by setting the sort order Ascending or Descending.

Final queries. Sometimes we are not interested in individual table records, but in the total values ​​for groups of data. For example, you need to find out the average sales volume for each month separately. This can be done using a summary query. To do this you need to click on the button Group operations A new line with this name will appear on the toolbar and in the form. In this case, grouping is carried out according to all fields entered in the form, but the result is not summed up. To get the results you need to replace Grouping in line Group operation to specific final functions.

Access provides several features to enable group operations. The main ones:

sum- calculates the sum of all values ​​of a given field in each group. Used only for numeric and currency fields;

avg- Calculates the arithmetic mean of all values ​​of a given field in each group;

min, max- calculates the smallest (largest) value of a field within a group;

count- calculates the number of records in which the values ​​of this field are different from Null.

Selecting records to form groups. You may not include some records in the final query groups. To do this, you need to add one or more filter fields to the request form. To create a filter in the Group operation line, select the setting Condition, uncheck the box Output on display For this field, a selection condition is introduced.

Selection conditions for query fields are set in the line of the same name. If the conditions in this line are defined for several fields, then they are connected by the logical function “AND”. If the selection conditions for different fields are defined in different lines: Selection condition And Or, then such conditions are connected by the logical function “OR”. A query can have complex criteria, according to which fields have filter conditions defined on both rows.

A record selection condition is a set of rules predefined in Access and set in the query by the user. When constructing a selection criterion (condition), queries can use expressions, operators and Access functions.

Operator Between allows you to set an interval for a numeric value, for example: Between 300 And 500

Operator IN allows you to check for equality to any value from the list, which is specified in parentheses, for example: IN(3181,3185) or IN("Moscow", "St. Petersburg", "Kazan")

Operator Like allows you to use patterns and template symbols when searching for data, for example: Like "Mikhailov" or Like "M*"

It is allowed to use wildcard characters *, ?, #, [list], [!list] in literals.

? – the pattern position can contain any symbol: (0 – 9), (Aa – Zz), (Aa – Zaya);

* - any number of characters;

# - position can contain any number;

[list] – the position can contain any character from the list;

[!list] – the position can contain any character except characters from the list.

When writing, the template is enclosed in double quotes.

Like “A*” – in the field Surname all last names starting with A.

Like “*/1/99” – in the field date– all records for January 1999.

Like “*” – in the field Name– any name starting with the specified letters.

Requests - actions

Executing a query - action changes the contents of the database. You should be careful when executing such queries, as careless use of these queries can lead to irreversible loss of information in the database. Therefore, Access automatically marks requests-actions in the database window with the “!” symbol.

When you create a query, Access creates a select query by default. If necessary, you can use query designer commands to specify a different query type.

There are 4 types of change requests:

Request to add;

Request for update;

Removal request;

Request to create a table.

Request to add allows you to add records to the specified table, not only the current database, but also any other database. The structure of the query table record does not necessarily have to match the structure of the table to which records will be added. For example, a query record may have fewer fields if the fields in the receiving table are not required to be filled out. Field type mismatch is allowed if it is possible to convert the data type of one field to the data type of another field.

To create a request, you must complete the following steps:

Create a selection query and debug it (add tables whose field values ​​will be used to add records);

Cancel the Display property for query fields;

Execute the REQUEST/ADD command to convert to an add request. In this case, the Addition line appears in the request form. Next, you need to include in the request form the fields whose data will be added to the receiving table. You can also enter conditions for selecting records to add.

Specify the name of the table where records will be added;

Execute the REQUEST/Run command.

If the receiving table contains a key field, then the added records must have the same key field (according to the database integrity conditions).

The technology for creating other types of requests - actions is similar.

Request for update allows you to change the group of records selected based on the selection criteria. In the update request, you can specify one or more fields by making the necessary settings in the line Update. For an updated field to a row Update you must enter a value or expression that defines a new value. Once completed, a dialog box opens indicating the number of records updated.

Removal request allows you to delete records from one or more tables at the same time. A delete request deletes entire table records that meet the selection criteria, so if you want to delete the values ​​of individual fields in a record, you must create an update request. As this query runs, Access displays the data that will be deleted. In order to be able to view all the fields of the records to be deleted, you should drag the “*” symbol from the first line of the list of fields of the table whose records you want to delete with the mouse into the first line of the request form, into the first free column. In this case, the name of the table will appear in this column in the Field line, and the From value will appear in the line named Delete.

Query to create a table creates a new table based on a dynamic data set. The new table retains the names, data types, and field sizes as they were in the underlying query tables. Other field properties are not inherited.

Forms

The form is a convenient tool for viewing the database, as well as for entering data and correcting it. A form typically displays the fields of a single row of a table or query. However, a form can display data from multiple related tables or queries. The use of forms makes it possible to simplify data entry into the database and reduce the number of input errors. For this purpose the form is supplied with:

Input formats;

Conditions for checking entered data;

Input masks for entering standardized information;

Explanatory text;

Grouping of data, bringing its appearance closer to a paper form.

When designing a form, you can use text strings, pictures and lines, buttons, list boxes, etc. The form is designed using the element panel.

The form can be placed on one screen or multiple screen pages.

The form may or may not allow you to adjust certain data or enter new entries altogether.

You can work with the form in 3 modes:

In design mode;

In form mode;

In table mode.

The form can be printed. Forms have numerous properties that can be customized to make them easier to work with and provide a design to suit every taste.

When designing a form, it is determined from which tables and queries data should be displayed, which fields should be presented in the form, whether calculated fields are needed, which graphic elements should be used to design it - lines, pictures, explanatory text.

You can create a form either using a wizard or in design mode.

Types of forms

You can create the following types of forms in Access:

Column form or full screen form;

Tape form;

Tabular form;

Main/subform;

Pivot table;

Form - diagram.

Form to Column is a set of input fields arranged in a certain way with their corresponding labels and controls. The form allows you to display the fields of only one record on the screen.

Tape form used to display fields from multiple records. The fields are not necessarily arranged in a table, but a column is assigned to one field, and the field labels are arranged as column headings.

The tabular form displays data in table mode.

Main/sub form is a combination of column and tabular forms. It makes sense to create it when working with related tables that have a one-to-many relationship.

Pivot table form performed by the Excel PivotTable Wizard, based on Access tables and queries (the PivotTable Wizard is an object embedded in Access; Excel must be installed to use it in Access). A pivot table is a crosstabulation of data in which the summary data is located at the intersection of rows and columns with the current parameter values.

Form with diagram. In Access, you can insert a chart created by Microsoft Graph into a form. Graph is an OLE embeddable application and can be launched from Access. You can work with an embedded chart in the same way as you would with any OLE object.

Designing forms

When creating a new form, a dialog box appears New form, in which you should select:

Method for creating a form;

Data source (from the list).

Access offers the following ways to create a form:

1. With application Autoforms. AutoForm allows you to create three standard types of forms: column, ribbon, table. In this case, all fields of the data source are inserted into the form.

2. Using the Form Wizard. During the dialogue with the user, the wizard creates a form of one of three standard types. In this case, user-selected fields from the data source are inserted into the form.

3. Using the form designer. The form is designed by the user in the form designer window.

The following technology is convenient when creating a new form: the form is created using an autoform or the form wizard, and then modified in design mode.

The source of the form data is one or more related tables and/or queries.

Form structure

The form consists of five main sections:

1. Form title. The contents of the form title area appear at the top of the form window.

2. Header. The contents of the header area appear after the header at the top of the screen on each form page (if the form is multi-page). Typically, the header area contains the table header (column headings).

3. Data area. The data area contains fields in which data is displayed.

4. Footer. The contents of the footer area (date, page no., etc.) are displayed on each screen page at the bottom of the form.

5. Form note. The contents of this area appear at the bottom of the last screen page of the form.

The form may contain all sections or only some of them.

Form properties

Like any Access object, a form has properties. The values ​​of these properties determine the appearance of the form. The "Properties" window of the form can be called, for example, by right-clicking on the black square at the intersection of the rulers and selecting the command from the context menu PROPERTIES.

The properties window of the selected object contains the following tabs:

Layout – properties that specify the layout of the form;

Data – properties that define the data source, data type, format, etc.;

Events – a list of events associated with the object;

All – a list of all properties.

Basic properties of the form:

Signature(this property is located on the LAYOUT tab) – sets the name of the form, which is displayed in the title bar in the form window.

Default mode yu – determines the mode of opening the form (simple form, ribbon, table).

Acceptable modes– the property specifies whether it is possible to switch from table mode to form mode and back using VIEW menu commands. The property can take the following values:

everything is possible;

table – not possible, only viewing in table mode is possible;

form – not possible, only viewing in form mode is possible.

Allow change determines whether data can be changed through the form, i.e. sets the read-only status.

Allow deletion determines whether the user can delete data through the form.

Allow adding determines whether the user can add records through the form.

Data input defines the form opening mode. Can take the values ​​"Yes" (the form opens only to add new records) and "No" (existing records are displayed in the form).

Blocking records defines how to block a record and how to implement it when two users try to change the same record.

The following properties determine whether the following elements are displayed in the form window:

Scroll bars;

Window menu button;

Window size button;

Window close button;

Window border type;

Contextual help button.

Record number field determines whether buttons for moving through records will be displayed in the form window.

Form controls

Control element refer to any form or report object that is used to display data on the screen, design, or execute macro commands. Controls can be bound, calculated, or free.

Associated (attached)) the control is attached to a field in the underlying table or query. When you enter a value in the associated control, the table field of the current record is automatically updated. The table field is the data source of the associated control.

Computable the control is created based on expressions. Expressions can use table or query field data, data from another form or report control, and functions.

Available controls are designed to display data, lines, rectangles, and pictures on the screen. Free controls are also called variables or memory variables.

All controls can be added to a form or report using the Controls toolbar, which appears when you work with the form or report.

The main controls are:

Inscription– an element designed to display text. The inscription may consist of one or several lines. Is a free element. There are inscriptions that are free and attached to another element (signatures).

Free text is used to set headings and comments. Created by the "Inscription" button on the toolbar.

The signature is created simultaneously with the creation of the element to which it is attached. The signature is used in conjunction with fields, checkboxes, radio buttons, and lists.

Fields– these are elements intended for displaying data or for entering data. Fields can be attached or free. The contents of free fields are not saved anywhere.

A field is the main control element when working with databases, as it allows you to display and edit data from database tables.

Adding a free field to the form is done using the "Field" button on the toolbar. Adding an attached field (linked to a table field) is done in design mode as follows:

In the "Form Designer" panel, select the "Field List" button;

From the displayed list of base table fields, select the desired field and drag it into the form data area. You can drag one or a selected group of fields.

You can enter calculated fields into the form. The calculated field is a free field. To create it you need to select the button Field in the elements panel and paste it where you want the form, and then enter the expression directly into the element Floor" or as a property value Data. In a calculated field, the expression must begin with an "=" sign. Expressions can be typed manually or generated using the expression builder.

Control elements Switches, Switches, Checkboxes. The operating principle of these controls is exactly the same; they differ only in appearance.

Elements are used to display boolean data and return a value (-1) to their associated table field if the button is true, and 0 otherwise.

You can enter a default value to display a specified state. if this value is not set, the element will be in the Null state, which corresponds to the False value.

Group– a control element designed to accommodate multiple switches, radio buttons, or check boxes. Elements within a group function in harmony. The maximum number of elements is 4, and one element can be selected at a time. The group returns a number that corresponds to the number of the selected element.

The Checkbox and Switch controls can be used not only in a group, but also individually.

Checkbox can be associated with a logical field of the underlying table or query. If a checkbox is associated with a logical field in the base table, then the Checked/Unchecked state corresponds to the field values.

The checkbox can be a free element. In this case, it is used in special dialog boxes to accept user input.

The Switch control element can be used in a similar way.

Lists(List and Combo Box) are controls that allow you to select the desired value from several (lists). A list is a collection of rows containing data. Rows can contain one or more columns with headings.

Control element List may be attached (tied) or free. The joined list passes the selected value to the base table/query field. A free list returns a value that is used in another element or to look up a record in the underlying table/query.

Lists are created using a wizard. Most control properties List are generated automatically while the wizard is running. They can then be changed.

Basic properties of lists:

1. Data source type: table / query; list of values; list of fields; VBA function.

2. Data source – indicates the actual data source: for a table / query – the name of the table / query; for a list of values ​​– the values ​​of the list elements separated by “;” (for example, Gender – m;f).

3. Attached column – a field of the base table to which the list is attached.

4. Number of columns – the number of columns in the list. If the data source is a list of values, then the elements from the list are distributed into rows and columns.

5. Column width – specified as a numeric value using “;”. You can hide an attached list column if it contains multiple columns. To do this, you need to set the column width to 0. The value is not displayed when the list is displayed, but when you select a row, the value from the attached column appears in the base table field.

6. Number of Rows – Defines the maximum number of rows displayed in the combo box.

Buttons– a control element used to perform some action. To perform an action, the button property Button press needs to be associated with some macro or event processing procedure.

The button is created by a wizard. The wizard allows you to create 30 different types of buttons and associates them with event procedures. Property Signature defines the text on the button. Property Drawing defines the design on the button.

Page Break, Tab Set- allow you to create multi-page forms. The most convenient use of the element Set of tabs. With its help, a form is created whose pages are combined into one control. Switching between pages is done by selecting a tab.

When adding a control Set of tabs into the form, two tabs are created in it. You can add any controls to a tab except Set of tabs. You cannot move other controls from other parts or pages of the form to the tab; you can only copy them.

You can resize an element Set of tabs, order and names of tabs.

Control element Page break used to specify horizontal breaks between controls on a form. Keys are used to navigate through pages PgUp And PgDn. An element inserted into a form Page break is marked with a small dotted line on the left border of the form.

When creating a multi-page form, it is advisable to add headers and footers to the form.

Subforms are designed to display one form inside another. The primary form is called the main form. A subordinate form is one that is located inside the main one.

The subform is most convenient for displaying tables or queries that have a one-to-many relationship. In this case, the main form can only be displayed as a simple form, and the subform is usually displayed in tabular form. The main form can contain any number of subforms, as long as each subform fits within the main form. It is possible to create a subform of two nesting levels

You can create a subform:

By adding an element Subform in the form;

By dragging a form from the database window to another open form;

Master of subordinate forms.

Reports

Report Types

Reports are built based on data from tables and queries. Main types of reports:

- one column report ku (to column) – is a long column of text containing the labels of the fields and their values ​​​​from all records of the table or query;

- multi-column report– created from a report in one column and allows you to display report data in several columns (newspaper type columns);

- tape report– data is arranged in the form of rows and columns (as in a table);

- group/final report– created from a tape report by combining data into groups and calculating the totals;

- postal stickers– a special type of multi-column report designed to print names and addresses in groups;

- subreport report.

Report structure

Main sections of the report:

- report title– printed at the beginning of the report on the title page, containing the title of the report;

- page header– printed at the top of each page; usually contains column headers;

- group header– printed before processing the first record of the group; the header may contain the field by which the grouping is performed;

- data area– each record from the data source is printed;

- group note– printed after processing the last group record; may contain summary data for records included in the group;

- footer– printed at the bottom of each page, may contain, for example, the date the report was printed, the report page number;

- report note– printed at the end of the report after processing all records; it may contain summary data for all records.

Designing a report

You can create a report using a wizard or in design mode. Both methods can be used. Wizards allow you to speed up the process of creating a report, then you can refine it in design mode. Report wizards allow you to create three types of reports: column report (simple), group / summary and post stickers.

Technology for creating a simple report in a column:

1). While on a tab REPORTS press the button CREATE.

2). In the window New report:

Select tool Auto report to column;

Click OK.

Technology for creating a multi-column report:

1). Create a simple columnar report.

2). Select from menu FILE team Page settings. In the dialog box Page settings select tab Columns and set:

In Group Grid Options number of columns that should appear on each page (field Number of columns), line spacing width (field Interval), distance between columns (field Stolbtsov);

In Group Column size column width (field Width) and line height (margin Height);

In Group Column Layout parameters that determine the order in which records are printed: Top down or From left to right.

3). On the tab Page select orientation: Book or Landscape.

When printing a multi-column report, the report title, report footer, header, and footer are displayed at the full width of the report, so you can place controls in these sections in arbitrary locations in design mode. Group titles and notes, as well as the group data area, appear one column wide.

Technology for creating a group/summary report using the Report Wizard

1). While on a tab REPORTS button pressed CREATE.

2). In the window New report:

Select tool Report Wizard;

Select a data source in the form of a table or query;

Click OK.

3). In the window Generating reports(first step) you should generate a list of report fields ( Selected fields) from the list Available fields. Fields should be selected in the order that they should appear in the report. If the report is based on data from several tables / queries, you should select from the list Table/Query the name of the next source and select fields for it.

4). If the report data source is linked tables, then in the second step in the window Generating reports It is necessary to determine how the data will be grouped.

5). In the next step, you can set your own method for grouping data. To do this, from the left list, select the name of the field by which you want to group the data. Having set the data grouping, you can use the button Grouping set grouping intervals.

For numeric fields, you can set grouping by tens, fifty, hundreds, etc. For text fields, you can group by first letter, by first two, by first three, by first four and by first five letters. If no special grouping interval is required, from the list Grouping intervals value should be selected Ordinary.

6). In the third step, you can set the sorting order of records within the group and, by clicking on the button Results, open a window in which you can specify which final operations need to be performed with the numeric fields of those records that are included in the group; determine what will be displayed in the report: data and totals or just totals (the data section is hidden).

7). In the fourth step, you can select one of the standard report layouts.

8). The fifth step is one of the standard styles. Any standard style can be customized, or you can create your own style. This is what the command is for FORMAT / Autoformat.

9). At the last step of the wizard, you should give the report its own name or agree with the name generated by Access, and use the switches to determine further actions - working with the report in design mode or viewing the report.

Technology for creating a group / final report in design mode

1). Create a new report using the command of the same name – button Create on the tab Reports in the database window. Select tool – Constructor, select the data source (table/query), click OK. A blank report layout appears in the designer window.

2). Create a report layout:

2.1). In chapter Heading report place control Inscription. The text of the inscription will be the title of the generated report. If the header section is not in the report layout, you should select the command VIEW/Title| Report Note.

2.2). In chapter Page header place control Signatures to those fields that will be placed in the data area. If you don't have a header in your layout, you should choose VIEW/Headers and footers.

Headers and footers can be placed in a table in a variety of ways. The placement method determines the value of the report property Page header And footer:

All pages– displayed on all pages of the report;

No title– displayed on all pages of the report except the first, where the header is located;

No note– displayed on all report pages except the last one;

No title/note– are displayed on all report pages except the first and last.

You must use the drag-and-drop method to place fields in the data area and their labels in the header section. To place controls in a report you must:

Expand the list of data source fields by clicking the button List of fields toolbars;

Drag the desired field into the report designer window. Move the field label to the header, and the field itself to the data area.

Chapter Data area can contain both attached and calculated fields. Adding calculated fields is the same as adding a form

If the data source (table/query) field being added to the report has a control selected Field, then two elements are added to the report: Inscription (signature) And Field, interconnected. When you select one of them, the other is automatically selected. When you move one element, the other also moves. To drag only one element from a pair (for example, a signature), you need to select the field element, place the mouse pointer on the upper left corner marker (view - a clenched hand with an extended index finger) and drag the element to the desired location.

Moving an element from one section to another (for example, from the data area to the header area) should be done via the clipboard (with the commands Cut And Insert).

In addition to linked fields, you can also add free fields (calculated fields) to the report.

2.3). Align elements and resize elements.

To change the size of elements:

Select all elements with the command EDIT/Select all;

Run command FORMAT/Size/By data size.

Access will size the control to fit the content.

Access also allows you to resize:

- by grid nodes– in this case, each corner of the control element is shifted to the nearest corner of the grid;

- at the highest– increases the height of all elements to the height of the tallest one;

- at the lowest;

- at the widest;

- according to the narrowest.

Alignment is performed on a group of selected command controls FORMAT/Align. For reports that contain data in tabular form, it is best to align data area elements to the top or bottom.

To change the line spacing in the header and footer areas, you need to move the bottom border of the section. To reduce line spacing – as high as possible. However, the property Height The data area must have a value greater than the value of this property of the highest control.

2.4). Determine the order in which data is sorted and grouped in the report. This is done in the window Sorting and grouping, which opens when the command is executed VIEW/WITH sorting and grouping or buttons Sorting and grouping toolbars Report designer.

To determine the order of grouping and sorting records, you must:

From the list Field/Expression select the field by whose values ​​you want to group records. This field will define a group of records;

For the group, specify the sort order: ascending, descending;

Set values ​​for group properties.

A group can have the following properties:

1. group header– determines whether the section will be present in the report Group header. The property can have two values: Yes / No. To section Group header the value of the field by which the grouping is performed can be placed;

2. group note– determines whether the section will be present in the report Group Note (Not really). Chapter Group Note typically used to display summary operations on data from group records;

3. Property Grouping determines how data is grouped. The list of values ​​for this property depends on the data type you are grouping by. For text data, this property has the following meanings:

- by full value– grouping is performed by field value;

- by first characters– grouping is performed by the first n characters in the field value. Property Interval specifies the value of this n.

For numeric data, the property Grouping matter:

- by full value;

Interval – grouping records by values ​​that fall within the specified interval.

Property Interval specifies a range of values ​​in the interval, for example, if a property has a value of 10, then grouping will be carried out for records whose values ​​fall in the interval 0 - 9, 10 - 19, etc.

For date/time type fields, the property Grouping may have the following meanings:

By full value;

By quarter;

By month;

By week;

By days;

By the hour;

By the minute.

4. Property Don't Tear sets or cancels the mandatory printing of a group on one page, i.e. whether to print different elements of the same group (group header, data area, group note) on the same page. The property can have the following values:

- No– cancels the mandatory arrangement of group elements on the page;

- full group– group elements must be printed on one page;

- first data area– the group header is printed on one page if at least one data area record falls on that page.

To repeat the group title on a new page, you need to select the value YES for property Repeating a section in the group header properties window.

2.5). For a multi-page report, determine the layout of the report sections. All sections except headers and footers have the property End of page, using the values ​​of which you can force a new page to start. Property value Don't tear:

- absent– printing of the current section starts on the current page;

- before section– printing of the current section begins on a new page;

- before and after section– printing of the current section and the section following the current one begins on a new page.

Data Access Pages

Data Access Pages are a special type of Web page designed for viewing and working over the Internet or intranet with data stored in Microsoft Access databases or Microsoft SQL Server databases, as well as using data from other sources such as Microsoft Excel.

Unlike other Access objects which are part of a database, i.e. are in a file with the extension .mdb, access pages are saved separately, as HTML files. When you open a data access page in Internet Explorer, Office 2000 tools retrieve the data and Access database and then display it on the page.

Adding a selection condition to a query allows you to select not all records from the table, but only those that meet certain criteria. For example, you might be interested in contacts dating back to December 1999. Let's modify the query by adding an appropriate selection condition.

1. Select the Contact List request icon in the database window (Fig. 17.4).

Rice. 17.4. Access Database Window

2. Click the button Constructor.

3. In the request form, click on the cell Selection condition first column with the right mouse button and select the command from the context menu Build. The Expression Builder window will open.

4. In the left list of the builder, click on the folder Operators.

5. In the middle list, select a category Comparisons.

6. In the right list, double-click Between to add this operator at the bottom of the formula.

7. Click to highlight the first Expression placeholder in the formula field.

8. In the left list of the expression builder, double-click to open the Functions folder.

9. Click the Built-in Functions folder, which contains standard Access functions.

10. In the middle list of the expression builder, click on the item Date Time.

11. In the right list, double-click the DateValue function to replace the Expression placeholder with it.

12. Press the key twice -> , highlighting the "stringexpr" placeholder.

13. Enter the text "1.12.99".

14. Repeating steps 7-13, replace the second Expression placeholder with the expression DateValue ("12/31/99").

You should end up with the formula Between DateValue ("12/1/99") And DateValue ("12/31/99"). It checks the condition for the date to be in the range from December 1 to December 31, 1999, that is, it selects those records whose Date field value refers to December 1999.

Note If you have already mastered the rules for constructing Access expressions, you can not use the builder, but directly enter expressions into the request form.

15. Click on the button OK. The constructed formula will appear in the Selection condition cell of the first column of the request form. If you select records from the same month, the exact contact date may not be relevant. You cannot completely remove this field from the request form, since it is necessary to implement the selection condition. However, any field can be hidden, that is, not included in the query result.

16. Uncheck Output on display the first column of the query (Figure 17.5).

Rice. 17.5. Hiding a field in Access

17. Click a button Launch toolbar, complete the request.

When creating queries, it is important to correctly formulate the conditions for selecting records from the database. The following features are available in MS Access:

· simple sampling criterion;

· exact discrepancy between the values ​​of one field;

· inaccurate match of field values;

· selection by range of values;

· combining criteria of several fields;

· selection condition for the results of final calculations.

Simple sampling criterion . Records are selected based on matching field values. For example, from the field City you need to select the values ​​Minsk. To do this, in the request form in the line Selection condition in the column City value is entered from the keyboard "Minsk".

Exact discrepancy between the values ​​of one field. All records are selected from the database, except those for which a condition is specified. For example, you need to select all records with the field City, except those that have a value in this field Minsk. To do this, in the line Selection conditions in the column City the expression is entered Not "Minsk" or <>"Minsk". Logical operator Not excludes entries with value Minsk, comparison operator<>means "not equal".

Inexact field value match. This condition can be set if the field values ​​are not known. The comparison operator is used for sampling Like(like). Next to the operator, a pattern containing or the exact value is written, for example, Like "Petrov", or including wildcard characters, e.g. Like “Pet*”.

Access allows the following wildcard characters:

? - any one sign;

* - zero or more characters;

#- any one digit;

[list of characters] - any one character in the list of characters;

[!list of characters] - any one character not included in the list.

In addition to the list of characters, square brackets can enclose a range of characters, for example, [B-R]. The [b-rB-R] condition allows you to select both uppercase and uppercase letters.

Given that Like “[BR]*” All surnames that begin with B or R are selected.

Select by range of values. To set a range of values, the following operators are used:

> (more),

>= (not less than, greater than or equal to),

< (less ),

<= (not more than, less than or equal to) (for example, >= 10).

Between ... and ... (serves to check whether it belongs to a range, the upper and lower boundaries of which are connected by the logical AND operator (for example, between 1990 and 1995).

Operators can be used with text, numeric, and date fields.

Combining criteria of one field. If more than one condition is imposed on one field, then conditional expressions can be connected using operators Or (OR) And And(AND).

Combining criteria from multiple fields. A request may contain several selection conditions. In this case, there are two options for selecting records:

a record is selected only if all conditions are met, which corresponds to a logical operation AND. The request is called And-query;

a record is selected when at least one condition is met, which corresponds to a logical operation OR. The request is called OR-query.

When building OR-query Each condition included in the criterion should be located on a separate line. When building I-query Each condition included in the criterion must be located on one line.

IN final queries There are two types of record selection criteria.

The first type eliminates records that do not meet the criteria before performing the final calculations. The second type of criteria is applied to the result of the final calculations.