Sorting, Searching, and Filtering Data

When you're writing SQL statements, sorting, searching, and filtering data is relatively straightforward: You simply specify the appropriate criteria in either the WHERE clause or the ORDER BY clause. When you want to provide this capability to your users, however, you need to provide a buffer between them and the logic of the SQL SELECT statement.

The difficulty here is that SQL logic is a poor match to natural language. When a Sales Manager asks for a listing of all the company's distributors in Wyoming and Florida, she expects a list containing both the distributors in Wyoming and the distributors in Florida. But the SQL SELECT clause is WHERE State = "Wyoming" OR State = "Florida". Linguistically, the correct usage is "and," but in the formal syntax the correct term is "or." This is a huge problem for usersSQL is just close enough to natural language to be hopelessly confusing.

Teaching users how to write SQL SELECT criteria clauses is possible to do. In fact, I know of at least one well-respected database designer who does this regularly and, by all accounts, with great success. I think it's preferable to save yourself, the documentation writers, and (most importantly) the users the hassle of directly using SQL syntax by providing a more intuitive interface.

Fortunately, you're not on your own here. Microsoft Access provides examples of interfaces for constructing SQL criteria clauses. While it isn't always appropriate to copy one of these user interface mechanisms directly to your own systems, these mechanisms at least provide a place to start, which is how I'll treat them here. While the Microsoft Visual Studio interface doesn't support any of these mechanisms, you can implement most of them in Visual Basic code with a little work.

Sorting Data

Access provides an excellent sorting interface through the Sort Ascending and Sort Descending commands. Users click in the control by which they want the data sorted, and then select the appropriate command from the Records menu or on the toolbar. It's hard to think of a simpler interface.

Filter By Selection

When it comes to filtering data, the simplest interface Access provides is the Filter By Selection command and its partner, Filter Excluding Selection. The interface for these two commands works very much like Sort Ascending and Sort Descending. If a user either selects the contents of a field on a form or places the insertion point in the field, and then chooses the Filter By Selection command, Access filters the form's underlying recordset to include only those records with a value that matches the selected field value exactly. In other words, the SQL WHERE condition is <fieldname> = <control value>.

If a user selects only the first part of a field's contents, Access limits the recordset to those records with a field value that begins with the selected characters. If a user selects the first three characters of the product name "Chartreuse verte", for example, the equivalent SQL WHERE clause would be WHERE [Product Name] LIKE "Cha*". In the Northwind sample database, this query returns records containing the product names Chartreuse verte, Chai, and Chang.

If a user selects any other characters within the field, Access returns all the records that contain the selection anywhere within the field. If a user selects "ar" in the previous example, for instance, the equivalent SQL WHERE clause would be WHERE [Product Name] LIKE "*ar*". In the Northwind sample database, this would return the 10 records shown in Figure 20-1.

Filter By Form

Filter By Selection is a simple, elegant interface that's easy for users to learn, but it limits them to filtering on a single field. Users can issue additional Filter By Selection commands to progressively narrow the data set, but this can get tedious. For users who want a more powerful filtering interface, Access provides the Filter By Form command. Figure 20-2 shows the Customers form from the Northwind sample database after a user has chosen the Filter By Form command from the Records menu.

In a Filter By Form window, users can set filter values for multiple fields on multiple tabs in the window. The values on the Look For tab will be combined with a logical AND, while those on separate Or tabs will be combined with a logical OR. This approach doesn't completely eliminate the differences between the linguistic and formal uses of AND and OR, but it comes pretty close.

By default, the Filter By Form window displays each text box from the source form as a combo box that contains all the current values for the field. You can turn this behavior off by setting the text box control's Filter Lookup property to Never. If the Filter By Form window displays a combo box, qualifying records must match the value a user selects exactly. If a text box is displayedthat is, if you set Filter Lookup to Neverusers can either enter a value that must be matched exactly, or enter an expression such as LIKE "CHA*" or IS NOT NULL. Your decision on whether to use combo boxes in the Filter By Form window depends on the size of the underlying recordset (you don't want users to wait while Access populates a 100,000 item combo box) and the flexibility your users require.

Advanced Filter/Sort

Filter By Form, or an interface based on it, is sufficient for the majority of situations. However, if your users are familiar with Access' query design, or if you're using the Filter Lookup property to allow users to select values from a combo box in Filter By Form and also want them to be able to enter criteria expressions, the Access Advanced Filter/Sort window, shown in Figure 20-3, can be useful.

The Advanced Filter/Sort window provides a subset of the functionality provided by the Design View for a query: It controls only the WHERE and ORDER BY clauses of the SELECT statement. It does not provide the ability to change the basic structure of the returned recordset by changing the field list or joining additional recordsets.

The Advanced Filter/Sort window is one filtering interface that I would not attempt to duplicate in Visual Basic. It could be done, I suppose, but it would certainly be a nontrivial exercise. If you need this functionality, you should probably choose Access as your development tool or find a third-party product to plug in to the system.

Microsoft English Query

If you choose SQL Server as your database engine, consider implementing Microsoft English Query. More than just a sorting and filtering interface, English Query provides a full-blown natural language interface to the database.

To implement English Query in your application, you will need to map the language of the problem space to the database schema by creating what English Query calls an "application file." Creating an English Query application file isn't difficult, but it isn't trivial, either. Like a good help file index, it requires spending a lot of time predicting the words users will use to reference the entities and attributes in the schema.

Once you create the application file, integrating English Query into your database application is easy. Your database application simply submits a user's natural language question to the English Query engine and receives a SQL statement in return. Well, in theory it's that easy. In reality, your application might receive an error message indicating that a user (creative little creatures that users are) has phrased the question in terms the engine doesn't understand.

In the right environment, English Query is a wonderful tool. If you have a complex database schema and a lot of novice users making inquiries, the natural language interface provided by English Query can be a superb solution.