Record Sorting on Tables

By default, if you have a first field on a table to register incremental numbers (AutoNumber) as the user enters records, the records are organized in the order they were entered. They typically follow the ordinal numbers defined in the first field, as numbers are counted from the lowest to the highest. One way you can change this order is to rearrange the list of records based on a field of your choice. Rearranging the order of records is referred to as sorting.

To sort records, you must first select the field that would be used as the reference. To do this, you can click a field under the column of your choice. You have two options. To arrange the list in alphabetical order, you can ask the table to display its records in ascending order. To arrange records in alphabetical order following the field of your choice, you would use the Sort Ascending option, which is available from the main menu (Records -> Sort -> Sort Ascending).

Besides the regular arrangement of records, you can also sort records in reverse alphabetical order. This is done using a column as basis using Sort Descending. Both techniques of sorting are also available when you right-click the desired field on the table.

When you have finished viewing, it is sometimes important to reset the table before continuing unless you want to keep the table sorted.

If you sort records on a column whose fields are not all filled, which means some records are empty, the empty records would display first before the other records start in alphabetical order. This would allow you to find out which fields have not been filled; that is, what records are incomplete or missing. For example, in our students registration form, imagine you want to find out what students do not have the emergency name (which means if something happens, the user wouldn't know who to call) (of course, an alternative would be to make such a field Required), you can sort the emergency name. Here is an example:

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. This could be used to find out what student doesn't have an e-mail address yet.

Besides the sorting of text fields, you can also arrange a list of records by seniority. This is usually done by sorting a date field. In the case of a student list, you can sort records based on the date of birth. When sorting a list of records based on a date, the year is first considered. This means that, if two records have different years, the record with the oldest date would display first (of course, to get the youngest date first, you can sort in descending order). If two dates have the same year value, the month would be considered. The date with the earliest month would display first. If two dates have the same year and the same month, then their day values would be considered.

By right clicking, you can sort any field in the table or form. When a field has a combo box with two values, such as the M/F field for the Gender column, you can sort a Boolean column whose fields are equipped with a check mark. If you sort such a field in ascending order, the True, On, Yes or 1 records would display first, followed by the opposite records.

Practical Learning: Sorting Records on Tables

1. Open the Students1 table in Datasheet View
2. To view the list of students in alphabetical order based on their last name, click any field under the Last Name column
3. On the main menu, click Records -> Sort -> Sort Ascending
    
   
4. Notice that some students have the same last name
5. To see a list of students by date of birth starting with the youngest, click any field under the Date of Birth column
6. On the Table Datasheet toolbar, click the Sort Descending button
7. To restore the table to its original arrangement, right-click any field under Student ID and click Sort Ascending
8. Close the table. When asked whether you want to save the table, click No

Record Sorting on Forms

When reviewing forms, we saw that a form can display in Datasheet View, like a table or a query. With that type of form, you can apply the same techniques we used to sort records on a table. 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. This allows you to view one record at a time.

Practical Learning:  Sorting Records on Forms

1. In the Database window, click the Forms button and double-click the Students form to open it in Form View
2. Observe the last name of the first record. To navigate through the records, click the Next Record button a few times
3. To get to the first record, click the First Record button 
4. To jump to a specific record, select the number in the Record Number text box and type 42:
    
5. Press Enter. Notice that the table has jumped to record 42
6. To navigate backward, click the Previous record button 
7. To return to the first record, press Ctrl + Home
8. To organize the student's Last Names alphabetically, click the Last Name field on the form
9. On the main menu, click Records -> Sort -> Sort Ascending:
    
10. Notice that the records are arranged alphabetically based on the students last names
11. To arrange the list of students by age starting with the youngest, click the Date of Birth field
12. On the Form View toolbar, click the Sort Descending button 
13. To sort records using the context-sensitive menu, right-click the Gender field and click Sort Ascending
14. To dismiss the sorting of records, on the main menu, click Records -> Remove Filter/Sort
15. To sort records based on a Boolean field, we will find out who (students) lives in a single parent home.
16. Right-click the Single Parent? label or its check box and click Sort Ascending
17. Navigate through the records. Notice that the records of students who live in a single-parent home display first
18. When you have finished viewing, to remove the sorting, right-click anywhere on the form and click Remove Filter/Sort 
19. Close the form

Record Filtering on Datasheet and Form Views

Introduction

So far, we have built fairly simple queries that consisted of displaying all records that are part of a table. The only thing we were doing was to select the necessary fields. 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 (also called a Recordset). 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 Recordset 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.

Records Filtering Using Selections

While sorting is used to rearrange data in alphabetical, incremental, or decremental orders, filtering allows you to isolate data. For example, when you order the Gender column alphabetically, you get a list of girls first (F for Female), then the boys (M for Male). Using a filter, you can create a list that would include only one of these categories.

Fields on a table are organized in categories. For example, in the Last Name, all records are meant to represent a string as people's last name. The content of such a field can be used as a basis for selecting records. It is another way of asking a table to isolate records that share the exact same content. This technique of isolation is referred to as Filter By Selection.

To filter records that display on a data sheet, based on a selection, you can first click a field that would be used as the basis. Then on the main menu, you can click Records -> Filter -> Filter By Selection. Imagine you have a list of videos in a Video Collection database and stored in a table named tblVideos:

Imagine you want to have a list of only movies that are rated R. To do that, you can click an R field and filter by selection:

Unlike the sorting techniques, filtering hides data. In order to execute another filter on all records, you must remove the previous filter, unless, as we will see shortly, you want to combine filters. The context menu provides the same options available on the toolbar.

You can also filter records using a Boolean field that is equipped with check boxes. For example, on the Single Parent? column of the Students table, you can ask the table to show only the list of students who live in a single parent house. To do this, you can filter by selecting a record that is checked.

Practical Learning:  Filtering Data By Selection

1. Open the ROSH database and, from the Tables section of the Database window, open the Students1 table
2. To filter records by selection, under the Gdr column, click any field that has M 
3. On the main menu, click the Records -> Filter -> Filter By Selection
4. Scroll down in the list and notice that the table displays only male students
5. Also notice that the bottom section of the table displays Filtered
6. To remove the filter, on the main menu, click Record -> Remove Filter/Sort
7. To view only the records whose ZIP Codes are known, click any field under ZIP Code
8. On the Table Datasheet toolbar, click the Filter By Selection button
9. Notice that the list displays only records that have an entry in the ZIP Code
10. To remove the filter, on the Datasheet toolbar, click the Remove Filter button
11. Close the Students1 table. When asked whether you want to save the changes, click No
12. On the Database window, click the Forms button and double-click the Students form
13. To get only the list of girls, click the Next Record button a few times until a record displays F in the Gender field
14. Right-click F and click Filter By Selection
15. Navigate through the records and notice that the form now displays a list of only female students. To indicate this filter, the number of records displays (Filtered):
     
16. To refresh the form, on the main menu, click the Records -> Remove Filter/Sort
17. Navigate to a field whose Single Parent? check box is checked
18. For an example of filtering a Boolean field, right-click a Single Parent? field (one that is checked) and click Filter by Selection
19. Notice that the form is now displaying a list of only the students who live in a single parent home
20. To remove the filter, right-click anywhere on the form and click Remove Filter/Sort

Filter By Exclusion

Instead of using a field's content as a basis for inclusion, you can ask the table to deny or hide the records that respond to a certain field. This is referred to as filtering by exclusion. In Microsoft Access, this is done using Filter Excluding Selection. Imagine that, on your Videos table, you want to get a list of movies that are not rated R, you can right-click an R field in the Rating column and click Filter Excluding Selection.

Practical Learning:  Filtering Records By Exclusion

1. Navigate to a record whose State field displays MD
2. To get a list of the students who live outside of Maryland, right-click MD in the State field and choose Filter Excluding Selection
3. Notice that the form displays a list that excludes MD 
4. When you have finished viewing, on the Form View toolbar, click the Remove Filter button 
5. Close the Students form

Filter By Form

The techniques we have used so far to analyze our data consisted of looking for a particular field content as a basis for our filtering. Microsoft Access provides another technique that allows you to select a criterion from an empty field. Using this technique, the whole table is emptied and all records get hidden. You can then select your criterion from the column of your choice. Although the fields appear empty, each column equips its first and only field with a combo box that displays a list of all records of that column, thereby allowing you to select, which one of the fields responds to your choice. This technique is referred to as Filter By Form.

Practical Learning:  Filtering By Form

1. From the Tables section of the Database window, open the Students1 table
2. On the main menu, click Records -> Filter -> Filter By Form
3. Click the empty box under Gdr.
   To get a list of female students, click the arrow of the Gender combo box and click F 
4. To apply the filter, on the main menu, click Filter -> Apply Filter/Sort 
5. Scroll down in the list and notice that the table displays only girls
    
6. To remove the filter, on the main menu, click Records -> Remove Filter/Sort
7. Close the table. When asked to save it, click No
8. From the Forms section of the Database window, double-click Employees
9. On the Form View toolbar, click the Filter By Form button 
10. Delete the value in the Empl # field
11. To get a list of only the staff members who live in Maryland, click the State label or field and notice the button that appears
12. Click the button on the State field and click MD
13. On the Form View toolbar, click the Apply Filter button 
14. After viewing the records, to restore the records, on the Form View toolbar, click the Remove Filter button 
15. Close the form

Data Analysis With Operators

Introduction

Queries are meant to provide advanced techniques of performing data analysis. Because they use the SQL, they use a syntax that is not directly available to tables and reports. In fact, queries provide a good alternative to creating the record sources that can be used to populate forms and reports. To make this effective, it is a good idea to know what the SQL has to offer.

To perform data analysis, besides the techniques of selection, exclusion, and form we have used so far, you can use operators. We have already reviewed some of the operators used

throughout Microsoft Access. Such operators can also be used when filtering records. Besides those, the SQL provides additional operators to further filter data.

To provide a more refined criterion to filter data, the table and form can present a special text available from right-clicking the object. This is presented as Filter For. When the text box displays, you can use operators to write an expression and apply it as the criterion.

Advanced Filter By Selection

All of the techniques we used to filter data by selection, exclusion, and form on tables are also available on queries. As done on tables and forms, such criteria do not get saved. This is used to preserve data even if a filter was previously applied to an object. If you want to create a list that permanently reduces the number of records available, you can use a query. Based on its structure, a query uses operators to filter data and saves the criteria.

To create a query to filter records by selection, you should open it in Design View and use Criteria field to write the expression that will be applied.

To write an expression that would be used as the criterion of data filtering on a Datasheet View of a table or a query or the Form View of a form, right-click the object. Then, in the Filter For text box, type the expression and press Enter. There are a few rules you should observe:

• Each operator must be written “as is”. All operators we reviewed can be used
• To include a letter, a character, or a string, type it between double-quotes. An example is “M”. Another example would be “El Salvador”. Fortunately, if you forget to use the double-quotes, Microsoft Access would add them. In some circumstances, if you forget to use the double-quotes, Microsoft Access would not add them and the expression might fail. Therefore, to be on the safe side, always add the quotes.
• If a date or time value is part of an expression, include it between two # symbols. An example would be #12/8/94#. Another example is #05/02/04#. If you forget the # symbol, most of the time, Microsoft Access would not correct it. This leaves the responsibility on you.
• All built-in constants, True, False, NULL, etc, do not use quotes.

Of course, there are situations in which these rules can or must be applied differently. When in case, you will be appropriately directly.

Practical Learning: Filtering For

1. From the Tables section of the Database window, open the Students1 table in Datasheet View
2. To apply a filter by typing, we will get a list of the students who live in Maryland. Right-click any field in the State column, click in the Filter For: text box
3. Type =”MD”
    
4. Press Enter
5. To remove the filter, right-click anywhere on the table and click Remove Filter/Sort
6. To get a list of students who live outside of Virginia, right-click the State field, click Filter For, type = NOT "VA" and press Enter 
7. Close the table. When asked to save, click No

Data Analysis and Filtering by Comparison

Databases and other programming environments provide operators you can use to perform data analysis. We have already reviewed the logical operators used because they apply to other scenarios. These operators can also be very valuable for data analysis and/or filtering.

Comparisons are performed on Boolean, numbers, date, time, or string fields or values. To perform a comparison on a Boolean field, you can right-click it, click Filter For and type the desired value as True or as False. After pressing Enter, the database would perform the comparison and display the result. When comparing date-based or time-based values, include the date or time value between two # signs. For example, to get a list of records that occur before 1/1/1950, you would type an expression such as <=#1/1/1950#

Practical Learning: Performing Comparisons

1. The ROSH database should still be opened.
   Open the Students1 table in Datasheet View
2. To get a list of students who were born before January 1, 1988, right-click the Date of Birth field, click Filter For:
3. Type <#01/01/1988# and press Enter
4. After viewing the list, remove the filter
5. On the main menu, click Records -> Filter -> Filter By Form
6. To get a list of students who were born on or after June 30, 1988, click the empty field under Date of Birth column and type >=#6/30/1988#
7. To apply the filter, on the main menu, click Records -> Apply Filter/Sort
8. Close the table. When asked whether to save the changes, click No
9. On the Database window, click Queries and double-click the Staff Members query to open it
10. Switch it to Design View
11. To see only the people who were hired before 1995, in the Criteria row for the DateHired field, type <#1/1/96#
    This means "show me the people whose DateHired field is less than January 1st, 1996", which means anybody hired before 1996
12. Then run the query
13. To sort this list by seniority, right-click a field under the Date Hired column and click Sort Ascending:
     
14. To save this particular query, on the main menu, click File -> Save As...
15. In the Save As dialog box, set the name of the query to
    Staff Members Hired Before 1996
16. Click OK
17. Close the query
18. From the Database Window, click the Queries button if necessary. Right-click the Staff Members query and click Design View
19. To get the list of employees who live in Maryland, in the Criteria field for State, type ="MD"
20. Run the query and then switch back to Design View
21. To find out which ones of the employees do not reside in MD, change ="MD" to <>"MD"
22. Run the query and close it without saving it

Lesson Summary

MOUS Topics

Exercises

Yugo National Bank

1. Open the Customers table and sort records by State then remove the criteria
   View a list of customers who live in MD
   View a list of customers who live outside of MD
   Based on the ZIP Code, view a list of customers who live in DC (ZIP Code between 20000 and 20599). Notice some discrepancies of bad data entry
   Close the table without saving it
2. Open the Employees form
   View a list of only employees who have the capacity to create a new bank account
   View a list of cashiers
   View a list of employees who live outside of Baltimore
   View a list of employees who earn less than $16.00/hr
   Close without saving the form

Watts A Loan

• Open the Watts A Loan database and open the TypesOfLoan form
  View the types of loans that don't have a description