Finding data using queries. Sort, search and filter database records Quickly find data
To search for data or records in lists, filters are used that display on the screen only records that meet certain conditions, and the editor temporarily hides records that do not meet the specified requirements. The displayed records (strings) can be formatted, edited, printed, etc.
Filtration means include:
AutoFilter (there are two ways to use the AutoFilter command: using the "Data" - "Filter" - "AutoFilter" - "drop-down list of AutoFilter commands" menu and using the "AutoFilter" button on the standard toolbar)
Advanced filter ("Data" - "Filter" - "Advanced filter") The autofilter is intended for simple conditions for selecting rows, and the advanced filter is for more complex conditions for selecting records. The selection condition is the restrictions set for the selection of records that are selected by the editor for display on the screen.
Using AutoFilter to Find Posts
Before you use the AutoFilter command, select a cell in the list range. Next, execute the command "Data" - "Filter" - "Autofilter", buttons with autofilter arrows (drop-down lists of autofilter commands) will appear to the right of the column headings (labels).
Clicking the AutoFilter arrow displays a list of different filtering options and sorting in ascending and descending order. When filtering is performed, the active autofilter arrow turns blue.
List of autofilter commands and their purpose:
All- all entries will be displayed on the screen
First 10...- the “Imposing a condition on a list” dialog box will open if the data in the column is numbers. Use this window to quickly view up to the ten largest or smallest numbers in a column.
Condition...- the "Custom AutoFilter" dialog box will open. Using this window you can sort records that satisfy one or two conditions
Unique names is a list of non-repeating entries in the cells of a column. If you left-click on one of the unique names, the editor will hide rows that do not contain the selected data. In order for all records to be displayed after filtering, you must click the active button and select the "All" command or execute the "Data" - "Filter" - command "Display all", and to remove an autofilter, you must uncheck the "Autofilter" command in the "Data" menu.
Algorithm for searching records that meet one condition in one column
Select a cell in the list
Click on one of the unique names, the editor will instantly hide rows that do not contain the selected data. The figure shows a filtered list by the "Categories" column with the selection condition "Consumer Products" selected.
If necessary, a list filtered by one column can be filtered by other columns.
Algorithm for searching records that meet one or two conditions in one column
To search for records based on two conditions, use the "Custom AutoFilter":
Select a cell in the list
Execute the command "Data" - "Filter" - "Autofilter"
Click the AutoFilter button in the column you are using to filter data
Click on the "Condition" command, the "Custom AutoFilter" dialog box will open.
From the two left drop-down lists, select the appropriate operators, and from the two right drop-down lists, select the required values. Then set the switch to one of two positions: "and", "or"
Click the OK button to perform filtering.
The list will display records that meet the specified criteria. For example, we will select the following selection conditions: show in the list rows containing products whose prices are greater than or equal to 69.80 UAH and less than 99.99 UAH.
not equal
more or equal
In the Custom Filter dialog box, you can select the following operators:
less than or equal to In addition, you can specify comparison operators that are used when filtering text: “starts with”, “does not start with”, “ends with”, “does not end with”, “contains” and “does not contain”.
The figure shows a filtered list by the "Price" column under the selected selection conditions.
Filter by selection Filter by selection 
is a quick way to filter records by one condition using the "AutoFilter" button on the standard toolbar.
Algorithm for filtering by selection:
Click the AutoFilter button to filter by selection. The editor will turn on AutoFilter and hide rows that do not contain the selected data
Filter a list using the advanced filter
The Advanced Filter command is used to filter by more complex record selection criteria than an AutoFilter, such as multiple selection criteria in one column, multiple selection conditions in multiple columns, or filtering records by filter criteria using a formula. Before using the "Advanced Filter" command, you need to prepare selection conditions, which, as a rule, should be placed above the list. Because of this requirement, a list worksheet must have at least three blank lines above the list. In addition, there must be at least one empty line between the list and the values of the selection conditions.
Creating a range of selection conditions:
Select the headers of the filtered list columns and execute the "Copy" command using any of the known methods
Select the first line to enter selection conditions and execute the "Insert" command using any of the known methods
Enter the required criteria for selecting records in the lines located under the headings of the conditions. The figure shows a sample range of selection conditions: show in the list lines containing confectionery products such as cookies in the first warehouse and drinks in the fourth warehouse.
After creating a range of selection conditions, you can apply the Advanced Filter command to the list.
Filtering algorithm using the "Advanced Filter" command
Select a cell in the list
In the "Data" menu, select the "Filter" - "Advanced Filter" command
Set the Processing switch to Filter list in place if you want the editor to display filter results in the list, hiding rows that do not meet the selection criteria. To copy the filtered records to another area of the worksheet, set the Processing switch to Copy results to another location and specify a range to place the results.
Check the source range of the filtered list cells, the range should contain all the list cells including the column header cells
Check the range of selection conditions, it should contain all cells of the range of selection conditions, taking into account the cells of the column headers
Click the OK button to perform filtering.
The figure shows a sample of the "Advanced Filter" with the selected filtering parameters.
The figure shows a sample filtered list with a range of conditions above it.
In this lesson, the topic “Searching for data using filters” is offered for study. During the lesson you will be able to get an idea about filters and their types. You'll learn how to search for the information you need using three types of filters.
Topic: Technologies for searching and storing information
Lesson: Finding data using filters. Types of filters
Filters - a data retrieval tool that is used to narrow a list to eliminate (temporarily) unnecessary data.
Filter types:
Filter by selection;
regular filter;
advanced filter;
Let's look at an example of how to create and apply a filter by selection: the easiest way to filter is to use a filter by selection. Let’s say we need to select only those apartment museums that are located on the embankment of the Fontanka River. Let's select the word Fontanka. On the tab home, in Group Sorting and Filter, click on the filter Selection and choose Contains Fontanka(Fig. 1). As we can see, only the necessary records remain in our table. To restore all table records, just cancel the filter. To do this, click on the button Remove filter(Fig. 2).
Rice. 1
Rice. 2
A regular filter is distinguished by the fact that the search is performed based on one characteristic in one field. Let's see how to create and apply such a filter:
Let us only need those museum-apartments that are located on the Moika embankment or on the Fontanka embankment. Let's select the field for filtering, and then we can either click on the button Filter(Fig. 3), or click on the field name on the right side and open the list. In this list, uncheck the box Select all, then check the boxes next to the addresses that interest us and click on the button OK(Fig. 4). We see that only the records we need remain in the table. To cancel filtering, you can click on the button Remove filter(Fig. 5), or at the bottom of the window (Fig. 6). After this, the original view of the table with all records will be restored.
Rice. 3
Rice. 4
Rice. 5
Rice. 6
The advanced filter differs from the previous ones in that the search is carried out in several tables, according to several criteria, in several fields. An advanced filter can be created in this way:
Let's create and apply an advanced filter. On the tab home, in Group Sorting and Filter, click on the button Advanced filter options and select the command from the context menu Advanced filter(Fig. 7). A window will open in which we can select selection fields and selection conditions, and there can be one or more conditions. Let us need those museums whose tickets cost 150 or 100 rubles, and which start working at 10:30. It remains now Apply filter(Fig. 8).
We see that only one entry, only one museum, satisfies the selection conditions.
Rice. 7
When there is a lot of data in the database, and it is difficult to find specific ones while looking through them, a filter is applied.
Filter is a set of conditions designed to select records or sort them.
When using a filter, a logical expression is specified that will allow you to display only those records for which this expression evaluates to "True".
The filter is selected in a special filter window.
To install or change it, you must select “ Posts"command" Change filter”,
then, after setting all the conditions, select “ Posts"command" Apply filter”.
To restore the display of all records, select from the menu “Records” team “Remove filter.”
You can also use the corresponding buttons on the toolbar -
There are two types of filter - simple and advanced. At the top of the simple filter window, a list of all table fields is displayed.
You can select the required search condition from the drop-down list and apply a filter.
What states are in Asia?
Which country's capital is Jakarta?
For more complex selection conditions, advanced filters are used. 
At the bottom of this window, you must indicate which fields are being searched and the selection conditions.
In line "Field" You must specify the name of the field by which the required information is searched. This can be done in the following ways:
· type its name on the keyboard;
· drag the mouse from the list of fields;
· double-click on the name of the desired field in the list of fields;
· click the mouse in the “Field” line and select the desired field name from the drop-down list.
In line “Selection condition” you must specify a set of conditions.
Templates can be used in expressions for selection conditions:
· * (asterisk) – replaces any group of any characters;
· ? (question mark) – replaces any one character;
For example:
" A* "– all words starting with “A”
“ ????? ” – all words consisting of five letters
" ?And* "– all words whose second letter is “and”
You can also use in conditional expressions
· logical operations, >=, =,
· logical functions Or(disjunction), And(conjunction), Not(negation).
Conditional expressions typed in different columns of the same line “Selection conditions” connected by function AND.
Conditional expressions typed on different lines “Selection conditions” connected using the OR function.
For example:
Find all states whose names and the names of their capitals consist of five letters
Find all states whose names or the names of their capitals consist of five letters
Find all states whose names contain the letter “e” or “i”
Find all states whose area is less than 100
Find all states whose area is at least 100
Use filters to answer the following questions:
1)
Which states are located in Europe?
2)
What states are located on the American continent?
3)
Which states have the name of their capital consisting of five or four letters?
4) Which states whose names begin with the letter “M” are located in Asia
In this lesson we will talk about the topic “Filtering, sorting and searching data in Microsoft Access”.
When there are quite a lot of records in a table, the ability to view them selectively and in a certain order becomes very important. You can use the tools on the Home ribbon page to hide some table columns and records, and to sort records by one or more fields. To hide a selected column or group of columns for a while, expand the “ribbons” section "Records".
Click the "Advanced" button and select "Hide Columns" from the menu that opens. 
To show hidden columns again, expand the “Posts” section of the ribbon, click the “Advanced” button and select the “Show Columns...” command from the menu.
In the dialog box that opens, you need to tick the columns that should be displayed in the table data view. Changes made in the window are immediately reflected in the view. Select the hidden column checkbox and click the Close button. 
In order to display only table records selected by a certain criterion, you can use a filter, that is, a condition set for a field. After applying the filter, records that do not meet the specified condition will be hidden. To assign a filter, select one of the columns, for example, the "Sum" column, and click the "Filter" button in the "Sort and Filter" section of the ribbon. 
In the list of field values, clear the values that you do not want to display by unchecking the checkboxes next to each value. To apply the filter, click OK. 
After applying the new filter, the data view is updated. A field with a filter installed is marked with a special icon in the header. 
To temporarily remove a filter from a selected field, click the “Remove Filter” button. 
When you remove the filter, the view is also updated. Click the same button again to turn the filter on again. 
To permanently clear the filter, click the arrow in the table column header and select the “Remove filter from Sum” command. 
To set a condition for a filter in the form of a logical expression, call the sorting and filters settings window again. 
The list of available logical operators will depend on the data type of the field. Expand the “Numeric Filters” list. Select the “More…” filter from the list and, having specified the value for comparison, click the “OK” button. 
Now only records containing values of at least twenty in the “Amount” field will be displayed on the screen. You can only apply one simple filter to an individual field, but you can define your own for each field. In this case, only records that satisfy all the conditions simultaneously will be displayed. More complex filters, which can include values from multiple fields at once, are set using the Advanced Filter Options command. 
The filter parameters are valid until the table is closed and are not lost when switching the display mode. If you save the table, the applied filter will be available the next time you open it. You can turn a saved filter on and off using the navigation bar button “Without filter” - “With filter”; the label on the button reflects the current state of the field. Click on the button labeled “With Filter” to turn off the filter.
To organize records in a table data view, you can sort by text, numeric, or date. Sorting allows users to find the data they need without having to sift through everything. Records can be sorted by one or more fields. To set the sort order in ascending order of field values, click the “Ascending” button.
To sort in descending order of values, click the Descending button. 
To cancel sorting by all fields at once, click the “Clear all sorting” button.
In Microsoft Office Access, records are sorted in ascending or descending order and are not case sensitive. The order in which numbers, text, and special characters are sorted depends on the language settings and standards you select for your computer. When sorting by several fields at once, the result will depend on the order in which the sort mode is applied to these fields. If you need to find one or more records based on a piece of text included in one of their fields, you can use the standard Microsoft Office Access search tool. Click the Find button on the Home feed page. 
In the “Search and Replace” dialog box, you need to specify a pattern for the search and select the desired search parameters, for example, set the area where the pattern matches any part of the field, or view across all records. 
To move to the next found record, click the “Find Next” button. 
After completing the search, close the window. 
Quick data search The main purpose of the database is to search for the necessary information. Access allows you to search: by part of the name, if you don’t know it exactly, how the name is spelled; by the initial letters of the word; at the end of a word, etc.; When specifying the searched text fragment, you can use wildcard characters wildcard characters
Searching for data using filters Filters allow you to select records that meet specified conditions. Conditions for selecting records are created using comparison operators (=, >, =,). Filters: simple and complex , =,). Filters: simple and complex"> , =,). Filters: simple and complex"> , =,). Filters: simple and complex" title="Searching data using filters Filters allow you to select records that satisfy specified conditions. Conditions for selecting records are created using comparison operators (=, >, =,). Filters: simple and complex"> title="Searching for data using filters Filters allow you to select records that meet specified conditions. Conditions for selecting records are created using comparison operators (=, >, =,). Filters: simple and complex"> !}
Finding Data Using Queries Queries search for data in a database in much the same way as filters. filterquery Search for data by records - by fields Searching for data in one table combines data from one or more tables Displays all fields of the table only those. needed for searching is not saved as a separate object can be saved as a separate object does not allow you to create calculated fields allows you to create calculated fields Differences
