O'Reilly logo

Access Data Analysis Cookbook by Wayne S. Freeze, Ken Bluttman

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Building a Multifaceted Query Selection Screen

Problem

What is the best way to build a form that allows a user to query data through a series of selections? In other words, how can you use a series of controls on a form to create a SQL statement without forcing the user to understand the nuts and bolts of the SQL language?

Solution

Query construction forms are a great way to let users build queries and select criteria in a paradigm they understand. Of course, the structure of such a form is dependent on the actual schema of the database in question. That is, the fields from which users will make selections are dependent on the database and the business case of the data.

Figure 6-45 shows two related tables. Each customer can have multiple purchases. A form, shown in Figure 6-46, has been designed that lets users filter records based on state, customer type, and total amount spent.

The custom query form has been designed to allow the user to select any mix of multiple states and multiple customer types, and to specify a minimum amount spent. All of the criteria are optional. If no selections are made, all records are returned. Each time the Go button is clicked, the query SQL is assembled, and the results are put in a new table. The user is required to enter the new table name on the form; if he does not provide a value, he will be prompted to do so.

Related customer and sales tables

Figure 6-45. Related customer and sales ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required