Creating Database Queries


When your search needs are more complicated than you can set up in the data form window, you can create more complicated searches using queries that contain search formulas. Suppose, for example, that I want to search the database shown in Figure 12.9 for everyone with a last name of Martin or a first name of Andrea. Or, suppose that I wanted to search for everyone with a last name of Sandler and a first name of Andrea. These are searches you can't perform using the data form window.

Figure 12.9. You can create queries for databases like this one.


To make searches like these, you set up queries. Setting up a query is a three-part process:

  • Set up a criteria table

  • Set up an output range

  • Set up the search criteria and formula

Creating a Criteria Table

A criteria table is, at a minimum, two rows long. The first row contains field names that must exactly match one or more fields in the database cells. I suggest that you simply copy the row containing all the field names to the area you plan to designate as the criteria table.

Tip

Although you can define the criteria table and the search output range on the same sheet as the database, I separate the criteria table and the search output range so that I always know what I'm viewing.


The second row and subsequent rows contain search criteria. You should not leave any blank rows between the field names and the criteria you enter. You must set up the entire criteria table on one spreadsheet, but you don't have to set up the criteria table on the same spreadsheet as the database.

In Figure 12.10, I've renamed sheets A, B, and C to reflect the information they hold, naming sheet A "Database", sheet B "Criteria Table", and sheet C "Search Output." To set up the criteria table for the database you saw in Figure 12.9, I copied field names from the database sheet into row 1 of the Criteria Table sheet; later in this chapter, I'll enter criteria starting in row 2 of the Criteria Table sheet.

Figure 12.10. The field labels in the criteria table must exactly match one or more field labels in the database.


Creating the Search Output Range

You define search output cells in an area of the notebook where Quattro Pro can copy database records that match your search criteria; you view the results of your query in the search output range.

Because Quattro Pro copies database records that match the search criteria you establish to the search output range, be sure that you don't place your output cells in an area that overlaps with either the database cells or the criteria table.

You set up the search output range the same way that you set up the criteria table, only in a different location. In Figure 12.11, I copied the database field titles onto the Search Output sheet.

Figure 12.11. Set up the search output range so that it is separated from the database and the criteria table.


Setting Up Queries

You set up database queries by typing criteria in row 2 and subsequent rows of the criteria table. In Figure 12.12, I set up a simple query to display all the records in the database where the First Name field contains "Cathy."

Figure 12.12. This criteria table tells Quattro Pro to search the database for all records where the First Name field contains "Cathy."


Make sure that the output range you select is equal to or larger than the database range so that all selected records will appear.


To tell Quattro Pro to make the search, follow these steps:

1.

Open the Tools menu, point to Data Tools, and click Notebook Query. Quattro Pro displays the Notebook Data Query window.

2.

Click the Database Cells Range Picker button. Quattro Pro collapses the Notebook Data Query window so that you can you select the range for the database; when you finish selecting the cells, click the Maximize button to redisplay the Notebook Data Query window.

3.

Repeat step 2 using the Criteria Table Range Picker button and the Output Cells Range Picker button. When you finish, the Notebook Data Query window should resemble the one shown in Figure 12.13.

Figure 12.13. Use the Range Picker buttons to establish ranges for the database, the criteria table, and the search output range.


4.

To copy records that match the search criteria to the output range, click the Extract button.

5.

Click Close.

6.

Display the output range onscreen; it will contain all database records that match the search criteria (see Figure 12.14).

Figure 12.14. The results of the search.


You can use the same steps to create more complicated searches, changing the criteria table. For example, you can search for all records where the first name is Cathy or the last name is Martin by setting up the criteria table listing one criterion on row 2 and the other on row 3, as I did in Figure 12.15.

Figure 12.15. To set up an "Or" search, specify criteria on two rows of the criteria table.


Reopen the Notebook Data Query window by opening the Tools menu, pointing to Data Tools, and clicking Notebook Query. In the Notebook Query window, be sure to change the rows included in the criteria table to include rows 1 through 3; then, click Extract to copy matching records to the output range. When you complete the query, Quattro Pro copies all records where the first name is Cathy or the last name is Martin to the output range (see Figure 12.16).

Figure 12.16. The results of the "Or" search.


If you place the criteria on the same line of the criteria table, Quattro Pro performs an "And" search and displays only those records where the first name is Cathy and the last name is Martin.

There are lots of other, powerful ways you can search a spreadsheet database, but they are beyond the scope of this book. Be aware, though, that you can combine "And" and "Or" queries. And you can use math in a query to search, for example, for customers with balances that exceed $500. I hope that the taste of database querying that I've offered here encourages you to explore the Help system in Quattro Pro to set up additional queries.




Absolute Beginner's Guide to Quattro Pro X3
Absolute Beginners Guide to Quattro Pro X3
ISBN: 0789734265
EAN: 2147483647
Year: 2007
Pages: 128
Authors: Elaine Marmel

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net