Sorting grouping window in the access form. Sorting and grouping in reports. Sort by one field
The Access report you designed in the previous two exercises has another major flaw. The names of people in the Contacts list are not displayed in alphabetical order. The Report Wizard automatically grouped the data in the List table according to their belonging to certain records in the Contacts table, which is determined by the relationship between the Contact_Code fields of these two tables. For this reason, the names in the report are ordered in ascending order by the numerical parameter Code_Contacts - the identifier of records in the Contacts table. This is inconvenient; in reports with a large number of records, with such sorting it will be quite difficult to find the right person. It makes sense to change the sort order; to do this, follow these steps.
1.Click on the button Reports Access database windows.
2. Highlight the Contact List report icon.
3. Click the button Constructor toolbar of the database window, open the report in design mode.
4. Click the button Sorting and grouping toolbar to open the dialog box shown in Fig. 18.19.
Rice. 18.19. Sorting and grouping data in an Access report
There are two sort levels assigned in this window. First, in ascending order by the Code_Contacts field, and then within the list of contacts related to one person, in ascending order by the contact date. An icon is visible on the Code_Contacts line button, indicating that records are grouped by this field, that is, when iterating through the records of the Contacts table, for each new value of the Contact_Code field, a group header is generated, in which, as you should remember from the previous exercise, the first name, last name and other characteristics of people are displayed.
It would seem that you can simply change the sorting field Code_Contacts to Last Name, and the records in the report will immediately be sorted alphabetically. But in this case, for four people with the same surname Petrov, only one group header will be displayed, below which a list of contacts with all Petrovs will be placed. Remember that a new group header is displayed only for each new field value that you group by. Code_Contacts is a unique identifier of records in the Contacts table, grouping by which ensures that all records in this table are displayed in the report. To sort records alphabetically by last name and first name, add to the dialog box list Sorting and grouping corresponding fields, placing them above the Code_Contacts grouping field.
5. Click on the third line drop-down button Field/Expression.
6. Select Last Name.
7. From the Field/Expression drop-down list on the fourth row of the Sort and Group window, select Name.
Note When you add a new sort field, Access automatically assigns the Ascending sort order to it. To reverse the order of records, select the Sort Order cell drop-down list for the corresponding row. Descending.
8. Click on the Last Name line button to highlight it.
9. Place the pointer over the Last Name row button, click and drag the row to the very top of the list. Release the mouse button at the moment when the thick horizontal line indicating the future location of the moved row is above the first row of the table.
10. Repeating steps 8 and 9, place the First Name line between the Last Name and Contact Code lines, as shown in Fig. 18.20.
Rice. 18.20. Adding data sort fields in an Access report
11. Close the Sort and Group window.
Note The headers of those groups for which there are no records in the List table are not displayed in the report.
12. Click on the button Toolbar view to display the report in preview mode.
Now records are first sorted by last name, then by first name. After that for each new entry In the Contacts table, the group header appears (since the values of the Contacts Code field are different for all records). Below each such header from the List table, all contacts corresponding to a given value of the value Code_Contacts are displayed, that is to this person(Fig. 18.21).
Rice. 18.21. Final view of the Access report
13. Close the Access report.
Reports become more understandable and structured if the data in them is organized into groups and subgroups. However, the report structure can be changed. To do this, use the dialog box
To open the dialog box, open the report in Designer and click the Sort button and grouping on the toolbar Report designer.
Rice. 12.
In this dialog box, you can define fields or expressions by which Access will group data. Additionally, grouped data can be sorted in ascending or descending order. The highlight button symbol in the left gray column of this dialog box indicates that the field or expression (located to the right of the icon) is used to group records. IN in this example the “Category” field is used to group report lines, and the sorting of lines within a group is performed by the “Product Code” field.
Application interface development
There are several ways to organize command interface applications. You can create a special menu bar or Main Button to provide a convenient means for the user to select actions to perform in the application. Commands should be grouped in menus or on button form pages according to their purpose, so that it is intuitively clear to the user where a certain command should be located and in what cases it should be executed. Usually, to work with an application, a special form is created, which is called the Main Button Form and contains buttons that allow you to select the main functions or subsystems of the application. You can create such a form, as usual, using the Form Builder, or you can use a special wizard called the Button Form Manager. The Main Button Form was created in the Northwind database without using a wizard. Let's see how to create a similar form using the Button Form Manager.
Select menu command Service, Utilities, Button Form Manager. Click Yes. Now the wizard creates the “Pushbutton Form Elements” table and the pushbutton form.
Rice. 13.
To create the desired buttons on it, click the button Change. A dialog box opens Changing the Button Form Page, containing a list of form elements.
Rice. 14.
In the field Button name we can change the standard name if necessary. To create the first element, click this button. A dialog box appears Changing a button form element. In the field Text enter name button being created, for example Products. Second field Team allows you to define the action that will be performed after pressing this button. The number of buttons on one form page is limited to 8. This does not mean that you cannot create more of them, you just have to do it manually.
If the number of actions that you would like to place on buttons exceeds 8, you can group some of the actions on a separate page of the button form. For example, let's create another page of our button form and place customer action buttons on it. To do this, click the button in the dialog box Create. In the dialog box that appears, enter a name new page Clients and press the button OK.
Next, in the page editor, we will create elements in a well-known way: Customers, Customer stickers, Customer phone numbers, Customer orders, which open the corresponding forms. Don't forget to create a button that will allow you to return to the Main Button Form. To do this you need to associate the command with it Go to button form. Close this window to return to dialog box. You should have two pages appear in the list of pages.
Now you need to edit the first page - add an element to it that can be used to go to the second page. To do this, select the page in the list Main button form and press the button Change. In the page editor window add new element Clients also using the command Go to button form. Now both pages are linked. You can close the wizard window and see what we have done by opening the “Push Button Form” form. You can check how the buttons work. You can go to Design mode and design appearance forms using all the tools of the Designer. Thus, using the Pushbutton Manager, you can create a hierarchical system of pushbutton pages, which can be an alternative to the usual hierarchical menu.
Another way to create an application interface besides a button form is to create a menu and add a toolbar to it. There is a very convenient tool for this called the command bar.
Command bars- these are objects Microsoft Office that provide standard way organizing user interaction with Microsoft application Office. They come in three types:
Menu bar;
Context menu;
Toolbars.
In your application, you can use both standard Access command bars, customizing them as needed, and special custom command bars.
Creating and modifying command bars of any type is performed in configuration mode, which can be called either using the menu command View, Toolbars, Customization, or from the context menu that appears on the screen after right-clicking in the application command bars area.
Open the window Settings, for example, by right-clicking on the toolbar and selecting the command Settings. In this way, you switch to the command panel configuration mode. By default the tab opens Toolbars. If you have another tab open, expand that tab. Currently, only standard command bars are displayed in the list on this tab. To this list you need to add a new command bar like Menu bar. Click the button Create. In the dialog box that asks for a name for the command bar you want to create, enter the name: Main Menu and click the button OK. A small empty panel will appear, which is the toolbar by default. To turn it into a menu bar, you need to change its type. To do this, select the element Main menu in the list on the tab Toolbars and press the button Properties. A dialog box will open Properties toolbars Properties. In the dropdown list type select value Menu bar.
In addition to the type, you can set a number of properties for this command bar:
Consolidation. You can set the location of the command bar on the screen: anywhere, only horizontally, only vertically, or prohibit changing it altogether.
Display in menu. Select this check box if you want the name of this command bar to appear in the list that appears when you execute the menu command View, Toolbars.
Setup. Clearing this checkbox will prevent this command bar from being modified.
Resizing. If this checkbox is not selected, resizing this command bar will not be possible.
Moving. Clear this check box if you want to prevent the command bar from moving around the screen.
? show and hide. When this option is cleared, the user will not be able to hide and show the command bar on the screen.
For now our menu bar is empty. Now you can add menus to it - lists of commands that drop down when you select a menu in the menu bar or toolbar. To add a menu to the command bar:
· Expand the Commands tab in the Settings dialog box.
· Drag New Menu from the Commands list to the new panel.
· Now you will see the New Menu item on it.
You can change the name of the created menu in the properties window. To do this, right-click on the menu and select context menu team Properties. A dialog box will appear Main Menu Item Properties. In this window you can set the following values:
In the signature field, enter the name of the menu: Clients.
In the Pop-up message field, you can enter the text of the tooltip.
The action field should be left blank. Typically it specifies the action performed when a given command bar item is selected. When you select the Clients menu, a list of commands for this menu should open. This will be done automatically because... We have added menu as a menu bar element.
The style field allows you to define the appearance of the command bar element, in in this case- menu. The dropdown list has the following meanings:
ь By default - an icon and a caption are displayed simultaneously on a menu item;
ь Text only (always) - only the inscription is displayed on both the menu element and the button;
ь Text only (in the menu) - the inscription on the menu item and the icon on the button are displayed;
b Picture and text - both the icon and the inscription are displayed on both the menu element and the button.
Two fields—Help File and Help ID—allow you to associate a topic from a given command bar item with help system application that will be displayed when you press a key
The parameter field is associated with the action field and determines the value of the function argument if the action field defines a function call. In this case, it also remains empty.
In the field additional information you can enter information that may be available from a VBA procedure.
A selected Create group checkbox means that this command bar element is the beginning of a group and a dividing line will be displayed to the left of it (in this case in the menu bar).
Close the window Menu Item Properties and we will add commands to the menu. You can use the same tab for this Teams dialog box Settings. By selecting a category and then selecting a command from the selected category, you can drag it to this menu. For example:
Drag into the menu the forms that we used when creating the button form in the previous section: “Customers”, “Customer Stickers”, “Customer Phone Numbers”, “Customer Orders”.
To add the following menu with a list of commands to the menu bar, you need to select the tab again Teams dialog box Settings category New menu, then element New menu and drag it to this menu, placing it to the right of the first element. To change the appearance of a menu or submenu, right-click on the corresponding element. You can use the commands in the context menu that appears or open the window Element properties.
Removing a menu is very easy. In the command panel customization mode, it is enough to drag the corresponding element into an empty area of the Microsoft Access window. Similarly, you can drag submenus and menu commands from one menu to another, you can also turn a menu command into a button on the toolbar and vice versa. Because menus and toolbars are different types one object - the command bar; you can place menus on the toolbar, and you can add buttons to the menu bar.
To finally give your menu a professional look (as in standard menu Access), you need to assign access keys to each menu and menu command. The letters corresponding to these keys are highlighted in the element name with underlining. The access keys are used as follows. In order to open the menu, you need to hold down the key
To specify the character that will define the access key, you need to enter the command name (in the signature menu item properties window) insert an & sign before this symbol, for example “&Products”. In this case, in different commands of the same menu, different symbols must be used to define the access keys, although the symbols may be the same in different menus.
To provide quick access to the most frequently used forms, reports, or macros, you can create special buttons that are placed either on the toolbar or directly in the menu. Creating these buttons is very simple:
Close the Settings window.
Expand the list of forms in the Northwind application database window, highlight the Products form and drag it to the menu bar we just created. A button with an icon and the name of the form will appear in the menu bar; when clicked, the “Products” form will open.
As you can see, one menu bar can contain both submenus and buttons. In the same way, you can add a button to one of the standard toolbars. If there are many such buttons that you need to create to call various functions of the application, it is better to create a separate toolbar in which to include all these buttons, as well as useful buttons from standard toolbars.
A custom toolbar is created similar to menu bars:
Open the Settings window again by right-clicking in the command bars area of the application window.
In the dialog box that appears, expand the Toolbars tab.
Click the Create button.
In the dialog box that appears, enter a name for the new My Macros toolbar and click OK. An empty toolbar will appear.
In this case, you do not need to change its type (by default, a toolbar is created); open the Commands tab.
Drag this macro using your mouse onto an empty toolbar.
You can change the appearance of this button. To do this, in the dialog box Settings click the button Edit selected object or right-click on the newly created button. A context menu will appear. In this case, the button display mode is marked as Icon and text. But you can place only an icon on a button, or only text. If you want to change the text on the button, enter this text in the field Name(by default, this field displays the name of the macro).
You can place a different icon on the button by selecting the image you like in the special panel. To display a set of standard icons, select the command in the context menu Select an icon for a button and click on the icon you like. If none of the pictures suits you, you can select the icon that is closest in design, and then correct it in a special editor. To open a window Button editor, select the command in the context menu Change the icon on a button.
Finally, you can simply copy the icon you like from another button. To copy an icon from one button to another:
Open the Customize dialog box and highlight the toolbar button from which you want to copy the icon.
Select the Copy button icon command from the context menu.
Select the button whose icon you want to change.
Click the Edit Selected Object button in the Options dialog box.
Select the command from the context menu Insert icon for button.
You can use the button properties dialog to change the tooltip text and set shortcut keys.
To change the tooltip text for a toolbar button:
In setup mode, highlight the button on the toolbar. In the Options dialog box, click Edit Selected Object.
Select the Properties command from the context menu. The Button Properties dialog box appears.
Enter the desired text in the field Tooltip.
You can place any others on the toolbar you created. useful buttons, not just buttons for running macros. When you're done creating the toolbar, you can move it to an empty space at the top of the screen (or place it at any other edge of the screen).
It is very important to provide the application with a user-friendly interface. Access allows you to provide the database with all the necessary interface elements - menus and toolbars, forms for data entry, keyboard shortcuts for quickly calling macros, etc. All this makes working with the database convenient and gives the application a finished look.
Property OrderBy(Sort Order) determines how records in a form, query, report, or table are sorted. Returns a read-write value of type String.
expression.OrderBy
expression(Necessarily). An expression that returns one of the objects from the Usage list.
Notes
Property OrderBy is a string expression representing the name of the field or fields by which you want to sort the records. When using multiple fields, their names are separated by commas (,). Property OrderBy serves to save the sorting value and apply it later. Property values OrderBy are saved along with the objects in which they are created. They are loaded automatically when you open an object, but are not automatically applied.
When setting a property OrderBy by entering one or more field names, entries are sorted alphabetically. Similarly, Visual Basic for Applications (VBA) sorts these fields in ascending order by default.
If you want to sort records in descending order, enter DESC at the end of the string expression. For example, to sort customer records by contact name in descending order, set the property OrderBy value "ContactName DESC".
Property OrderBy can be set using the object's properties page, macro, or VBA.
For property reports OrderByOn(Sort Enabled) must be set to Yes to apply the sort order specified by the property OrderBy object. For forms, select the field by which records will be sorted, and on the tab Home in the group Sorting and Filter Click the desired sort order. Property OrderByOn can also be set for forms or reports using VBA.
If you set the property OrderBy for an open report, event processing procedures will be launched Close(Closing) and Open(Opening) the report.
Note: When an object is created, it inherits properties RecordSource(Source of records), Filter(Filter), OrderBy(Sort Order) and OrderByOn(Sorting enabled) of the table or query from which it was created. For forms and reports, legacy filters are not automatically applied when you open an object.
Rows in a table can be sorted according to the contents of one or more columns. To do this, select the field by which you will sort and click the Sort Ascending or Sort Descending button on the toolbar.
When searching for a certain value, Access finds and displays the corresponding record. But if several records satisfy a given criterion, then for convenience it becomes necessary to display only them on the screen using the sorting and filtering buttons located on the toolbar (or the Filter and Sort commands). Sort) from the Records menu), you can display on the screen only the records necessary for work, and also quickly sort them in the desired order. To sort the entire table, two sort buttons are used, and to select specific records, three filter buttons are used.
Quick sort
Sometimes you need to sort records in a specific sequence. The toolbar sort buttons allow you to sort columns in ascending or descending order. First, select the fields to sort, and then click the Sort Ascending (A-Z) or Sort Descending ( Z-A) (Sort Descending (Z-A))
To select a sort field, just place the cursor in it at the level of any record. After that, click on the corresponding button, and the data will be instantly sorted
If you want to sort data by multiple fields, select multiple columns. To do this, first select one column and then press
To restore the original order of the records, use the Records^Remove Filter/Sort command.
Filtration
Filtering allows you to view only certain records in a form. Select the data you want to filter by and click the Filter by selection button on the toolbar. To change the filter, click the Change filter button. To set a more detailed filtering condition, select the Filter command in the Records menu and the Advanced filter subcommand. To apply a regular or advanced filter, click the Apply filter button on the toolbar .
5. Creating queries in the ms Access environment
Query design process using the standard query window toolbar in Designer.
Requests
Queries provide fast and efficient access to data stored in a table.
When creating a table using MS Access, you may notice that the program does not allow the use of calculated fields. There is also no way to sort by something other than the primary key.
Thanks to queries, you can sort or evaluate expressions. For example, bringing together data from related tables.
When a query is executed, MS Access reads data from tables and displays the execution result in table view. Please note that the result of the query is not saved. Data is always stored in tables. In a query, MS Access only stores instructions about how the data should be organized as a result of the query.
For some queries, you can make changes to the query result displayed in Datasheet view. These changes will be reflected in the source tables. Based on the request, you can develop a form or report.
Sorting table records
In a table, data is always sorted by primary key. To organize another way of sorting data, a query is used. To create such a query, select the icon in the database window “ TABLE” and specify the table that needs to be sorted. Icon selection “ NEW REQUEST” , located on the toolbar, opens a dialog box for creating a new query for the database table specified in the window. As a result of selecting the icon “ NEW QUERY” in the dialog box a list of all field names of the source table is displayed, at the beginning of which there is a “*” symbol. Tow the sprocket to the QBE request form. It is a placeholder character for all data fields. In addition, drag into the request form the data field by which the sorting should be carried out.
For this field, select a sorting method from the Sorting list. Select the “RUN” icon and MS Access will display the query result on the screen, displayed in table view, with the data sorted as required.
Queries with search criteria
To select data from tables based on a specific condition, MS Access uses queries with search criteria. Having previously set the selection condition, the result of the query will be a set of data that satisfies the generated criterion.
To create a request, go to the database window, select the “QUERY” icon and click the “Create” button, and then the “New request” button. In the next dialog window, select one of the database tables and click the "Add" button. MS Access will place a list of fields of the specified table in the query window. You can now select one or more fields and drag them into the QBE request form.
In the "Selection condition" line you can now enter selection criteria. To represent an arbitrary number of characters, use an asterisk (*) as a wildcard character, or use a question mark (?) to represent a single arbitrary character. Formulate your request criteria. For example, if only the criterion "A*" is specified for a text field, then the result of the query will be displayed only those records whose text field begins with the letter "A" (or "a").
If you need to find a combination of several values, you must enter them in the “Selection condition” line, using the logical operators AND and OR as a separator. In addition to the usual comparison operators, MS Access provides three special operators useful for selecting data: BETWEEN, IN, LIKE.
Queries with parameters
It is not always possible to decide at the stage of creating a query exactly what values MS Access should look for, and sometimes a flexible selection condition is required, which can change depending on the current situation. Therefore, instead of entering a selection condition directly into the QBE form, you can include a parameter in the query, in which case MS Access will prompt for specific selection conditions each time before executing the query.
To set the parameter, you must enter a name or phrase enclosed in square brackets () in the “Selection condition” line. What is enclosed inside the square brackets is treated by MS Access as the parameter name. This name is displayed in the dialog box when you run the query, so it makes sense to use a meaningful phrase as the parameter name. You can specify multiple parameters in one request; in this case, the name of each parameter must be unique and informative.
By default, MS Access converts the parameters entered in a query to a text data type. However, because text fields cannot be compared with date fields, the data type of the parameters must be set precisely. To do this, select the command Query/Parameters from the menu, causing MS Access to open the "Query Options" dialog box. Enter the parameter without square brackets in the dialog box and select the desired data type.
Now, to run a query, you must first enter a parameter value, and the query result will contain only those fields that satisfy the specified parameter value.
Calculated fields in queries
It is possible to set a calculation on any table fields and make the calculated value a new field in the recordset. For this purpose, many functions built into MS Access are used. You can create a field in a query using arithmetic operations on table fields, or you can create a new field as the result of concatenating text fields or character constants.
Creating a calculated field is done by simply entering a calculation expression in a blank column of the QBE query form. You just need to create the appropriate query using the field names. Once the input is complete, the expression will look like this:
Expression 1: [Field1] “operator” [Field2]
Only field names are enclosed in square brackets. MS Access automatically uses "Expression 1" as the name of the calculated expression. This name can be changed to something more meaningful.
When building complex expressions, MS Access suggests using a utility called the “Expression Builder”. To go to the “Expression Builder” you need to click on the empty field in the QBE form, and then click on the “Build” button on the toolbar.
Final queries
The resulting queries differ significantly from the usual ones. They can use two types of fields. There are fields by which data is grouped, and fields for which calculations are carried out. Additionally, you can include more fields to define query conditions.
In order to compose a final request while in the mode Query constructor, you should select the “GROUP OPERATIONS” icon or use the command Type/Group Operations. As a result, the line “Group operation” will appear in the request form . Then, for the corresponding field from the list, you must select the “Grouping” function.
Multi-table queries
Once you've considered the capabilities of queries based on a single table, it's easy to view combined data from multiple related tables. Selecting data from multiple tables is especially useful when creating forms and reports.
First, open the window Query constructor and add tables that contain the required information. If previously using the command Connections a connection has been established between the tables, MS Access will detect this automatically. This relationship will be displayed as a line drawn between the fields of the tables. If there is no relationship between the tables, you must establish one.
Next, you need to define the communication parameters. Point to the connector and select a command View/Merge Options. This will open a dialog box that offers three options for defining communication parameters.
After this, drag the fields from the main and subordinate tables into the request form. Typically, there are more records containing information about objects than records about their categories. Therefore, MS Access displays the category as many times as necessary. Therefore, if ten different objects correspond to one category, the category will be repeated ten times.
Note: The main table fields in a one-to-many relationship cannot be changed as a result of a query displayed in Datasheet view. Additionally, these fields may contain duplicate values.
Any select queries can be converted to create table queries. In this case, a table is created that contains the query result that is currently returned.
Cross requests
MS Access supports a special type of summary query called a cross-query. This query allows you to see the calculated values in a crosstab, similar to a spreadsheet.
Imagine a product table that contains three data fields: Type, Supplier, and Price. For each category of goods, it is required to determine the amount of prices of goods indicating the supplier.
Place it in the window Query constructor"Products" table. Then enable the "Group Operation" line by selecting the command View/Group operations. After that, drag the "Type" data field into the request form. For this field, select "Grouping" from the list.
Drag the "Supplier" data field into the second column of the request form. Also select Grouping for this column.
The last thing to add to the request form is the “Price” field. For this field, select "Sum".
When a query is executed, its result is displayed in table mode, the first column of which will contain all types of goods, the second column will contain supplier numbers for each type, and the last column will contain the sum of prices of goods of this type and this supplier.
Now you need to convert the final query into a cross-query. To do this, select the command Query/Cross.
Selecting this command results in the replacement of the “Output to screen” line in the request form with a new line “Crosstab”. All other parameters remain unchanged. The Type field of the source table will be used as the row header. Therefore, from the Crosstab list box, select the Row Header item. For the Supplier column, select Column Headers from the Crosstab list.
The sum of the prices of goods must be placed in the cells of the crosstab. Therefore, for this field, you should select the Value item from the Crosstab list.
When you run a query, the result is displayed in table view, with category numbers serving as row headings. In addition, for each supplier the sum of the prices of goods in the cross-tab is placed in a separate column.
Note: When creating a crosstab, you can select only one field as the table content and only one field as the column header. You can also specify several fields of the source table as row headers.
Removal requests
All of the above queries are select queries, which only allow you to select and view data from tables. The following requests are action requests that allow you to modify stored information.
A delete query is used to remove specific data from tables. Before constructing a deletion request, you should consider which records need to be deleted. The most reliable method is to first create a selection query.
In mode Query constructor select the table that will be deleted later. Then define your criteria and see the query result displayed in Datasheet view. Quickly switching between design and run modes is usually done using the icons of the same name located on the toolbar.
Only if the query results in only records to be deleted should you convert the select-query into a delete-query.
To do this, select the command from the menu bar Request/Delete. After this, MS Access turns off the “Sorting” and “Display” lines in the request form. For delete requests, these lines are not needed. In addition, for all columns of the request form, the text “Condition” is displayed in the new “Delete” line. However, the criteria do not change.
If you execute a query by selecting the “EXECUTE” icon, MS Access will not display the result of the query on the screen. Instead, a message will appear indicating how many records will be deleted in the source table. You can interrupt the deletion process by clicking the "Cancel" button. However, clicking OK permanently deletes records that match the query criteria.
Update requests
An update request allows you to update some stored information that is currently out of date.
First, create a new selection query and enter criteria to select the records to change. Only if the result of the query meets the specified requirements should it be converted to a replacement query. To do this, select the command Query/Update. This will turn off the “Display” and “Sorting” lines in the request form. Both of these lines are not needed for the replacement request. Instead, a new line "Update" is included. New expressions for the records to be replaced should be entered in this line. Note that the data type of the expression must match the data type of the source table field.
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 sort order for numbers, text, and special characters depends on the selected language settings and standards for of this 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. In cases where you need to find one or more records based on a piece of text included in one of their fields, you can use standard means Microsoft Search Office Access. 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. 
