Typically, data is easier to analyze if it is divided into groups. For example, a report that groups sales by region can help identify trends that might otherwise go unnoticed. Additionally, by placing totals (such as sums or averages) at the end of each group in your report, you can replace a lot of the calculator's work.

Access makes working on reports with groupings easy. You can create a simple report with grouping using the Report Wizard, add grouping or sorting to an already created report, or change grouping or sorting options that have already been specified.

Note: This article does not apply to Access web applications, a new type of database that is created in Access and published on the Internet.

In this article

Create a summary report with grouping or sorting

Even if this is your first time creating grouping reports, you can quickly create a simple report by following these steps:

Creating a grouping report using the Report Wizard

The Report Wizard asks you questions and creates a report based on your answers. One of them mentions the fields by which you want to group in the report. After you create a report, you can use it as is or modify it to suit your needs. Before running the report wizard, decide on the choice of data source.

Launching the Report Wizard

Grouping records using the Report Wizard

Grouping allows you to organize records by group (for example, by region or salesperson). Nested groups make it easy to define relationships between groups and quickly find the data you need. With grouping, you can also get summary data such as totals or percentages.

When you include multiple tables in a report, the wizard checks the relationships between them and determines how you can view the data.

Sorting and summarizing records

You can sort records in ascending or descending order by 1 to 4 fields.

Using the navigation buttons at the bottom of the viewport, you can scroll through the pages of the report or jump to any page in the report. Press one of the navigation buttons or enter the page number you want in the page number input field, and then press ENTER.

In Preview mode, you can zoom in to see details or zoom out to see where the data is on the page. Click once when the mouse cursor points to the report. To cancel the magnification effect, click again. You can also use the zoom control in the status bar.

Add or change grouping and sorting in an existing report

If you already have a report and you need to add sorting or grouping to it, or you need to change the sorting or grouping conditions in it, this section will help you with that.

Add grouping, sorting, and totals

You can perform sorting, grouping, and summarizing operations by right-clicking fields in Layout view and then selecting the operation you want from the context menu. To switch to Layout view, right-click the report in the navigation pane and select Layout mode.

Note: Although the instructions in this section do not directly talk about the area, we recommend opening it and monitoring changes in it as you work. You'll gain a better understanding of what Access does, and once you've mastered the scope Grouping, sorting and totals, you can use it to further adjust the report. To display the panel Grouping, sorting and totals:

on the tab Constructor in the group Grouping and totals click Grouping and sorting.

Sort by one field

Right-click any value in the field that you want to sort by.

Select the desired sorting option from the context menu. For example, to sort a text field in ascending order, click Sorting from A to Z. To sort a number field in descending order, click Sort in descending order.

Access sorts the report as you specify. If the area Grouping, sorting and totals is already open, you may notice that a new line has been added to the field Sorting.

Sorting by multiple fields

Note: When you apply sorting by clicking a field in Layout view, you can sort only one field at a time. Applying a sort on another field removes the sort on the first field. This is different from the way you sort in forms, where you can set a variety of sort orders by clicking each field in turn and selecting the sort order you want. To create sort levels for multiple fields, see .

Group by field

Right-click any value in the field that you want to group by.

From the context menu, select Grouping.

Access adds a grouping level and creates a group header. If the area Grouping, sorting and totals is already open, it will be visible that a new line has been added to the field Grouping.

Add a total to a field

This option allows you to calculate sum, average, count, or other statistical fields. The total value is added to the end of the report, and group totals are added to the report groups.

Right-click any value in the field for which you want to calculate the total.

Click Bottom line.

Select the operation to perform: Sum, Average, Number of records(to count all records) Number of values(to count only records with the value of this field), Maximum, Minimum, Standard Deviation or Dispersion.

Access will add a control to the report header and footer, such as calculated text, that summarizes the total. If your report contains grouping levels, Access will add group headers and footers (if they don't exist) and place a total value in each header and footer.

Note: You can also add totals by clicking on the field you want to calculate them on and on the tab Constructor in the group Grouping and totals clicking Results.

Add grouping, sorting, and totals using the Grouping, Sort, and Totaling pane

Working with an area Grouping, sorting and totals provides maximum flexibility when you need to add or change groups, sort orders, or total options in a report. Layout mode is the easiest to use because it makes it much easier to see how your changes affect the display of your data.

Display the Sort, Group, and Total pane

On the tab Constructor in the group Grouping and totals click Grouping and sorting.

Access displays the area Grouping, sorting and totals.

To add a grouping or sorting level, click Add grouping or Add sorting.

To the region Grouping, sorting and totals a new row will be added and a list of available fields will be displayed.

You can click one of these field names or expression below the list of fields to enter an expression. Once you click a field or enter an expression, Access adds a grouping level to the report. Layout view will immediately display the sort or group order.

For more information about creating and using expressions, see Create expressions.

Notes:

After specifying several levels of sorting or grouping, you may need to move down the area Sorting, grouping and totals to see the buttons Add grouping And Add sorting.

You can assign up to ten levels of sorting and grouping in a report.

Changing Grouping Options

Each level of grouping or sorting contains a set of parameters that can be used to obtain the desired results.

Sort order. You can change the sort order by clicking the appropriate drop-down list and selecting the desired option.

Grouping interval. This setting determines how records are grouped. For example, text fields can be grouped together by the first character (if they start with "A", "B", etc.). Date fields can be grouped by day, week, month, quarter, or enter your own interval.

Results. To add totals, click this option. You can add totals to multiple fields, and you can calculate multiple types of totals for a single field.

Total by field and select the field for which you want to calculate the totals.

Click the drop-down arrow Type and select the calculation method.

Select Show grand total to add a grand total to the end of the report (its footer).

Select Show group totals and grand total to add a control to the groups footer that calculates the percentage of the grand total for each group.

Select Show in group header or Show in group footer to display the grand total in the desired location.

Once you have selected all the options for a field, you can repeat the process by selecting another field from the drop-down list Total by field, or click outside the popup window Results to close it.

Name. Allows you to change the title of the field from which the total value is calculated. Used for the column header and for summary fields in headers and footers.

To add or change a title:

click the blue text after the caption with title;

a dialog box will appear Scale;

enter a new title in the dialog box and then click OK.

With/without header section. With this setting, you will be able to add or remove the header section that appears before each group. When you add a header section, Access places a grouping field in the header. Before you delete a header section that contains controls other than a grouping field, Access asks you for confirmation.

With/without note section. Use this option to add or remove a header and footer section after each group. Before you remove a header or footer section that contains controls, Access will ask you for confirmation.

A seamless representation of the group. This setting determines how the groups are laid out on the page when the report is printed. You may want to position the groups as close together as possible to make them easy to see on the page. But this usually increases paper waste when printing the report because most pages will have blank space at the bottom.

Don't keep the group on one page. Use this option if you do not care about the placement of groups on page breaks. For example, 10 elements of a group of 30 elements might be located at the bottom of one page and the remaining 20 at the top of the next page.

Keep the group on the same page. This setting helps minimize the number of page breaks in a group. If a group doesn't fit in the remaining space on one page, Access leaves that space empty and places the group on the next page. Large groups can still span multiple pages, but this option minimizes such occurrences.

Keep the title and first entry on the same page. Ensures that the group title does not print separately from the group itself at the bottom of the page. If Access determines that there is not enough space to print at least one line after the heading, the group will print from the next page.

Changing the priority of grouping and sorting levels

To change the priority, click a line in the Grouping, sorting and totals, and then an up or down arrow to the right of the line.

Removing grouping and sorting levels

To delete a level, in the area Grouping, sorting and totals select the line you want to delete and then press the DELETE key or button Delete to the right of the line. When you delete a grouping level, if the group header or footer contained a grouping field, Access moves it to the detail section of the report. All other controls are removed.

Create a summary report (without record details)

If you want to show only the totals (data in the header and footer rows), on the tab Constructor in the group Grouping and totals click Hide details. This will hide the records of the next lower grouping level, and the resulting data will be displayed more compactly. Even though the entries are hidden, the controls in the hidden section are not removed. Click Hide details again to return the detail rows to the report.

The query allows you to summarize data for data belonging to the same groups: calculate their number, sum, average, maximum and minimum value.

Exercise: create a query that will calculate the total number of items and the total volume of goods received for each invoice.

Let's look at the table data " Receipt of goods"(Fig. 13). The table has the same column values Invoice number can be repeated many times (depending on how many goods were received according to the invoice). It is necessary to create a query that will sum up the quantity of goods for invoices with the same number.

For a better understanding of what the result should be in the query in Fig. 13, on the right, with curly brackets, the records for which the invoice numbers match are highlighted and the total number of items and the total volume are calculated.

Rice. 13. Table "Receipt of goods"

Operating procedure:

1. B Navigation window is selected Object category - Object type, and in the section Filter by group switch installed Requests. On the tape in the section Creation in the group Requests button pressed Query Builder.

2. In the dialog box Adding tables, the tables are selected, the data from which needs to be displayed in the query (" Receipt of goods") and press the button Add.

3. The query designer window will appear on the screen, consisting of two parts: the upper part displays table layouts, and the lower part contains a section for defining query parameters.

The fields whose values ​​should be displayed as a result of executing the request are added to the request parameters section (Fig. 14). (You can add a field to a query by double-clicking on its name in the corresponding table located at the top of the query window).

5. To implement the request in Design mode, add an additional line Group operations (Fig. 14) by clicking on the button on the toolbar.

Under the field Invoice No. in line Group Operations team is selected Grouping(invoices with the same number are grouped), under the field Product code in line Group Operations team is selected Count(quantity), and under the field Quantity - team Sum( quantity is summed up). The operations used in the grouping query are described in Table 2.

The result of the request is shown in Fig. 15. (compare the result with the data in Fig. 13).

Table 2. Group operations

Operation name	Meaning
	Summation
	Average value
	Minimum value
	Maximum value
	Number of elements in a column
	Last element
	First element
Condition	Points to a Boolean expression
Expression	Indicates that the field is calculated

6.1. Calculated fields.

6.2. Create expressions using the Expression Builder.

6.3. Overview of the built-in functions of the MS Access DBMS.

6.4. Final queries.

6.5. Cross requests.

Very often, when creating a set of records, it is necessary to perform calculations on the data (determine the age of the employee, the cost of the order, the percentage of sales, highlight part of the item code, etc., i.e., display information that is not stored in the database) or perform certain operations for direct processing of selected data.

In the QBE DBMS MS Access, such capabilities are provided through calculated fields and group operations.

6.1. Calculated fields

A calculated field is an expression consisting of operators (arithmetic, comparison, logical, concatenation) and operands. The operands can be constants, built-in or user-defined functions and identifiers, for example

Cost: Goods! Price * Quantity * (1-Discount)

Number of Men: Sum(IIf(Gender = "m"; 1; 0))

Full name: Last name &" "& Left(First name;1) &". "& Left(Middle name;1) &"."

Cost, Number of Men and Full Name are names of calculated fields and are displayed in table view in the column header; the colon character acts as a separator between the name of the calculated field and the expression.

If the name of a table or field contains spaces, then its identifier must be enclosed in square brackets in the expression, for example

Cost: Price*[Quantity of goods]

A calculated field is created directly in the QBE form by entering an expression into a cell Field any free column. The results of calculations displayed in the field are not stored in the base table. The calculations are performed again whenever a query is executed, so the results always represent the current contents of the database.

It is not possible to update calculated results manually.

To build complex expressions, the MS Access DBMS includes a utility called Expression Builder.

6.2. Creating expressions using the Expression Builder

The expression builder can be launched by clicking on the key Build on the toolbar Query Builder or by selecting the menu command in the context menu of the QBE request form field Build....

Fig.6.2. Expression Builder Dialog Box
with formed expression

6.3. Overview of built-in DBMS functionsMS Access

The MS Access DBMS contains more than 100 built-in functions (Fig. 6.3) that can be used when creating a calculated field or when setting a selection condition.

	Returns the day of the month from 1 to 31
	Returns the month value from 1 to 12
MonthName(month[; flag])	Returns the name of the month corresponding to the month number: 1 – January, 2 – February, etc. If the value of the flag argument is True, then the function returns the abbreviation of the month: 1 – January, 2 – February, etc.
	Returns a year value between 100 and 9999
Weekday(date[; number])	If number is not specified, returns the day of the week from 1 (Sunday) to 7 (Saturday). If number is 0, returns the day of the week from 1 (Monday) to 7 (Sunday)
	Returns an integer from 0 to 23 representing the hour value
DatePart(interval; date)	Returns a numeric value based on the value of the interval argument: "q" – quarter (from 1 to 4); "m" – month (from 1 to 12); "yyyy" – year (from 100 to 9999); "ww" – week (from 1 to 53); etc. (see function help).
	Returns the current system date

Continuation of Table 6.1

Description

Left(text; n)	Returns the left n characters of the text argument
Right(text; n)	Returns the right n characters of the text argument
Mid(text; start_pos[; n])	Returns n characters starting from the start_pos position of the text argument. If n is not specified, returns all characters up to the end of the string starting from the start_pos position of the text argument.
	Returns the number of characters (string length) in the text argument
LTrim(text)	Returns the string value of the text argument without leading spaces
RTrim(text)	Returns the string value of the text argument without trailing spaces
Trim(text)	Returns the string value of the text argument without leading or trailing spaces
	Returns the string value of the number argument
Format(variable; format)	Returns the value of the variable argument in the format specified by the format argument

6.3. Final queries

When analyzing data, one is often interested not in individual records, but in the total values ​​for groups of data, for example:

Number of transactions with Partners over a certain period of time;

Average sales volume for each month for the previous year.

The final query provides answers to such questions.

To calculate the total values, you must click the button Group Operations on the toolbar Query Builder so that the line appears in the QBE form Group operation(after the table name).

By default, for each field entered in the request form, the value is set to Grouping(results are not summed up).

To summarize, it is necessary to replace the installation Grouping to a specific final function. The MS Access DBMS provides 9 functions (Table 6.2) that ensure the execution of group operations.

Table 6.2

	Purpose
	Returns the sum of a set of values
	Returns the arithmetic mean of a set of values
	Returns the smallest value from a set of values
	Returns the largest value from a set of values
	Returns the number of records in a set of non-Null values
	Returns the first value of a field in a group
	Returns the last value of a field in a group
	Returns the standard deviation of a set of values
	Returns the variance of a set of values

Group operation installation available Expression. This setting is applied when in the expression (line Field) several summary functions are used.

In the drop down list string Group operation installation available Condition. This setting is used when the selection condition is written in the Selection Condition line, but the column (field) data should not participate in the group operation.

To solve more complex statistical problems, the MS Access DBMS provides a special type of query - cross-query.

6.4. Cross requests

Cross request is a special type of group query that reflects the results of statistical calculations based on the value of one table field.

To build a cross-query, three fields of the base source are enough. Based on the repeating values ​​of one field, the names of the row headings of the final (pivot) table are formed (Fig. 6.4). Based on the repeating values ​​of another field, the names of the column headings of the final (pivot) table are formed. The results of statistical processing for the third field are displayed in the cells of the pivot table (values ​​area). An example of a cross-query in design mode is shown in Fig. 6.5, and the results of executing the query are shown in Fig. 6.6.

The row header area allows multiple fields, while other areas can only accommodate one field at a time.

In a cross-request, it is possible to specify selection conditions. Sorting can only be done by fields located in the row header area.

Fig.6.4. Crosstab Layout

Fig.6.5. Generated cross-request in QBE

Fig.6.6. Result of running a query in Table mode

Queries allow you not only to select records from Access tables, but also to calculate various statistical parameters. For example, you can count the total number of contacts and display the dates of the first and last contact with each of the people included in the Contacts table. To build such a query in design mode, follow these steps:

1. In the database window, click on the button Requests.

2. Double click on the icon Creating a Query in Design View.

3. In the dialog window that opens (Fig. 17.6), highlight the Contacts line.

4. Click the button Add Add the selected table to the top pane of the query designer.

5. Highlight an item List and click on the button again Add.

6. Click the button Close close the dialog window. Lists of fields from two tables connected by a link will appear in the designer window.

7. Click the button Group Operations toolbars. An additional line will appear in the request form Group operation, which allows you to perform statistical operations on the values ​​of specific fields.

Rice. 17.6. Adding a table

8. Drag the Last Name field to the cell Field the first column of the constructor.

9. Drag the field into the same cell in the second column Name Contacts tables.

10. Drag the field to the third, fourth and fifth columns of the request form Date List tables (Fig. 17.7).

11. Cell Dropdown Group operation In the third column of the request form, select Min.

12. In the same cell in the fourth column, select Max.

13. In the fifth column, specify the group operation Count. Group operations of the constructed query will process all records of the List table corresponding to a specific person from the Contacts table, and instead of the List table data itself, they will display in the corresponding field of the query result only the value of the value calculated using a certain formula. The available group operations are listed in the table. 17.1.

TABLE 17.1. Group Operations

Name	Function
Condition	The mode for specifying selection conditions for a field, but for which grouping is not performed. Access automatically makes this field hidden
Expression	A calculated field whose value is calculated using a complex formula
Group By	A field that defines the group of records from which statistical parameters are calculated. One group includes all records for which the values ​​of the field with the Group By mode are the same
Last	Last value in group
First	First value in group
Var	Variation of field values
StDev	Standard deviation of zero values ​​from the average
Count	The number of records whose corresponding zero does not contain the value Null
Max	Maximum value
Min	Minimum value
Avg	Average field value
Sum	Sum of field values ​​across all records

Note Because the fifth field of the query calculates the number of records, you can place any field in the List table in the Field cell of this column.

14. Click on the button View to complete the request. A table with five columns appears. The first two columns contain people's last names and first names. Grouping is performed based on them, that is, the calculation of the values ​​of the remaining query fields is performed for List table records that are matched to one person. As mentioned above, the correspondence between a contact from the List table and a person from the Contacts table is determined by the Contacts_Code fields, which are used to connect these two tables. The third and fourth columns of the query display, respectively, the date of the first (Min function) and last (Max function) contact with this person. The fifth column contains the number of records in the List table (Count function) that match. to a given person, that is, the number of contacts with him. The only drawback of the constructed query is the unclear column names. Let's adjust them.

15. Click a button View return to the query constructor.

16. In the third column Field cell, replace the name Date with the text Date of first contact: Date. The right side of this expression, located to the right of the colon, still specifies the field name, and the left side specifies the name of the query result column. This way, you can assign any name to any query column.

17. In the Fourth Column Field cell, enter Last Contact Date: Date.

18. In the first line of the fifth column of the request form, enter Number of Contacts: Date.

Note Unfortunately, this technique is not suitable for changing the name of a field whose value is not calculated, but transferred from the table. That is, in this way it will not be possible to rename the Name field.

19. Click the button again View.

20. Close the request.

21. To save structure changes, click on the button Yes.

22. In the dialog window Saving enter the name Final Query and click the button OK.

Today we will talk in detail about queries in Access.

Queries, as you already know, are needed to work with data located in tables.
To create a request...
1) ...open Queries in the database window
2) ...and create a request using the constructor.

Please DO NOT use to create queries. Master, because it allows you to make only the simplest queries, and then converting them into more advanced ones is even more difficult than creating a query in the constructor from scratch.

Filtering out empty lines

When multiple tables are linked, empty rows may occur.

Why is this happening?
The fact is that in our tbPerson table, along with dog owners, judges are also recorded (Petrovskaya, Yelets, Tereshchuk). Judges do not have the right to bring their dogs to the exhibition, so in the lines with their last names there are empty cells with the names of the dogs.
There are two ways to remove blank lines.
1. Set a condition on the meaning of the dog’s name Is not Null, i.e. NOT EMPTY.

2. Or change the type of connection between the tables in the tables area: you need to call the context menu on the connection line that gives an inaccurate result and change Merge Options.

Question for you: what settings need to be changed in the merge options dialog box?

Queries with calculations

So far, we have only selected records for various conditions. But Access allows you not only to view the data recorded in tables, but also to make CALCULATIONS: determine age by date of birth; from the first name, surname and patronymic, make a surname with initials; Determine the total cost of the purchase based on the unit price of the product and its quantity; Based on the date of issue of the book in the library, determine the amount of the fine for debt and much more. Built-in functions (similar to those found in Excel) are used for calculations.

The simplest operation is string addition. Let's write an expression in the cell to display the following phrase: owner from the city city .
To do this, write in the top line of the new column of the conditions area: + “from the city” + .

Field names are written in square brackets, string fragments are written in quotation marks, with addition signs between them.

Expressions for calculations are written in the topmost line ( Field) area of ​​conditions. So far we have written conditions in the lower lines ( Selection conditions).

So that you don’t get confused: in the top line we write WHAT to display on the screen, and then (in the bottom) - under WHAT CONDITION.

Exercise: Write an expression to display in one cell the owner's last name and in parentheses the city in which he lives. Like this: Ivanov (Moscow). The city and last name must be entered from the table.

Expression Builder

To make it more convenient to edit expressions, there is a special editor - “Expression Builder”. It looks like this:

And it is called using the context menu: you need to place the cursor on the cell where you will write the expression:

In the Expression Builder, you can choose from a library of functions:

and data from tables (you can use ONLY those tables that are used in this query and displayed in the data area):

When you double-click a field name or function from the list, Access often inserts the word “expression” to indicate that other functions and field names can be inserted IN THIS PLACE. Don't forget to remove unnecessary words "expression"!

We'll look at text and time functions, as well as the conditional statement Iif(condition; if-true; if-false).

Text functions allow you to convert string variables:
Left("Ivanov"; 2) = "Ivanov" leaves n left characters
LCase("Ivanov") = Ivanov makes all letters lowercase
InStr(1; "Ivanov"; "but") = 4 finds the substring (third argument) in the string (second argument), and equals the position (from the beginning) of the substring in the string
Len (“Ivanov”) = 6 displays the number of characters in the line
StrComp("Ivanov"; "Petrov") = -1 compares two strings: if they are equal, then returns 0
and others...

Temporary allow you to work with temporary variables:
Month(#12.04.2007#) = 4
Year(#12.04.2007#) = 2007
Day(#12.04.2007#) = 12.
Now() = 04/28/2008 14:15:42 (current date and time)
Date() = 04/28/2008 (today's date)
DateDiff("d"; #12.04.2007#; #28.04.2007#) = 16 finds the difference between two dates (“d” - in days, ww - in weeks, m - in months, yyyy – in years, etc.)
and others...

Logical process conditional expressions:
Iif(<=1; «щенок»; «взрослый») аналог функции ЕСЛИ из Ecxel.
and others...

Exercise: write an expression that makes a surname with initials from the surname, first name and patronymic. Ivanov Ivan Ivanovich -> Ivanov I.I.
Exercise
Exercise
Addition: There are two ways to calculate a dog's age: one more accurate, the other less so:
1) subtract the dog’s year of birth from the current year;
2) using the DateDiff function, calculate how many days have passed from birth to today. Use one method in one task and another in another.

Queries with parameter

When you run request with parameter, unlike a regular selection request, it is not executed immediately, but first asks you to clarify some selection conditions in a dialog box. For example, we want to get complete information about a dog with a certain tournament number.

This request is structured like this:

In the place where the selection condition usually stands, a question is now written (in square brackets) that will be asked to the user. And the user’s answer, as you may have guessed, will be inserted into this cell as a selection condition.

Exercise: Create a query that will return all dogs based on the owner's last name, which is a free parameter.

Queries with grouping

Using expression builders, we can perform operations on one row: add values ​​in cells, transform data.
But what if we need to process several rows at once: calculate the sum of points, find the number of rows with the same value for a given field?
This is done using grouping (very similar to summarizing in Excel).

Let's count how many dogs of each breed came to the exhibition. To do this, let’s drop only two fields into the conditions area: name and breed – and call an additional line group operations(via the context menu on the conditions area):

Now let’s group the dogs by breed and count the number of different nicknames in each group:

Let's sum up the results of the exhibition and calculate the average score for exterior, average score for training and their sum.

Add a table with ratings (tbMarks) to the table area. We group the estimates by the tournament number of the dog and among the group operations we select the average value of Avg (from the English. average– average).

Run the query and in view mode, notice that the columns with group operations have a double name (operation + field name). This will be useful to us when calculating the sum of points.

You can also round values ​​to one decimal place: Round(+;1)

Exercise: Find out which dog was the most controversial among the judges. To do this, you need to subtract the minimum from the maximum score.

Requests for changes, deletions, additions

In the first lesson, we already talked about the fact that queries allow you not only to view data from tables, but also to edit records: add new ones, delete them, change them. You can change the query type using the query list in the toolbar.

The database operator received new information on the eve of the exhibition:
1) the Desi dog is sick and will not be able to take part in the exhibition;
2) by mistake, Guardian, who is actually an English setter, was listed as an Irish setter;
3) the owner Migunova submitted an application for participation in the exhibition of another of her dogs (nickname: Harry, breed: Gordon Setter, gender: m, date of birth: 09.15.07).

Let's start editing the database.
1) Delete entry from Desi.
Create a quDelDog request. Request type – for deletion. With a change in the type of request, the area of ​​conditions also changes somewhat. A new cell has appeared Removal. Under it, you specify the condition by which you want to select records to be deleted. Even if you specify a selection condition for one field, the ENTIRE record will be deleted.

After you click on the “exclamation mark”, a message will appear on the screen indicating that the entry has been deleted. Now open tbDog table and make sure Desi is not in it.

2) At Guardian, change the Irish Setter to the English Setter.
Create a quUpdateDog request. Request type – update. We find Guardian and update his breed.

Open tbDog table and make sure that Guardian's breed is English Setter.

3) Add an entry with Harry.
Create a quAddDog request. Request type – to add. Add queries have one feature: the table area displays NOT the tables WHERE you add a record, but WHERE you take the data from (if required). You specify the target table (to which records are added) in the dialog box that appears as soon as you set the query type (to add):

Since we are not taking data from other tables, but creating a new record, the table area must be EMPTY! (there shouldn't be any tables there). In the conditions area on the line Field you write WHAT to add (a new value for each field), and in the line Addition WHERE (field names):

Open tbDog table and make sure Harry appears in it!

SQL Query Language

When you click on the "exclamation mark" the request is executed. This is how it appears to a beginner.
Professionals know that at this moment, in fact, an instruction in a special query language, SQL, is being executed. The fact is that Access is not the only database management system (DBMS). Maybe you have heard about such DBMS on the Internet as MySQL, FreeBSD??? Access simply offers a very convenient interface for working with the database, while other control systems do not have any button with an exclamation mark. But there is always a special window in which you can write SQL instructions.
Access also allows you to edit queries in SQL statement mode:

The rules of the SQL language are not that complicated. You can see this for yourself! Make a simple selection request (for example, display the name, breed and date of birth of a dog named Harry). Now open Harry's search query in SQL mode!
The instructions are very simple:
SELECT field1, field2,…
FROM table1, table2,…
WHERE condition1, condition2,…

Now open the update, change, delete queries (quDelDog, quUpdateDog, quAddDog) in SQL mode and write down the templates of their SQL statements on a piece of paper (as was just done for the select query).

Queries with a parameter, queries for grouping, queries with calculations are the same SQL queries, but only with slightly more complex selection conditions. The SQL language is an indispensable tool for those who work with databases!

Quests

Here are the topics we covered:
- simple and compound selection conditions
- LIKE operator
- selection from several tables
- expression builder
- requests with a parameter
- requests for grouping
- requests for updating, adding, deleting
- SQL query language.

There are a lot of them! But, once you have mastered them, you can find any information in the database.

Test your knowledge! Run the following queries in your database (or rename the ones already executed so that their names match the names of the tasks):

1. sample

quSelectDog: Find all Shar-Peis and Gordon Setters from the regions (NOT from Moscow); use the "not equal" operator.

2. Like operator

quLike: Find all MTS subscribers (those with a mobile phone number starting with 8(916)…).

3. expressions

quEvalText: Write an expression that makes a surname with initials from the surname, first name and patronymic. Ivanov Ivan Ivanovich -> Ivanov I.I.
quEvalDate: Write an expression that calculates how old a dog is based on its date of birth.
quEvalIif: write an expression that determines by age what age category the dog is in: “puppy” - up to a year; “junior” - from one to two years; "Senior" - over two years old.
Addition: Use one way to calculate the dog’s age in one task, and another in another: 1) subtract the dog’s year of birth from the current year; 2) using the DateDiff function, calculate how many days have passed from birth to today.

4.parameter

quParameter: Create a query that will return all dogs based on the owner's last name, which is a free parameter.

5. grouping

quGroup: Find out which dog caused the most conflicting opinions from the judges.

6. database editing

Shortly before the exhibition, the owner Gorokhovets left for permanent residence (permanent residence) in Germany and handed over all the dogs to his friend Mikhail Igorevich Karpov. Required:
1) quAddOwner: add a record about the new owner;
2) quUpdateOwner: change the owner ID of Gorokhovets dogs to Karpov’s ID;
3) quDelOwner: remove Gorokhovets from the database.

I'm waiting for your databases with completed tasks, as well as templates of SQL instructions for requests for update, change, and deletion.