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.
In the Custom Filter dialog box, you can select the following operators:
more or equal
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”.
not equal
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:
Select the required entry in the list
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, for example, by multiple selection criteria in one column, by multiple selection conditions in multiple columns, or to select records by selection 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 the 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 list will display records that meet the specified criteria
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 table mode, where data is displayed in rows and columns format, you can customize the table layout by setting the desired composition of columns, changing the placement and width of columns, row height, data font, etc. queries and forms can also be presented in this mode.
With appropriate access rights, the user can edit table data in table or form mode by adding and deleting records, as well as updating field values
When viewing records in table or form view, you can search for records, sort records by one or more fields, and filter records according to specified selection criteria.
Finding and replacing values in fields
To search for values in the current or all fields of a table, in table or form mode, click the Find toolbar button or use the menu command Edit | Find.
In the dialog box that appears Search and replace On the tab Search in field Sample Enter the value that you want to find in the table fields. In the dropdown list Search in the search area is selected. If no columns are selected in the table, this drop-down list allows you to select the field in which the cursor is positioned or the entire table as the search area. If several columns are selected in a table, then this list is inactive and the search is automatically performed only in the selected columns. In the dropdown list Coincidence you can select the degree of coincidence of values: From the beginning of the field, With any part of the field, Entire fields.
Drop-down list View at the bottom of the window allows you to select the search direction, which can take values All, Up, Down.
If you select the Replace tab in the dialog box, it will be complemented by a drop-down list Replaced by, which allows you to enter a new value into the found fields.
Sorting entries
Sorting records by ascending or descending values of one field (sort field) can be done both in table mode and in form mode.
You can also use the menu command to sort records Posts | Sorting. To sort form entries by multiple fields, the form must be open in Datasheet view. When you switch to form mode, the form records will be viewed in accordance with the sorting performed.
Using filters
To view and correct database records that satisfy user-specified selection conditions, table filtering is provided. Filter is a set of conditions applied to a subset of records from a table, form, or query.
Filter by selection
The simplest way to set the conditions for selecting records is to highlight a certain field value or part of it in a table or form. Let's look at this filtering method using the student table as an example.
Suppose you need to find all records about students whose last names begin with the letter “B”.
Let's open the student table in table mode. Let's highlight the letter c in the Full Name field of one of the records where the last name begins with this letter. Select a menu command Posts [Filter | Filter by selection or click the corresponding button on the Table toolbar in table view. As a result of filtering, only those records in which the student’s last name begins with the letter “B” will be displayed in the table.
As a result of filtering, only those records in which the student’s last name begins with the letter “B” will be displayed in the table. Among the data found on students, we will select those born in 1977.
Select the value 77 in the Date of Birth (ddtar) field of one of the records. After executing the command Filter by selection Of all the records in the subset, only those whose field has the value 1977 will remain in the table.
In a table or form for which a filter is applied, the record navigation row indicates from [number of records] (Filter).
Each new selection of a value imposes additional conditions on the selection of records by the existing filter. Thus, this method allows not only to create a new filter, but also to supplement a filter created by any other method with new selection conditions.
To select records whose fields do not contain the selected value, after selecting the value, select the menu command Posts | Filter | Exclude selection or right-click and select the command in the context menu Exclude selection. If you need to work with all records of the table, then the filter action can be canceled using the command Posts | Remove filter or by clicking the corresponding button on the toolbar. In this case, the filter is removed from the table, but saved, and at any time it can be reapplied using the command Posts | Apply filter.
Created by command execution Filter by selection or Exclude selection the filter is saved automatically when you save a table, query, or form. On subsequent openings of the table, this filter can be applied again.
Regular and advanced filter.
In addition to the selection filter, you can use two other types of filters - regular and advanced filters.
Regular filter
Regular filter called by the Write | Filter | Change filter, or the corresponding toolbar button. In this window you can create a new filter or view and edit a previously created filter.
After executing the command in the regular filter window Filter Displays an empty table or form for the active database object. On the tab Find The values by which records will be selected are entered into the filter fields. Values can be entered from the keyboard or selected from a list, as shown for a field. Entering values in several fields of one filter line determines the selection of records that contain all the specified values. In this case, the specified conditions are considered as being combined by the logical operation “AND”.
To specify an alternative option for selecting records included in the result set, select the tab Or in the lower left corner of the filter window. This opens a new filter field for specifying an alternative option. A set of conditions entered in different fields is linked by a logical OR operation. The next alternative can be set on the next tab Or.
The filter will select records containing in the fields all the values specified on the tab Find, and records containing in the fields all the values specified on the tab Or. To perform filtering, click the button Applying a filter.
Advanced filter
Advanced filter called by the command Posts | Filter | Advanced filter. In this case, a list of fields of the active table is displayed in the upper part of the advanced filter window. At the bottom of the window it appears request form. To the line of the request form Field From the list, fields by which you need to set the conditions for selecting records are dragged with the mouse. Selection conditions are entered into the corresponding field of the filter window. In addition, the request form allows you to select the sort type for one or more selected fields in the field Sorting.
Saving a filter
The regular and advanced filters, as well as the selection filter, are saved automatically when you save a table, query, or form. When the object is opened again, this saved filter is the current one and can be used by command Posts | Applying a filter. If a new filter is created, it replaces any filter previously saved with the form or object in Datasheet view. To destroy a filter on the toolbar Filter click the button Clear form and then the button Applying a filter. Only in this case will the filter saved with the table be destroyed.
Saving a filter group
It is advisable to prepare and save several filters for a table or form, which can later be loaded into the filter window and applied. To save the prepared filter, run the command Posts | Filter | Change filter or click the corresponding toolbar button and select the command File | Save as request, or click the corresponding toolbar button Filter. To execute a command, you can also call the context menu when the filter window is active. In the dialog box that opens Saving as a query you must enter the name of the saved filter in the field Request name.
In the future, instead of creating a new filter, you can use an existing filter. To do this, go to the Change filter mode and use the command File | Load from request. In the window Filter applied the desired filter is selected.
You can display information on one/several parameters using data filtering in Excel.
There are two tools for this purpose: AutoFilter and Advanced Filter. They do not delete, but hide data that does not meet the conditions. Autofilter performs the simplest operations. The advanced filter has much more options.
AutoFilter and Advanced Filter in Excel
I have a simple table that is not formatted or declared as a list. You can enable the automatic filter through the main menu.
If you format the data range as a table or declare it as a list, the automatic filter will be added immediately.
Using an autofilter is simple: you need to select the entry with the desired value. For example, display deliveries to store No. 4. Place a check mark next to the corresponding filtering condition:
We immediately see the result:
Features of the tool:
- The autofilter only works in a non-breaking range. Different tables on the same sheet are not filtered. Even if they have the same type of data.
- The tool treats the top line as column headings - these values are not included in the filter.
- It is permissible to apply several filtering conditions at once. But each previous result may hide the records needed for the next filter.
The advanced filter has much more capabilities:
- You can set as many filtering conditions as needed.
- The criteria for selecting data are visible.
- Using the advanced filter, the user can easily find unique values in a multi-line array.
How to make an advanced filter in Excel
A ready-made example - how to use an advanced filter in Excel:
Only the rows containing the value “Moscow” remained in the original table. To cancel filtering, you need to click the “Clear” button in the “Sort and Filter” section.
How to use the advanced filter in Excel
Let's consider using an advanced filter in Excel to select rows containing the words “Moscow” or “Ryazan”. Filtering conditions must be in the same column. In our example - below each other.
Filling out the advanced filter menu:
We get a table with rows selected according to a given criterion:
Let’s select rows that contain the value “No. 1” in the “Store” column, and “>1,000,000 rubles” in the cost column. The criteria for filtering must be in the appropriate columns of the conditions table. On one line.
Fill in the filtering parameters. Click OK.
Let us leave in the table only those rows that contain the word “Ryazan” in the “Region” column or the value “>10,000,000 rubles” in the “Cost” column. Since the selection criteria belong to different columns, we place them on different lines under the corresponding headings.
Let’s use the “Advanced Filter” tool:
This tool can work with formulas, which allows the user to solve almost any problem when selecting values from arrays.
Basic Rules:
- The result of the formula is the selection criterion.
- The written formula returns TRUE or FALSE.
- The initial range is specified using absolute references, and the selection criterion (in the form of a formula) is specified using relative ones.
- If TRUE is returned, the row will be displayed after the filter is applied. FALSE - no.
Let's display rows containing quantities above average. To do this, aside from the plate with the criteria (in cell I1), enter the name “Largest quantity”. Below is the formula. We use the AVERAGE function.
Select any cell in the source range and call “Advanced Filter”. We indicate I1:I2 as the selection criterion (relative links!).
Only those rows where the values in the “Quantity” column are above average remain in the table.
To leave only non-repeating rows in the table, in the “Advanced filter” window, check the box next to “Only unique records”.
Click OK. Duplicate lines will be hidden. Only unique entries will remain on the sheet.
A filter is a quick and easy way to find a subset of data and work with it in a list. The filtered list displays only rows that meet the criteria. Unlike sorting, a filter does not change the order of entries in the list. Filtering temporarily hides rows that you don't want to display.
Rows selected by filtering can be edited, formatted, created into charts, and printed without changing the row order or moving them.
Filtering selects only the necessary data and hides the remaining data. This way, only what you want to see is shown, and it can be done with one click.
When filtering, the data does not change in any way. Once the filter is removed, all data appears again in the same form as it was before the filter was applied.
Excel has two commands for filtering lists:
- Autofilter, including a filter by selection, for simple selection conditions.
- Advanced filter for more complex selection conditions.
Autofilter
To enable Autofilter you need to select any cell in the table, then on the tab Data in Group Sorting And filter press the big button :
After this, a down arrow button will appear in the table header to the right of each column heading:
Clicking an arrow opens a list menu for the corresponding column. The list contains all the elements of a column in alphabetical or numeric order (depending on the data type), so you can quickly find the element you need:
If we need a filter for only one column, then we don’t have to display arrow buttons for the remaining columns. To do this, before pressing the button select several cells of the desired column along with the heading.
Filter by exact value
Turn on Autofilter, click on the arrow button and select a value from the drop-down list. To quickly select all elements of a column or deselect all elements, click on the item (Select all) :
In this case, all rows whose field does not contain the selected value are hidden.
When performing laboratory work, select the filtering result, copy it to another place on the sheet and sign it.
To turn off Autofilter you need to press the button again .
To cancel the filter action without leaving the filtering mode, click on the button and select the item from the drop-down list (Select all) . In this case, table rows hidden by the filter appear.
Signs of data filtering
Filters hide data. This is exactly what they are designed for. However, if data filtering is not known, it may appear that some data is missing. You might, for example, open someone else's filtered sheet, or even forget that you yourself previously applied a filter. So when you have filters on a sheet, you can find different visual cues and messages.
(located at the bottom left of the window). The initial state:
Immediately after filtering the data, the result of applying the filter is displayed in the lower left corner of the status bar. For example, " Records found: 2 of 11”:
Line numbers . Intermittent line numbers indicate that some lines are hidden, while visible line numbers that change color indicate that the highlighted lines are the result of a filter selection.
Arrow type . When the AutoFilter arrow in a filtered column changes to, it indicates that the column is filtered.
”
“” is another universal filter that can be applied to columns with numbers or dates.
“” is a very conventional name. In fact, the capabilities of this filter are much wider. Using this filter, you can find either the first elements or the last elements (smallest or largest numbers or dates). And, contrary to the filter's name, the results obtained are not limited to the first 10 elements or the last 10 elements. The number of items displayed can be selected from 1 to 500.
” also allows you to filter data by percentage of the total number of rows in a column. If a column contains 100 numbers and you want to view the largest fifteen, then select 15 percent.
You can use the filter to find products with the highest or lowest prices, to determine the list of employees most recently hired, or to view a list of students with the best or worst grades. To apply the “” filter to a data column ( only numbers or dates!!!), click the arrow in the column and select the item Numeric filters Further :
After this, a dialog box will open Overlay conditions By list :
In the dialog box select number(rows or percentages), largest or smallest, list elements or % of the number of elements.
Create your own custom filters
For example, we need to output only rows with positions starting with the letter ‘ D’. To do this, click on the autofilter arrow in the first column and select Text filters , then point begin with… :
A dialog box will appear (Whichever item on the right you select, the same dialog box will still appear.):
In field Job title choose – begin with , on the right we enter d:
In the window there is a hint:
Question mark " ? ” means any one character.
Sign " * ” denotes a sequence of any characters.
Processing data in the database
Quick data search
Access allows you to search for records in which the values of a specific field completely or partially match a certain value.
For example, in the Internet Providers database we want to find a record containing information about the MTU provider, but we do not remember its full name. You can enter only part of the name and search for the entry.
Quick search for data in the Internet Providers database
2. Enter the command [Edit-Find...]. A dialog box will appear Search. In field Sample: you must enter the search text, and in the field Coincidence: select item With any part of the field.
 |
3. As a result, record No. 3 will be found and marked as active.
Searching for data using filters
Much more opportunities for searching data in the database are provided filters. Filters allow you to select records that meet specified conditions. Conditions for selecting records are created using comparison operators (=, >,
Simple filters contain a condition for selecting records for only one field. Complex filters contain several conditions for different fields. As a result of applying a complex filter, only those records that satisfy all conditions at the same time will be selected. We can say that the conditions in complex filters are interconnected by the operation of logical multiplication.
Let, for example, we look for an optimal provider, that is, a provider that does not charge a connection fee, the hourly rate is quite low (500), and it has high-speed Internet access (channel speed >100 Mbit/s).
Let's create a complex filter for the Internet Providers database.
Finding data using a filter
1. Open the “Internet Providers” database table by double-clicking on the corresponding icon in the database window.
2. Enter the command [Records-Filter-Change Filter]. In the table window that appears, enter the search terms in the appropriate fields. The filter has been created.
Finding Data Using Queries
Requests search for data in the database in the same way as filters. The difference between them is that queries are independent database objects, while filters are tied to a specific table.
A query is a derived object of a table. However, the result of a query is also a table, meaning queries can be used instead of tables. For example, a form can be created for both a table and a query.
Queries allow you to select those records that satisfy specified conditions. Queries, like filters, can be simple or complex. A simple query contains a single condition, while a complex query contains multiple conditions for different fields.
When creating a query, you can select not only records, but also fields that will be present in the query.
Let's create a complex query to identify the optimal provider in the Internet Providers database.
Finding data using a query
1. In the window select a group of objects Requests and select item .
2. On the dialog panel Adding a table Add.
3. In the request window in the line Field: From the drop-down list, select the names of the fields for which conditions will be set.
In line Selection condition: enter conditions for the selected fields.
In line Output on display: set the fields that will be presented in the request.
Practical tasks
3.5. Carry out various types of searches in the “Notebook” and “Library Catalog” databases: quick, using a filter and using a query.
3.6. In the Internet Providers database, search for providers who do not charge connection fees and charge the lowest hourly rates.
Sorting data
Databases can contain hundreds or thousands of records. It is often necessary to organize records, that is, arrange them in a certain sequence. The ordering of records is called sorting.
Records are sorted by some field. The values contained in this field are arranged in a specific order, which is determined by the field type:
- alphabetically if the field is text;
- by number size, if the field is numeric;
- by date, if the field type is Date Time and so on.
Records can be sorted either in ascending or descending order of field values. During the sorting process, the integrity of the records is maintained, that is, they are transferred from one place in the table to another entirely.
Sorting entries databases are their ordering by the values of one of the fields.
Let's sort in the "Internet Providers" database, for example, by the "Channel speed (Mbit/s)" field.
Quick data sorting
1. In the window Internet Providers: Database in a group of objects Tables select the "Internet Providers" table and click on the button Open.
2. Select a field Channel speed and enter the command [Records-Sort-Sort Ascending]. Records in the database will be sorted in ascending order of channel speed.
 |
Can be implemented nested sorts, that is, sortings that are sequentially performed on several fields. After sorting by the first column, sorting by the second column is performed, and so on.
In our case, in the field Channel speed, by which the sorting was performed, two records (8 and 7) have the same value 10 and two records (3 and 2) have the same value 112. To sort these records, let’s do a nested sort, first by the “Channel Speed” field, and then in the "Number of input lines" field.
Access allows you to nest sorts using queries.
Nested data sorting using a query
1. In the window Internet Providers: Database select a group of objects Requests and select item Creating a Query Using the Designer.
2. On the dialog panel Adding a table select the "Internet Providers" table for which the query is being created. Click the button Add.
3. In the request window in the line Sorting: From the drop-down list, select the names of the fields in which to set the sorting type.
Practical tasks
3.7. Perform nested sorting in the “Internet Providers” database by the fields “Hourly Rate” and “Name of Provider”.
Printing data using reports
You can print tables, forms, and queries directly using the [File-Print] command. However, for beautiful document printing, it is advisable to use reports. Reports are derived database objects and are created based on tables, forms and queries.
Let's create a report that will beautifully print out the Internet Providers database. Let's use it for this Report Wizard.
Printing a database using a report
1. In the window Internet Providers: Database select a group of objects Reports and select item Create a report using the wizard.
2. Using a series of dialog panels, set the report appearance parameters.
3. In the window Internet Providers: Database click on the button View. The document appears in a form in which it can be printed.
 |
4. If you are satisfied with the appearance of the document, print it using the [File-Print] command.
Practical tasks
3.8. Create a Business Card report for the Notebook database and a Library Card report for the Library Catalog database.