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:
Creating a Criteria TableA 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
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 RangeYou 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 QueriesYou 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."
To tell Quattro Pro to make the search, follow these steps:
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. |