The analysis done on a table or a form is usually temporary and may be lost when you close the table or the form. Other than that, most data analysis operations you can perform on a query are also available on tables and forms.
Data analysis can be performed on tables, queries, and/or forms. To analyze data, display the table or query in Datasheet View, or the form in Form View. To assist you with data analysis, when a table, a query, or a form is displaying in Datasheet View, the Ribbon is equipped with a section titled Sort & Filter in the Home tab:
Also, when a table or a query displays in Datasheet View, each column header displays a down pointing arrow button on its right side:
There are two main aspects involved with data analysis: what you provide to the users and what the users may decide to do with data. In some cases, you may want to create objects such as forms and/or reports that isolate records instead of showing all records. As we saw in the previous lesson, these types of forms and reports can be based on a query or a SQL statement. In some other cases, you can create queries that your users would run to get a fixed list of records based on a rule of your choice. For these and many other reasons, you should be aware of what your users can do and what they should not do.
Sorting records consists of rearranging them in alphabetical, incremental, chronological, or Boolean order. Sorting can be performed on fields that have numbers, strings, date, time, or Boolean values. Fields can also not display any value but must be sorted also.
When performing data entry, a user may be handed a paper-based list of records, a user may receive the information over the phone, or the database system may receive values electronically. In some cases, the needed values are available. In some cases, some values may be missing. When introducing data entry, we saw that the user could leave those fields empty if the table allows it. As a result, a table, query, or form may end up displaying empty fields. These are also referred to a null field or null values. In reality, or behind the scenes as far as an application is concerned, there is a difference between a null field and an empty field.
A null field is one that has never received any value previously. For example, during data entry, if the user encounters a field for the first time, the field not having a value, and the user skips it, the field is considered null. On the other hand, if the user comes to a field, enters a value in it, then deletes that value (at that same time or at another time), the field is considered empty. This means that the field has had a value before but that value has been lost, deleted, or removed. To make our discussion easier in these lessons, we will interchangeably use the expressions "null fields" and "empty fields" to mean the exact same thing.
When sorting records in alphabetical, incremental, or chronological order, the empty fields always come first. This would allow you to find out which fields have not been filled; that is, what records are incomplete or missing. For this reason, you can sort records on a field, not because you want to get the alphabetical order, but because you would like to find out what record(s) need(s) to be completed; that is, what records are empty.
To sort records, you must specify the field that would be used as the reference. To do this, on the table or query, click a field under the column of your choice. To arrange the list in alphabetical, incremental, chronological, or Boolean order, on the table or query, click the column header or a field under the column. Then, in the Sort & Filter section of the Ribbon, click Ascending . You can also right-click to sort the list.
Besides the regular arrangement of records, you can also sort records in reverse alphabetical, incremental, or chronological. When you do this, the empty fields will always come last in the list.
When you have finished sorting the records, it is sometimes important to reset the table before continuing unless you want to keep the list sorted.
When you have sorted the values of a column, the down-pointing arrow button on the right side of the column header becomes equipped with an arrow:
You can keep the list sorted while you are performing other operations. It would remain sorted until you decide to put it back to the way it was previously.
In Lesson 9, we saw that a form could display in Datasheet View, like a table or a query. With that type of form, you can apply the same techniques you use to sort records for a table or a query. If you display a form in its regular and most usual format, where it displays one record at a time, you can still perform the same sorting operations as done on a table. The main difference is that, in Form View, the form shows only one record at a time. We also saw that a tabular form resembled a datasheet except that each field is in its own control and the tabular form displays as many controls as its vertical size allows.
When you sort the records in incremental, alphabetical, or chronological order, the empty records would come first. This means that, in the Form View, the first record with the empty field on the control you selected would come first.
In most cases, you should "unsort" a list before continuing. To put the list back in the sequence it previously had:
So far, we were just selecting the necessary fields for our data analysis. In some circumstances, you may want to set a limit on the number of records to display or make available to the user. To do this, you must create a rule and ask Microsoft Access to apply it to a set of records. The rule works like a funnel that decides what to let through and what to retain. The rule is also called a criterion. For example, you can set a criterion that asks a query to consider the list of all students in a school but to restrict the list only to female students.
A filter is a criterion or a set of criteria that must be applied to a set of records to create a list of records that abide by a common rule. Filters can be used to isolate records on a table, a query, a form, or a report. There are differences on the way each type of value handles it.
If you have a list of records where fields are empty in a certain column, you can filter the list so that you would get the empty fields only, or you can create a filter that would produce the non-empty fields only.
To filter records that display on a data sheet, first decide what column to use. Then:
After clicking, the table or query would display only the records that are empty.
After clicking, the table or query would display only the records that are not empty for that particular column.
When a table, a query, or a form is filtered, in the Sort & Filter section of the Ribbon, the Toggle Filter button is highlighted . Also, the bottom section of the table, query, or form displays a Filtered button . If you perform another filter on the list, only the selected records would be considered. This means that you must decide whether you want the new filtering to apply to all records or only to the new ones. If you want to use all records of the list, you must first remove the previous filter.
To dismiss the previous filtering operation:
In this and the next lessons, we will see different ways to examine the values stored in a database. Every time you finish visually analyzing data, you can dismiss the result or save it. If you do not save the result, all the analysis will be lost. The best way to save the results of data analysis is by saving them in a query. In the previous lesson, we saw that the advantage of using a query is that it could be saved and its data reviewed over and over again. Besides the ability to save sorted and/or filtered lists, queries provide advanced techniques of performing data analysis. Because they use the SQL, they use a syntax that is not directly available to tables, forms, and reports. In fact, queries provide a good alternative to creating the record source that can be used to populate forms and reports.
Most fields of a database contain strings. This allows you to sort them in alphabetical order. When sorting the strings of a column, Microsoft Access refers to the language of the computer set in the Control Panel. That language defines its alphabetical rules. This also implies that the rules in one language are not necessarily the same in another language.
To sort the records of a table or query, you must specify the field that would be used as the reference. To do this, on the table or query, click a field under the column of your choice. To arrange the list in alphabetical order, click the column header or a field under the column.
Consider the following table:
When many records have the same value for the Property Type column, you can ask Microsoft Access to sort the records based on the type of property. In this case, as seen previously, the empty records would come first. Then, the records that have Condominium as value would display, followed by the records that have Single Family, and so on. When sorting the records, the table, query, or form must still keep each record complete. Therefore, after displaying the field in the order, its corresponding fields are displayed in their equivalent columns. In this case, the property number, the condition, the city, and the state corresponding to the property type would display on the same row with the property type. For example, notice the property whose type is Condominium, its property number being 200417, its condition being Excellent, its city being Germantown, and its state being MD. When the records have been sorted alphabetically, the whole record is kept the same, just at a different position:
To sort the records, identify and click the column header, a cell under a column, a control, or its label. Then, in the Sort & Filter section of the Ribbon, click the Ascending button .
You can also right-click to sort the list:
Besides the regular arrangement of records, you can also sort records in reverse alphabetical order. To do this, click the column header or a field under the column header. Then, in the Sort & Filter section of the Ribbon, click the Descending button .
You can also right-click to sort the list in reverse order:
When you have finished sorting, it is sometimes important to reset the table before continuing unless you want to keep the list sorted.
To sort records on a form in the Form View or the Tabular View, click the control or its accompanying label. Then, in the Sort & Filter section of the Ribbon, click the Ascending button .
As mentioned for the table or query, to arrange the strings in alphabetical order, right-click the control or its label and click Sort A to Z.
To sort the records in reverse alphabetical order, click the control or its label and, in the Sort & Filter section of the Ribbon, click the Descending button .
You can also right-click. To arrange the list in reverse alphabetical order, right-click the control or its label and click Sort Z to A.
In SQL, to sort a field in ascending order, you can include the ORDER BY clause in your statement. The formula to follow would be:
SELECT What FROM WhatObject ORDER BY WhatField;
The field used as the basis should be recognized as part of the selected columns. Imagine you have created a list of staff members made of their first and last names in a table named StaffMembers. If you want to order the list in alphabetical order based on the LastName column, you would use a statement such as:
SELECT FirstName, LastName FROM Employees ORDER BY LastName;
If you use the * operator to include all fields, you can order the list based on any of the table's fields. Imagine that you have created a query that includes all fields. The following statement would list the records of the Employees table in alphabetical order based on the LastName column:
SELECT * FROM Employees ORDER BY LastName;
By default, records are ordered in ascending order. Nevertheless, the ascending order is controlled using the ASC keyword specified after the based field. For example, to sort the last names in ascending order of a query that includes the first and last names, the above statement can also be written as follows:
SELECT FirstName, LastName FROM Employees ORDER BY LastName ASC;
The second statement can be written as:
SELECT * FROM Employees ORDER BY LastName ASC;
If you want to sort records in descending order, use the DESC keyword instead. It produces the opposite result to the ASC effect. To sort records in reverse alphabetical order, the above two statements can be written as:
SELECT FirstName, LastName FROM Employees ORDER BY LastName DESC;
The second statement can be written as:
SELECT * FROM Employees ORDER BY LastName DESC;
Imagine you have a table where some records can be recognized as belonging to a category. For example, in a list of persons, you may have a column that shows each one's gender. Obviously different people would have different values but a group of people would have the same value and another group would have another same value. Based on such a field, you can create a list that includes only the people who share one of the values. To do this, you would filter the values. Microsoft Access provides various techniques to visually filter records.
To filter records that display in a data sheet, first decide what column holds the value you want to filter by. Then:
After clicking, the table or query would display only the records that share the value in the field:
If you have a set of records with a string-based column, you may want to create a list of records that share a portion of a string, also called a substring. Microsoft Access allows you to create a list based on strings that start with a certain substring, that end with a certain substring, or that include a certain substring. To start, first select the substring. Then:
When using the Contains option, you must first select a string or a substring. In some cases, you may want the string to contain one or more characters or substrings. To exercise an advanced level of control on the substring or the combination of (sub) strings, right-click any field under the column, position the mouse on Text Filters and choose one of the options. The Custom Filter dialog box would come up:
When using the Custom Filter dialog box, you can type an exact string or use some characters to create an approximate string.
If you had selected:
As opposed to filtering records on a value, you can filter the records that do not follow, or are opposed to, a certain rule. This is referred to as filtering by exclusion. To filter the records that are not conform to a criterion:
After clicking, the table or query would display the records that do not follow the specified criterion.
You can also filter excluding a substring. That is, you can get a list of records that do not have a certain substring. To do this, first select the substring. Then: