Create a grouping or summary report. Sorting records in a query in Microsoft Access Access forms table mode sorting is not active
GardenCo
Sorting data
Information in tables can be sorted in descending or ascending order by one or more fields. For example, you can sort the customer table alphabetically by using last name as the first sort field and first name as the second. The result is a list similar to the one found in telephone directories.
In some cases, sorting is used to group tabular data according to a specific principle. For example, if you sort your address information by zip code before printing mailing labels, the labels will be grouped according to postal service requirements.
In this exercise, you will use different ways to sort data in a table and form. The folder will be used as the working folder Office XP SBS\Access\Chap12\Sort. Follow these steps.
1. Open the GardenCo database located in your working folder.
2. Open the table Clients in table view.
3. To sort the table by the State field, click anywhere in the State column, and then click the button. Entries will be organized by region.
Advice. You can also use the commands Sort Ascending and in the menu Records or click right click mouse on the column and then click on the right team V context menu.
4. To reverse the sort order (assuming the cursor is still in the column State), click on the button Sort Descending. Entries for Oregon are now at the top of the list. In both cases, sorting was carried out alphabetically, but the data in the field City arranged randomly. Obviously, the list would benefit from ordering cities within regions.
Although the essence of sorting seems quite obvious, for a computer it is not so simple. In Access, digital data can be treated as both numbers and text. Fields that contain spaces, dashes, or punctuation in addition to numbers (as in a postal code or telephone numbers), are treated as text. In contrast, fields containing data such as price or quantity are considered numeric. When sorting text fields, all entries are ordered first by the first character, then by the second, and so on until the last. Sorting of numeric data is carried out by their values in ascending or descending order. This approach can lead to unexpected results. The following table shows the results of sorting the first column based on whether its content is text or numeric.
To sort a text field containing numbers in ascending or descending order, you need to pad them with leading zeros to the same length. For example, data 001, 011, 101 will be sorted correctly even if it is defined as text.
Advice. When sorting records by multiple fields, sorting is done sequentially, from left to right. Therefore, the sort fields must be adjacent and located in the required sequence.
5. To place a column State to the left of the field City, click on the column header and drag it to the left until between the fields Address And City no dark line will appear.
6. Since the column State already highlighted, press the key (Shift) and click on the column header City to spread the selection across both columns.
7. Click the button Sort Ascending to sort records by region and city in ascending order (cities will be sorted within regions).
Advice. When viewing records in Form view, you can sort them, but only by one field. To do this, click in the desired field, and then click on one of the buttons Sort.
8. Although the order of the columns in the table Clients doesn't really matter, still return the column State to its original place.
9. Save and close the table Clients.
10. Close the database.
There are two types of sorting that can be done: simple sort and complex sort.
Simple sorting. When sorting in Form view, Datasheet view, or Page view, a simple sort is performed, which means that all entries in the field are sorted in either ascending or descending order (but not both).
Complex sorting. If you want to sort records in Query Design view, Advanced Filter window, Report Design view, Page Design view, PivotChart view, or PivotTable view, you can perform complex sorting. This means that some fields can be sorted in ascending order, while other fields can be sorted in descending order.
Features of sorting records.
If the new form or report is based on a table or query whose sort order was saved with it, the sort order is inherited by the new form or report.
You can sort up to 255 characters in one or more query or advanced filter result fields.
The sort order depends on the language specified in the Database sort order field (on the General tab of the Options dialog box) when the database was created. If a database contains related tables from a database that uses a collation order for another language, Microsoft Access uses the collation order specified for the database that contains the relationship to the table, not the database that stores the table itself.
If a query or filter form contains an asterisk from the list of fields, then you can determine the sort order in it only by adding the fields that need to be sorted.
To sort date and time values from earlier to later, use the ascending sort order. To sort from more recent values to earlier values, use descending sort.
Numbers stored in text fields are sorted as character strings rather than as numeric values. Therefore, to be able to sort them in numerical order, all text strings must be the same length. For example, the result of sorting in ascending order text strings"1", "2", "11" and "22" will be "1", "11", "2", "22". Insignificant zeros should be added to the beginning of lines with fewer characters, for example: “01”, “02”, “11”, “22”. Another solution to this problem for fields that do not contain values Null, will use the Val function to sort the numeric values of the strings. For example, if the "Age" column is a text field containing numeric values, then to arrange the entries in this column in the proper order, you can specify the Val ([Age]) function in the Field cell, and specify the desired sort order in the Sorting cell. If you store numeric or date values only in a text field, it is recommended that you change the data type of that table field to numeric, currency, or date/time. Once you sort by this field, the numbers or dates will be in the correct order without having to enter any extra zeros.
When sorting a field in ascending order, records in which the field is empty (contains a Null value) are placed at the beginning of the list. If a field contains both entries with Null values and entries with empty rows, the sort will display fields with Null values first, followed by fields with empty rows.
The report can be sorted by 10 or fewer fields or expressions.
Click the Sort button and in the field drop-down list, specify the field for which sorting will be performed.
To ensure that records within a sorted group of records are sorted, add another level of sorting as described above.
The field or expression listed on the first line determines the first (primary) sort level. The second line of the form determines the second sorting level, etc.
Ascending sort sorts the values in order from A to Z and 0 to 9.
To change the sort order, select Starting with Z for text fields or Highest to Lowest for number fields. When sorting in descending order, the values are sorted in order from "Z" to "A" and from 9 to 0.
Grouping records in a report
Reports can be grouped by 10 or fewer fields or expressions. Grouping can be done during the report creation stage. To group records in the generated report:
Open the report in Design view.
Right-click and select Sort and Group from the context menu to open the Group, Sort and Totals window.
Click the Group button and select the field or expression for which you set grouping options in the report.
Specify the grouping direction, for example "Minimum to Maximum".
Add grand totals and subtotals for two to three report fields. To do this, in the grouping line, expand the More list and select With totals. In Fig. Figure 4.1 shows a report in Designer mode with athletes grouped by weight. Subtotals for the group (the sum of the heights of athletes with a certain weight) and the overall total (the sum of the heights of all athletes) have been added to the Report.
Rice. 4.1. Report with grouping of athletes by weight in Design mode.
Let's consider the grouping parameters shown in Fig. 4.1:
·"Minimum to Maximum" determines how values are grouped. The list of available values for this property depends on the data type of the field by which the grouping is performed.
· “Across all values” defines any valid range of field or expression values by which to group.
··"With Note Sections." Adds or removes a comment area from a group defined by a field or expression.
"Don't keep the group on the same page." Sets or disables whether a group is required to be printed on one page.
Creating a Calculated Control
Open the report in Design mode.
Add a Field control to the report data area, as described in Lab 2 (see Figure 4.2).
Open the field properties window and on the Data tab, in the Data line, call the Expression Builder (click the button with three dots).
In the Builder, specify fields and operations on them (for example, calculating specific area or relative salary, etc.).
Remove the label for the created field in the data area and specify the name of the calculated field in the report header.
Switch to report layout mode and view the results of its generation.
Rice. 4.2. Use the Expression Builder to create a calculated field in Design view.
Now let's define the structure of the report, setting the conditions for grouping and sorting records. These conditions are set in the dialog box Sorting and grouping(Sorting and Grouping), which appears when you click the button with the same name on the toolbar.
To create a group, you need to specify a report field by which records will be grouped and set property values Group header(Group Header) and Group Note(Group Footer) equal Yes(Yes). The first level of grouping in our example will be determined by the field " Client name" (Company-Name). Select this field from the drop-down list on the first line of the window Sorting and grouping(Sorting and Grouping). The field to the right of this line that defines the sort order will default to Ascending(Ascending). Set property values Group header(Group Header) and Group Note(Group Footer) in Yes(Yes). After this, a grouping symbol will appear in the first line selection area (in the window), and two sections will be displayed in the report: Group header "Name" (CompanyName Header) and Group Note "Name" (CompanyName Footer).
The second level of grouping will be organized by field " Order Code" (OrderlD). Fill in its properties in the window in the same way Sorting and grouping(Sorting and Grouping).
In addition to the fact that we have specified two grouping levels for our report, we can also define the sort order within the internal group. In the third line of the window Sorting and grouping(Sorting and Grouping) select the "Brand" (ProductName) field to sort the products in the order alphabetically. After this, the report will take the form shown in Fig. 10.19.
The grouping level by the "Order Code" field turned out to be nested. And there can be up to 10 such nesting levels in Access 2002.
For each group, except properties Group header(Group Header) and Group Note(Group Footer), which allow you to insert additional sections into the report, you can set three more properties: Grouping(Group On) Interval(Group Interval) and Don't tear(Keep Together).
Properties Grouping(Group On) and Interval(Group Interval) are interconnected and together determine how we want to group the data. Values that a property can take Grouping(Group On), depend on the data type of the grouping field. Typically, data is grouped by each field value, that is, each field value should have a separate group. Then the property Grouping By full value(Each Value), and the property value Interval(Group Interval) should be equal to 1. In our example, for the “Title” field, set these properties exactly like this.
When grouping by a field with text content, it is allowed to start a new group whenever the first or first few characters of the field value change. In this case the property Grouping(Group On) should matter According to the first signs(Prefix Characters), and the property Interval(Group Interval) will determine how many first characters should be used to form new group. If this value is 1, then a Piece Group will be created for each letter of the alphabet - this allows you to create reports like the Subject Index in books. An example of such a report is the report " List of products" (Alphabetical List of Products) in the Borey database ( Northwind).
Rice. 10.19. Report structure and sorting and grouping conditions
For a numeric field " Order Code" (OrderID) we leave the property value Grouping(Group On) equal By full value(Each Value) as it is set by default. Property value Interval(Group Interval) will remain equal to I, because we need to create a separate group for each order. However, the property Grouping(Group On) allows you to group numeric data by value ranges. In this case, you need to set the value of this property to Interval(Interval), and the property value Interval(Group Interval) equal to a number that defines the grouping range. For example, if a numeric field contains a person's age, then the value 5 of the property Interval(Group Interval) will define age ranges from 0 to 4, 5 to 9, 10 to 14, etc.
For a field of type Date/Time property Grouping(Group On) other than default By full value(Each Value) can take the following values: On years(Year) By quarter(Qtr), By month(Month) By week(Week) By days(Day) By the hour(Hour) By minute(Minute). Property value Interval(Group Interval) determines the number of units selected in the range. For example, if the property value Grouping(Group On) is set to By month, A Interval(Group Interval) is set to 1, then the data will be grouped monthly.
Property Don't tear(Keep Together) allows you to control how the group will be placed on the page. If the groups are small, then it is best to set the value of this property to Whole group(Whole Group). Then, when printing the report, the entire group, i.e. the group header, all records from the data area and the group note will be placed on one page (if the entire group does not fit on the current page, it will continue from next page), and each subsequent group will begin with new page(unless it fits entirely on the current page). If a group can contain so much data that it will not fit on one page, then it is better to set the value of this property to With the first data(With First Detail). In this case, the group header is placed on the current page only if the first row of data also fits on it, otherwise the entire group is carried to the next page.
Property Don't tear(Keep Together) for a group is associated with a similar property for a report section. These properties must be consistent. For example, if a group has the property value set to Don't tear(Keep Together) equal Whole group(Whole Group), then the property values Don't tear(Keep Together) corresponding sections of the report should have values equal to Yes(Yes). Otherwise, the property specified in the window takes precedence Sorting and grouping(Sorting and Grouping).
The report itself also has the property Inextricable group(Grp Keep Together), which is used for reports printed in multiple columns. It only applies to groups whose property value is Don't tear(Keep Together) is set to Whole group(Whole Group) or With the first data(With First Detail). If the value is set No(No), then the property Inextricable group(Grp Keep Together) is ignored. This property report may have implications On the page(Per Page) and In column(Per Column). In the first case, the group is printed without a break on the page, and in the second - without a break in the column (strip) of the report.
Comment
To cells Field/Expression(Field/Expression) windows Sorting and grouping(Sorting and Grouping) you can enter not only field names, but also expressions, i.e. grouping can be performed by the value of the expression. Entering an expression begins with an equal sign (=) just like in form and report properties.
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. 
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 Office Access search. Click the Find button on the Home feed page. 
In the “Search and Replace” dialog box, you need to specify a search pattern and select required parameters search, for example, set the area where a sample matches any part of the field, view across all records. 
To move to the next found record, click the “Find Next” button. 
After completing the search, close the window. 