Preparing queries for searching notebook databases
Preparing to query a notebook database is a three-step process. First, you create a criteria table. A criteria table consists of a row of field names that must match one or more fields in the database cells, and one or more rows of search criteria. The criteria table must be contiguous and confined to one spreadsheet, but it does not have to be on the same spreadsheet as the database cells.
Next, you create the search output cells for viewing the results of your query. You create these cells on an area of the notebook where database records that match your search criteria will be copied. It is important to place your output cells in an area that does not overlap with either the database cells or the criteria table.
Finally, you can set up AND and OR queries, search formulas, and wildcards in the criteria table to help you narrow or broaden your search.
AND and OR queries can help you either narrow or broaden your search. When you use AND queries, all data must match all search criteria in all rows of the criteria table. When you use OR queries, data can match criteria in one or more rows of the criteria table, but not necessarily all rows. To get the most out of your search, combine AND and OR queries. For an example, see “Example of a notebook database query.”
Search formulas are a powerful database search tool. They let you specify conditions that must be met for Quattro Pro to find matching records. Your search conditions can be as specific or as general as you want. For example, you can create a search formula that looks for payments of less than $25.00 (+~PAYMENT<25) or records dated after January 1, 1939 (+~DATE>@DATE(1939,1,1)). When you reference another cell in a criteria table formula, while the cell reference on the left needs to be relative, the cell reference on the right needs to be absolute; for example: +~AGE>$H$5. For a listing of available operators and symbols, see “Operators and symbols available in a search formula.”
Wildcards are placeholder characters that represent one or more characters. They are powerful tools for searching for text in database cells. You can use wildcards or search formulas as search criteria, but you cannot combine wildcards with other operators. For example, +~Item=tape is a valid comparison formula. However, if you enter the formula +~Item=t?p , the exact string t?p is found; records containing tip, tap, or top are not found. For a listing of available wildcards, see “Wildcards available in a criteria table.”
To create a criteria table for a notebook database |  |
|
1.
|
|
Select the field names to search in the database.
|
|
2.
|
|
Click Edit
 Copy. |
|
3.
|
|
In a blank area of the spreadsheet, click Edit
Paste. |
|
4.
|
|
Type search criteria in the rows beneath the corresponding field names using the following:
|
|
•
|
Exact matches — type search criteria exactly as the data displays in the database
|
|
•
|
Conditional matches — type search criteria using search formulas, wildcards, or queries
|
|
•
|
|
For an example of a query, see “Example of a notebook database query.”
|
To set up an AND query in a criteria table | |
|
1.
|
|
Type a criterion under a field name of the criteria table.
|
|
2.
|
|
In the same row, type a criterion under another field name of the criteria table.
|
|
•
|
|
For an example of this query, see “Example of a notebook database query.”
|
To set up an OR query in a criteria table | 
mages/btnbacktotopproc.gif" alt="Back to Top" border="0" height="10" width="10"> |
|
1.
|
|
Type a criterion under a field name of the criteria table.
|
|
2.
|
|
In a different row, type a criterion under another field name of the criteria table.
|
|
•
|
|
For an example of this query, see “Example of a notebook database query.”
|
To set up an AND and OR query in a criteria table | |
|
1.
|
|
Type a criterion under a field name of the criteria table.
|
|
2.
|
|
In the same row, type a criterion under another field name of the criteria table.
|
|
3.
|
|
In a different row, type a criterion under a field name of the criteria table.
|
|
•
|
|
For an example of this query, see “Example of a notebook database query.”
|
To set up a search formula in a criteria table | |
|
1.
|
|
Type search formulas under field names of the criteria table.
|
|
2.
|
|
Click Tools
Data tools Notebook query. |
|
3.
|
|
Click Field names.
|
|
•
|
|
For a listing of available operators and symbols, see “Operators and symbols available in a search formula.”
|
|
•
|
|
Logical formulas in criteria tables display either 1 (true) or 0 (false).
|
To set up wildcards in a criteria table | |
|
•
|
|
Type a criterion, including a wildcard, under a field name of the criteria table.
|
|
•
|
|
For a listing of available wildcards, see “Wildcards available in a criteria table.”
|
To create search output cells for a notebook database | |
|
1.
|
|
Select the field names to search in the database.
|
|
2.
|
|
Click Edit
Copy. |
|
3.
|
|
In a blank area of the spreadsheet, click Edit
Paste. |
|
•
|
|
The search output cells must be separate from the criteria table.
|
|
•
|
|
The search output cells do not have to be on the same spreadsheet as the criteria table or the database cells.
|