To create a select query in mode Constructor for the field that is supposed to be used as a parameter, enter in the line cell Selection condition expression with the invitation text enclosed in square brackets [Invite Text].

Note: To make working with text parameters easier, you can build an expression that allows you to enter a query parameter incompletely:

Like“*” & [Question text] & “*”, in this case it will be possible to indicate an incomplete name.

Example 10. It is necessary to obtain information about products in a specific department. Let's build the following query (Fig. 25):

Rice. 25. Query constructor with parameters

Now, when running a request, the user will be asked a question (Fig. 26):

Rice. 26. Request work with a parameter

As a result, the request will display information related only to the specific department entered.

Exercises

1. Create a request for Supplies for a period, select information about what, when and in what quantity was supplied to the supermarket during a specified time interval (i.e., when opening a request, the start and end dates of the period are indicated).

Indication: For the field Date of delivery from the table Supplies in line Selection condition set the expression:

Between[Enter start date] And[Enter end date].

2. Create a request Department Employees, with which you can find out who works in the department specifically specified by the user.

3. What was supplied to a specific (user-specified) department over the past month?

4. Create a request Departments on floor, which will allow you to get a list of departments located on it using the specified floor number.

5. Create a request for Products within the specified amount, which will allow you to receive a corresponding list of products based on the specified upper price limit (i.e., the user, by entering an upper price limit (for example, 5,000 rubles), receives a list of goods that cost less).

6. Create a query Supplier and his goods, which for the specified supplier produces a list of goods supplied by him.

7. Create a query that determines who was the earliest to be hired for a specific department.

8. Determine the amount of products supplied from the specifically specified supplier over the last month.

Control questions

1. Define the condition Query with parameter.

2. How to build a condition Query with parameter?

3. What condition must be formulated for entering an incomplete text parameter?

Topic 7. Functions in queries

Access uses a number of built-in functions that will help you set selection conditions for date and time and allow you to formulate more complex conditions for selecting information using Boolean functions.

To use existing features, you must open a window Expression Builder, select folder Functions, in it - a folder Built-in functions. After that, you need to specify the category of the function and the function itself, and then specify the data processed by the function.

Examples of basic functions in Access:

Day([Table name].[Field name]) – returns the value of the day of the month in the range from 1 to 31;

Month(date) – returns the month of the year in the range from 1 to 12;

Year(date) – Returns the year value in the range from 100 to 9999.

Example 11. Create a request that allows you to obtain complete information about the delivered products by the specified month number from 1 to 12 (Fig. 27).

Rice. 27. Functions in queries

Example 12. Determine the length of service of each employee.

To do this, we will use the expression builder (Fig. 28).

Function DateDiff allows you to get the interval between dates. Moreover, in order for the interval to be provided in days, years or other time units, it is necessary to indicate the response format: “yyyy” – interval in years; “m” – difference in months; “d” – in days; “w” – weeks.

The request form will look as shown in Fig. 29.

When constructing queries that compare data across periods, you often have to refer to the beginning of the month, quarter, etc. (Table 7).

Rice. 28. Using the function DateDiff

Rice. 29. Request form with a given function

Table 7

Useful functions for processing dates

Records without subordinates

Using the Records Without Subordinates query method, you can find those records that do not appear in the subordinate table. To build such a query you need:

Select an object in the database window Requests, press the button Create;

In the dialog box that opens, select Records without subordinates;

In the window Search records, having no subordinates, build a corresponding query.

Exercises

Implement the following queries in the Supermarket database:

1. Create a request Delivery month, in which of the total DatesDelivery the serial number of the month will be highlighted (based on the tables Goods And Supplies ). Function category Date Time, function Month.

Month number

Month number: Month([Delivery]![Delivery Date]).

2. Create a request Year of delivery (similar to the previous task). Function category Date Time, function Year.

3. In the Excise Tax Calculation request, determine the excise tax amount (20%) if the product is excisable, otherwise set the value to 0 (based on the table Goods ). Function category Control, function IIf.

Hint: Add a new field Excise tax amount, in which specify the expression:

Excise tax amount: IIf(Goods!Excise=True;Goods!Price*0.2;0).

4. In the request Deliveries on weekends, based on tables Goods And Supplies , display information about those deliveries that were made on Saturday or Sunday. Function category Date Time, function WeekDay.

Hint: Add a new field Day of the week, in which to set the expression:

Day of the week: Weekday([Delivery]![Delivery Date]; 2).

In line Condition selection set condition 6 Or 7.

5. Create a query with the Data by month parameter, which allows you to use the entered name (in text) of the month to obtain information about what, when and in what quantity was supplied to the supermarket based on the tables Goods And Supplies .

Hint: Add a new field Name of the month, in which to set the expression:

Month name: MonthName(Month([Delivery]![Delivery Date])).

In line Selection condition set parameter condition:

Like“*” & [Enter month name] & “*”.

6. Create a query Age of employees (based on the table Employees ). Function category Date Time, function DateDiff.

7. How many months have passed since the first delivery?

8. In the list of departments of the supermarket, create a field in which there are clarifications regarding the presence of excisable goods in it (i.e., the text in the column is written: “THERE IS excisable goods” or “NO excisable goods”).

Hint: Add a new field with a condition in the request Excise goods:

IIf(Sum([Goods]![Excise tax]=True)<>0;“IS”;“NO”).

Enable group operations, in line Group operation choose Expression.

9. Organize a request Comparison of data for 2006 and 2007, in which the total costs for all deliveries in 2006 and 2007 will be displayed separately in two lines.

10. Organize a request Comparison of data for any two months, in which the total expenses will be displayed for any two names of months that are entered from the keyboard in the form of text (for example, Jan., Feb., etc.).

11. Organize a request for supplies of the current quarter (using the functions DateDiff, Now() to calculate the difference between dates expressed in months “m” and other conditions).

12. Create a query that determines which products are included in the table Goods , were never supplied.

13. Create a query that determines which of the registered suppliers have not made any deliveries.

14. Create a query that determines whether the database contains names of departments in which no employees are listed.

Control questions

1. What built-in functions do you know?

2. In what ways can you add a function to Constructor request?

3. Describe a method for creating write requests without subordinates.

4. Which function can be used to determine the difference between dates?

5. How to determine the current date in a request?

Topic 8. Cross requests

Using a cross-query, you can more clearly present the data of final queries that provide for grouping according to several criteria (two, in particular).

In this case, field values ​​based on the first grouping characteristic can become row headers, and based on the second, column headings.

To transform the final selection query, you need to go to its designer mode and run the commands Request → Cross. After this, in the request form the line Output on display will be replaced with the string Cross table. In this line you need to specify how this field will be used in the crosstab: as a row header, field header, or value header.

A cross-over query displays the results of statistical calculations (such as sums, number of records, and averages) performed on data from a single table field. These results are grouped into two sets of data, one in the first column of the table and the other in the top row.

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 on 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 Creating a Database – New Database and click OK. In the dialog box New Database File select your folder and set the database name Autoshop.mdb.

16. In the Access DBMS window, select the object Tables, in the right area of ​​the window, select the option Creating a table in design mode.

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

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.

Queries are a special database management tool that can be used to collect the necessary information based on certain criteria. A query with a parameter in Access is performed in cases where selection needs to be done repeatedly, changing only some conditions.

Unlike a custom query, a parametric query allows you to create and save the main form, changing only the conditions in it immediately before launching.

Step-by-step instruction

If you need to solve a problem in which the query criteria are not known in advance or can change, you should use a query with a parameter in Access.

To set the parameter input instead of a specific value, enter a name or phrase enclosed in square brackets in the “Selection Condition” section. Only after this, Access will consider the information and display it as a comment to the parameters. To use several mutable criteria, you should come up with unique names for them.

1. As an example, create a query that displays a list of teachers working in a specific department. It is this criterion that will be changeable, therefore in the line “Selection conditions” you must enter the value =[Enter the name of the department].
2. Save the resulting filter under the name “Selection of teachers by departments.”
3. Now, after launching, you will see a dialog box in which you will need to enter the required name, after which a list of teachers who are enrolled in this department will appear.

A query with a parameter in Access can be used in any type of selection: summary, cross-section, or in an action 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.