Query By Example

Like this article? We recommend 

Many of the applications I have worked on manage large databases of some sort. Users typically examine a long list of records that need attention, select one, and open a detailed view of that record.

How a user selects a record to work on is critical to the application's success. If an application displays thousands of records in an unstructured list, the users won't be able to find the ones they need. To let the users find the records they want, a program must be able to select and order records with specific characteristics.

Often, a program can list records in order of importance or urgency. Sometimes, however, the user needs to find records using other criteria. The user might need to find the record for a particular customer or a particular purchase order.

Occasionally, the user needs to locate records that meet some fairly strange conditions. For example, a supervisor might want to see a list of all of the orders processed by a particular clerk during a certain one-week period to check on that clerks' productivity.

One way to give the user the flexibility to do all these things is query by example. In a query by example, the user enters or selects the fields that should be included in the query and the values that should be used for comparison. The program takes the user's selections and builds a database query to locate the needed records. The query itself is hidden from the user.

The Selector form described in this article lets you quickly add query-by-example features to your prototype. After your customers have seen it, they may want modifications or decide it's the wrong approach entirely. In that case, you haven't lost much time building something elaborate of your own. Often, however, customers like this selection mechanism. Then, you can mark this part of the application as finished and move on to something else.

This article describes the Selector form from three different points of view. First, it explains what the form looks like to the user and tells how the user selects records with the form. Second, it tells how you can use the form in your application. Finally, it describes the form's code and explains how it works.

You can download a sample project here and try the program for yourself as you read. The program was written in Visual Basic 6, but you can translate it for other versions of Visual Basic without too much trouble.

User Features

Figure 1 shows the Selector form in action. In the Query Values area, you pick a field from a combo box in the left column. In the middle combo box, you select an operator such as >= or !=. Your customers may need a little training to use some of the more exotic operators such as IS NULL, IS NOT NULL, and LIKE. After you select a field name and operator, you enter a value in the text box on the right.

Figure 1 The Selector form lets the user pick records by selecting field names, operators, and values.

When you click the List button, the form displays a list of the records that satisfy the requirements you specified. In Figure 1, the form is listing records where the Company field is LIKE %software%. The form is essentially executing the following statement:

SELECT Company, LastName, FirstName, AccountBalance
FROM Customers
WHERE Company LIKE '%software%'
ORDER BY Company, LastName, FirstName

With a little experience, users can build extremely complex queries. Selecting all the orders processed by a particular clerk during a certain one-week period is a snap.