Microsoft Office Tutorials and References
In Depth Information
4.4 Data queries with Sort, Filter, and Advanced Filter
The difference between a Sort and a Filter is quite simple: sorts are used to rear-
range the records of an entire database based on one or more sort keys , and ﬁlters
are used to eliminate (hide) records that do not conform to ﬁlter keys . Keys are the
data elements, database ﬁeld titles in the case of sorts and ﬁeld conditions in the
case of ﬁlters, which permit execution of the function. For example, in the case of
our payment data in Table 4.1, we can perform a sort with the primary key set to
Account . The sort process requests a primary key ( Sort by ) and also asks whether
the sort is to be executed in ascending or descending order. If we request an ascend-
ing sort, the sort will return Account records in alphabetical order with any text
beginning with A’s listed ﬁrst, B’s next, etc. Thus, Cleaning Services will be the
ﬁrst records to appear because the sort is conducted on the ﬁrst alphabetic character
encountered, which is C in this case. The opposite is true for descending sorts.
4.4.1 Sorting Data
A single key is valuable in a sort, but a hierarchy of several keys is even more useful.
Two additional keys ( Then by ) are permitted that will sort the result of each prior
sort in consecutive order. These sorts will re-sort the database after each higher level
sort in executed. Thus, a Then by sort by $ Amount will maintain the initial sort and
perform another sort within each alphabetic cluster of records. This is convenient
when an initial sort results in large clusters of ﬁrst key records that need further
Exhibit 4.13 displays the Sort of our database with the primary key as Account
and a secondary key as $Amount and the dialogue box that permits search key entry.
We begin the Sort execution by capturing the entire database range, including titles,
and then locating the Sort tool in the Data ribbon (also located in the Home ribbon
Editing groups). The range identiﬁes the data that is to be sorted. Upon entry of
primary (Account) and secondary ($ Amount) key, execution of the sort results in
the accounts being arranged in ascending alphabetical order, and within a particular
Account (Cleaning Services), the $ Amount is also sorted in ascending numerical
value. Thus, item number 11 is the last Cleaning Service to appear because it has
the highest $ Amount ($135.64).
As you can see, the Sort tool is a convenient method for quickly organizing
records for presentation. To return to the original data organization, select the Undo
arrow in the Quick Access Toolbar . Alternatively, the Item number is always avail-
able to use as a sort key. In fact, this is another good reason why we use an item
number or record number to identify each record. It permits us to reconstruct the
original data by using a ﬁeld that identiﬁes the original order of records.
As useful as sorts are, there are many situations when we are interested in view-
ing only particular records. In the world of database management, this process
is referred to as a query . As we noted earlier, the Data ribbon contains a group