Create a selection request with a parameter that should display the last names, first names, patronymics of teachers and the disciplines they teach, and set the last name of the teacher as a parameter and run the query for the teacher Grishina. To create a select query with a parameter:

· Create a query for the following table fields Teachers:Last name, first name, patronymic, discipline. Create a request as Simple request similar to how it was done in step 1.

· Give the request a name Disciplines taught. Click the button Ready. A table with the results will appear on the screen.

· Go to design mode by clicking a button or running a command View/Constructor.

· In the line Selection conditions for the field Surname enter the phrase (enter parentheses too): [Enter teacher's last name:].

· Execute the request by clicking the button on the toolbar or running the command Request/Run.

· In the window that appears, enter your last name Grishin and click on the button OK.

· A table will appear on the screen with data about teacher Grishin - his name, patronymic and the discipline taught.

· Save the request by clicking the button or running the command File/Save

Close the request window .

Reports.

A report is a formatted representation of data for output to a printer, screen, or file.

Table based Teachers create a report grouping data by job title. To create a report:

· In the database window Teachers select object Reports and click on the button Create.

· In the window that opens New report select item Report Wizard.

· Click the drop-down icon at the bottom of the window. Select a table from the list that appears Teachers. Click the button OK.

· In the window that appears, select the fields that will be present in the report. In this example, all fields from the table will be present, so click the button. Click the button Further.

· The window that appears contains a list of fields. Select a field Job title. Click the button. So you ask data grouping by position. Click the button Further.

· In the window that appears, select the sort order: first by field Surname, then across the field Name, then across the field Surname.

· Click on the button Results. Summarize Salary by selecting the function sum. OK.

· In the window that appears, select a layout for the report.

· In the window that appears, select the report design style. Click the button Further.

· In the window that appears, enter the name of the report Teachers.

· Click on the button Ready. The generated report will appear on the screen.

· Review the report. Go to report design mode (button) and change the field name sum on TOTAL:. To do this, in design mode, right-click the field Sum. In the context menu that opens, select Properties.

· Select a tab Layout, then property Signature. Delete the word and enter Total:.

· Switch to report viewing mode by clicking the button or selecting a menu command View/Preview.

· View and then close the report.

· Quit working with the MS Access DBMS.

Lesson 3. Relationships between tables.

Types of connections.

3.1.1. Communication 1:1 (one to one).

In a 1:1 (one to one) relationship, each record in the first table corresponds to one record in the second and vice versa.

For example:

The tables are linked by the “Last Name” field; in both tables there must be a unique index or key for this field.

This type of communication is rare. Graphically depicted:

3.1.2. 1:M (one to many) relationship.

One record of the first table may correspond to one or more records of the second, or may not correspond to any records. Each record of the second table necessarily corresponds to one record of the first table.

For example:

The tables are linked by three fields “Facility code”, “Group number” and “Last name of I.O.”, in the first table there must be a unique index or key for these fields, in the second table there must be an index starting with these fields.

This is the main type of communication and occurs most often. Graphically depicted:

The first table is called the parent (ancestor), the second is called the child (descendant).

3.1.3. M:M (many to many) communication.

One record of the first table may correspond to one or more records of the second table, or may not correspond to any records and vice versa.

For example:

Each student takes tests and exams in many subjects. Many students take tests and exams in each subject.

Graphic image:

For example:

3.2. Creating a database with a 1:M relationship.

Creating tables.

Let's create a database “Car sales”, consisting of two tables: “Suppliers” and “Cars”, connected by the 1:M relationship.

14. Launch MS Access: Start/Programs/Microsoft Access.

15. In the dialog box when Access starts, select the option Database Creation - New Database and click OK. In the dialog box New Database File select your folder and set database name Autoshop.mdb.

16. In the Access DBMS window, select the object tables, in the right pane of the window, select the option Create a table in Design view.

17. In table design view in a column Field name Enter your name Brand. In column Data type leave the type Text. In column Description enter a description of the data this field will contain, for example, car model. Go to the form Field properties at the bottom of the window and set the values Field size: 30 characters.

18. Proceeding similarly, set the names for the fields Engine size, Color, specify the data type and properties for these fields, according to the table:

6. Click on the “Body Type” field. Go to the tab Substitution. Select control type List.

7. Select Row source type - List of values. In stock Row source enter: sedan;combi;hatchback .

8. After entering a description of all the fields in the table, specify the key field “body number” by clicking the selection area of ​​the line with the field entry Body number, press the button Key field on the toolbar. After that, in the field selection area Body number the key field sign will appear - key.

9. Save the table structure with the command File/Save As. In the dialog box Preservation set the table name Cars, in field How select an option Table and click OK to save.

10. Close the table designer window. After that, in the database window car shop on the tab tables a new object will appear - a table Cars.

11. Save the table by clicking the button Save on the toolbar and close it.

12. Create a table Suppliers, describing the fields as follows:

13. As key field indicate the field Firm, whose values ​​in the table are unique. Let's close the table Suppliers while maintaining the structure.

Creating a relationship between tables.

Let's establish a relationship between tables Cars And Suppliers. For this:

1. Choose a team Data Schema on the menu Service. After this, an empty window will open Data Schema, and a new menu item will appear in the Access main menu Connections.

2. In the dialog box Adding a table select a tab tables. Selecting from a list of open database tables Auto shop and clicking the button Add, add tables to the data schema window Cars And Suppliers.

3. Close the window Adding a table by clicking the button Close.

4. To establish a connection between two tables, select the field name with primary key (Firm) main tables Suppliers and drag it using the left mouse button onto the field Providersubordinate tables Cars. As soon as you release the left mouse button, a dialog box will appear on the screen Changing connections.

5. To enable the mechanism for maintaining data integrity in linked tables, select the checkbox Ensuring data integrity. Activate the checkbox Ensuring data integrity, and then turn on the switches for cascade modification - updating and deleting related records.

6. Finish creating the connection by clicking the button Create. In the window Data Schema A graphical representation of the established connection will appear. Marks at the end of the communication line mean that one table entry Suppliers may have as much as you like related records in the table Cars.

The usual way to develop a query with a parameter is Access– create parameters and set them in selection conditions with square brackets. When running a query, input windows appear sequentially in which the user must enter the required values. In this case, you cannot return to the previous window and correct erroneously entered data. The user must enter values ​​manually, without selecting from a list, which also increases the likelihood of errors.
Let's consider another method in which data will be substituted from the form.
For example, let's take . Let's create a query that will select applicants by specialty and educational institution. The request will be launched from the form. On the “Creation” tab, in the “Forms” section, select “Blank Form”.
Let's add 2 combo boxes and two buttons to the form.
Combo box
At the first step, we select the method for obtaining values:

That is, the object will receive data from another table or query.
Then select the data source (specialties table):

Then select the required fields to place in the field:

The next step is sorting. We will install it by name, but you can also install it by code.
Then hide the key column

Specify a caption for this element. Then we rename it, for example, FieldSpeciality.
In the same way, we create a field with a list of educational institutions, but we include two fields in it - educational institution and city:

Let's rename it to Field Educational Institution.
Let's save the shape.
Then we create a request. In the constructor, we add tables of applicants, educational institutions, specialties and the following fields:

Codes may not be displayed on the screen.
Let's set the parameters in the selection condition. To do this, right-click on the “Selection condition” cell in the required field and select the “Build” context menu item. Then select the values ​​as in the figure:

Click OK.
Substituted value:
[Forms]![Form for selection by specialty and specialty]![FieldSpecialty]
Then we also create a parameter in the educational institution code field, but select a different combo box.
Save the request (“ParamRequest”).
We return to the form and add the “Run request” button.
At the first step of the Query Wizard, select the action: category “Miscellaneous”, action “Run Query”. Then, from the list of requests, select our "ParamRequest". Then choose a caption or image for the button. Save the button.
Add a second button - to exit. Using the wizard, specify the category – “Working with form”, action “Close form”.
Keeping the shape.
After starting the form, select the required fields and run the query:

Query with a parameter (parametric query)

As a rule, queries with a parameter (Fig. 10.34) are created in cases where it is intended to execute this query many times, changing only the selection conditions.

To define a parameter, enter a name or phrase enclosed in square brackets () in the "Selection Condition" line instead of a specific value. Access treats what's inside the square brackets as the parameter name. It is displayed in the dialog window when the request is executed, so it is wise to use a meaningful phrase as the parameter name. In the request you can specify

Rice. 10.34.

several parameters; Moreover, the name of each of them must be unique and informative.

For example, you need to create a parametric query to display processors costing up to a certain amount, their name, price and phone number of the warehouse where they are stored (the parameter is the price of the product).

Cross request

A cross-request is a special type of summary request. It allows you to display calculated values ​​in a spreadsheet-like crosstab.

To build a cross-query, follow these steps:

In design mode, create a selection query.

Change the request type to Cross(tab Working with requests, group Request type) (Fig. 10.35).

Rice. 10.35.

The lines “Group operation” and “Cross table” will appear at the bottom of the request form (Fig. 10.36).

In the Crosstab row, for each query field, select one of four settings: Row Headings, Column Headings. Meaning (displayed in crosstab cells) or Not displayed.

For a cross-query, define a field (possibly several) as row headers. Define one (and only one) field to be used as column headings.

Define one (and only one) value field. This field must be summary (i.e. in the “Group operation” line one of the summary functions must be specified, such as

Rice. 10.36.

example of summation (Sum), determining the average value (Avg) or quantity (Count)).

As an example, consider creating a cross-query that displays the number of products stored in warehouses. In the row headings we indicate the names of the goods, in the column headings - the warehouse numbers (Fig. 10.37).

Rice. 10.37.

Calculated fields in queries

The query can be used to perform calculations and summarize results from source tables.

You can use arithmetic operators and any of Access's built-in functions to create calculated fields.

A calculated field can also contain the result of concatenation (merging) of text field values. To do this, enclose the text in quotation marks and use the "&" symbol as the concatenation operator. For example, you can create a field that contains the result of combining the [Last Name] field and the [First Name] field.

A field whose contents are the result of a calculation based on the contents of other fields is called calculated field. A calculated field exists only in the result table.

Creating a calculated field is carried out by simply entering the expression for calculation in the “Field” cell of the empty column of the request form (Fig. 10.38).

Rice. 10.38.

After executing the query, a new field appears in the resulting table with the name "Expression1" used as the name of the calculated expression.

In query design mode, change the name to "Expression!" to something more meaningful. In the example "Expression!" changed to "PriceWithDiscount".

To enter complex calculations, use the P window expressionist(Fig. 10.39), which is called by the command located on the tab Working with requests(in design mode) in a group Query setup, or the corresponding context menu command.

Rice. 10.39.

At the top of the window there is an empty input area intended for creating an expression; at the bottom there are three lists designed to search for the required fields and functions.

The builder will help you construct the expression correctly. Click the button OK, and the entered expression will be transferred to the request form.

A parameter query is a query that, when executed, displays its own dialog box prompting you to enter data, such as a condition to return records or a value to insert into a field. You can design a query that prompts you to enter multiple pieces of data, such as two dates. Microsoft Access can then return all records that fall within the time interval between these dates.

Queries with parameters work in two ways:

· data selection occurs in a separate dialog box. Such dialog boxes exist in any help system, where a question is entered and the program searches for an answer;

· data selection is carried out directly in the query table in Design mode . The selection criteria are written into the Selection condition line, and only after that the tabular form of the request is displayed on the screen.

1. Request dialog box

Dialog boxes are needed not only for reference. They display only the requested data and hide all other information. This procedure allows you to divide the work into several areas and hide the commercial nature of the information.

Any request begins with creating a simple request, and since there is additional work to be done, it is better to create it in Design mode .

After selecting tables and fields in the same mode, in the Selection Condition line in the field where you want to select, you must specify the selection criteria. These criteria are written in square brackets as a formula:

[enter:Product Code].

After the word “enter” the name of the field is indicated (Fig. 18).

Rice. 18. Creating a request with a parameter

When validating or running a query, the Enter Parameter Value dialog box opens (Figure 19), prompting the user to enter parameters. Which parameter to enter is indicated on the gray background of the window, and the cursor blinks in the text window, waiting for data to be entered from the keyboard.

Rice. 19. Dialog box Enter parameter value

After entering the parameter in the dialog box Enter parameter value click OK, and a table with the selected parameters will appear on the screen (Fig. 20).

Rice. 20. Result of a request with a parameter

When running a query for two or more parameters, the Enter parameter value windows will appear after entering each parameter one after another until all required values ​​are filled in. Only then will the query table open.

2. Selecting data in a query table

Requests from data selection are considered workers. It all starts with a simple request. In Design mode, in the Selection condition line in the column where selection must be made, the selection criteria are indicated. In this case, they are written in quotation marks indicating data from the record or using mathematical symbols (for example, “notebook”,<5)..

Final queries

This type of query performs mathematical summary calculations or statistical calculations.

By doing final request records should be grouped according to some characteristic (product name, date of sale, etc.).

For each group, you can make a final calculation (for example, determine the total number of notebooks or pencils supplied).

The request begins with the preparation of a simple request, that is, the necessary fields are selected to carry out the final operation.

Grouping of data occurs in Design mode by clicking on the (Group Operations) button on the toolbar or executing the menu command View => Group Operations. A new line Group operations will appear at the bottom of the query building window, and the Grouping value will be automatically set in the zone of each field.

In those fields where you need to group data, you must leave the Grouping entry. In this case, the same data will be displayed on the screen in a single entry without repetition. In the remaining fields for which final calculations should be carried out, in the Grouping line you need to expand the list and select one of the final functions (Fig. 21)

Rice. 21. Creating a final query

The functions in the drop-down list have a short designation (Table 11).

Table 11 Functions of group operations.

Element	Result	Field type
sum	The sum of the field values.	"Numerical". "Date Time". "Cash" and "Counter"
Avg	Average of field values.	"Numerical". "Date/Time", "Currency" and "Counter"
Min	Smallest field value.
Max	The largest field value.	“Text”, “Numeric”, “Date/time”, “Currency”
Count	The number of field values, excluding empty values.	“Text”, “Numeric”, “Date/time”.
StDev	Standard deviation from the average field value.
Var	Dispersion of field values.	Numeric, Date/Time, Currency and Counter
First	Value of the first field entry	"Text", "Numeric", "Date/Time", "Currency"
Last	Last field entry value	"Text", "Numeric", "Date/Time", "Currency"

A query in Access is an object that is stored in a database file and can be repeated many times. All the queries that we have demonstrated so far contained specific values ​​for dates, titles, names, etc. If you want to repeat such a query with other values ​​in the selection conditions, you need to open it in Design mode, change the condition and run it. To avoid having to do these operations multiple times, you can create a query with parameters. When executing such a request, a dialog box appears Enter parameter value(Enter Parameter Value), in which the user can enter a specific value and then get the desired result.

Let's show how to create queries with parameters using the example of the "Sorted list of products" query that we created earlier. Now, using this query, we will try to select products supplied by a specific supplier. For this:

1. Open this query in Design mode.

2. To define a query parameter, enter in the line Selection condition(Criteria) for the CompanyName column, replace the specific value with a word or phrase and enclose it in square brackets, for example [Supplier:]. This phrase will be presented as a prompt in the dialog box when you run the request.

3. If you want Access to validate data entered as a query parameter, you must specify a data type for the parameter. Usually this is not necessary when working with text fields, because by default the parameter is assigned a data type Text(Text). If the data in the request field is dates or numbers, it is recommended to specify the data type for the parameter. To do this, right-click on the free field at the top of the request and select the command from the context menu Options(Parameters) or execute the menu command Request, Parameters(Query, Parameters). A dialog box appears Request parameters(Query Parameters).

4. To column Parameter(Parameter) you need to enter the name of the parameter exactly as it is defined in the request form (the easiest way to do this is by copying via the clipboard), but you don’t have to enter square brackets. In column Data type(Data Type) select the required data type from the drop-down list. Click the button OK.

5. Click the button launch(Run) on the toolbar to execute the request. When executing a query, a dialog box appears Enter parameter value(Input Parameter Value) (Fig. 4.32), in which you need to enter a value, for example, Tokyo Traders. The result of the query execution is shown in Fig. 4.33. It includes only those goods that are supplied by this supplier.

You can enter multiple parameters in one request. When executing such a request, dialog boxes will be displayed in turn for each of the parameters. Enter parameter value in the order in which the parameters are listed on the request form.

61. Reports: purpose, creation tools, use (MS Access).

The report is used to display summary data from tables and queries in an easy-to-view form. The reports provide special tools for grouping data and entering special design elements typical of printed documents (headers, page numbers, service information about the time of creation and artist). The result of the report can be viewed on the screen or printed.

The source of the report records is: fields in base tables and queries. The report gets data from the underlying record source. Other data such as title, date, and page numbers are stored in the report layout. By being able to control the size and appearance of all report elements, the user can display the information in the way they want.

A tool is used for automatic design Report Wizard.

Let's look at this with an example: create a report with information about contracts (code, contractor, end date, amount), grouped by customer.

On the tab Creation in the panel Reports select command button Report Wizard.

In the window Generating reports first select table Treaties and fields CODE, ZAK, ISP. WINDOW_DATE, AMOUNT. Press the button Further.

Set grouping by field ZAK. Press the button Further. Specify the fields by which data should be sorted. In our example, the field WINDOW_DATE. Press the button Further.

Enter report name – Contract report. Press the button Ready.

To create reports correctly, you need to understand the purpose of each section. For example, the selection of the section in which a calculated control is placed determines how the result is calculated. Creating and changing the report layout is carried out based on the structure of the report.

The purpose of each section:

The title of the report usually includes the company logo, the title of the report, and the date. The header appears before the header only once at the beginning of the report;

A header appears at the top of each page and is used when you want the report title and other general information to be repeated on each page;

The Report Headler is used when grouping report records to display the group name and display the fields by which the grouping is performed once. Displayed before each new group of entries. For example, if the report is grouped by customers, in the group header you can indicate the name of the buyer, as well as address, phone number and other details. Up to 10 levels of grouping of displayed records are allowed;

The data area (Detail) displays records from the data source that make up the main content of the report;

Group Note (Footer) is used to display totals and other group summary information at the end of each group of records. If you place a calculated control that uses the Sum aggregation function in a group note, the sum will be calculated for the current group;

A footer is used to number pages and display other information at the bottom of each page;

A report note displays totals and other summary information for the entire report once at the end of the report. If you place a calculated control that uses the Sum aggregation function in a report comment, the sum is calculated for the entire report.

Question 62. Concept and types of computer networks.

A network is a collection of computers that allows them to share resources. Resources include disk drives, files (databases), printers, and communications equipment. Computers connected to a network are called nodes. If the nodes are located in close proximity to each other, then the network is called a local network or LAN (LAN-Local Area Network), if the nodes are dispersed much wider, then the network is called a wide area network or WAN (WAN-Wide Area Network).

Global networks are formed when local networks are connected to each other. When two or more networks are connected to each other, an interconnection occurs and a global computer network is formed.

If a computer is constantly part of a network, it is said to have a persistent connection.

If the computer is connected to the network only while it is working, then it has a dial-up connection.

The connection can be made from afar, for example, by telephone. In this case, the connection is called a remote access connection.

There are 2 types of local networks: peer-to-peer networks and client/server networks. Each of them is supported by Windows.

A peer-to-peer network is the simplest and cheapest option for connecting several computers. All computers in a peer-to-peer network have equal rights. There is no computer here, called a server, which serves for centralized storage of information, administration of user rights and network resources.

Client/server networks. A dedicated server computer is used that hosts shared files and provides printing and other services to many users.