Databases are collections of information organized as fields, records, and files. A field is a single piece of information; a record is a set of fields; and a file is a set of records. A database can contain information such as names and addresses of clients, patient records, or product inventory.
In this section, you'll learn about
creating notebook databases
preparing notebook database search queries
querying notebook databases
querying external databases
importing data from external databases
For more information about using databases, see "Reference: Using databases."
Creating notebook databases
You can use data forms in Quattro Pro to create notebook databases. Data forms let you do the basic functions of adding, deleting, or changing records in a database using a form, instead of typing the information directly in the notebook cells. Each field from the database is listed, you type in the information, and then Quattro Pro automatically places it in a row in the notebook database.
You can add records to a notebook database. As well, you can search for records in a notebook database when you need to edit or delete existing records or discover if there are duplicate records. When searching for records, you can navigate through the database to find the information you want.
You can edit information in existing records and delete records that you no longer require.
To create a notebook database
1 In a blank notebook, type field names in individual columns of row 1.
For example, you could type Name in cell A1, Address in cell B1, and City in cell C1.
2 Select the field names in row 1 and the empty cells directly beneath them in row 2.
For example, if you have field names in cells A1 to C1, select the block of cells from A1 to C2.
3 Click Tools Data tools Form.
4 Click OK.
5 In the Edit records dialog box, type the record information in the corresponding fields.
6 Click New.
Note
A notebook database can also be created by manually typing data in the rows beneath the appropriate field name. For more information, see "Rules for entering database cells in Quattro Pro."
To add a notebook database record
1 Click a cell in the database.
2 Click Tools Data tools Form.
3 Click OK.
4 In the Edit records dialog box, click New.
5 Type the record information in the corresponding fields.
6 Click New.
To navigate in a notebook database
1 Click a cell in the database.
2 Click Tools Data tools Form.
3 Click OK.
4 In the Edit records dialog box, click any of the following navigation buttons:
First record-moves directly to the first database record
Last record-moves directly to the last database record
Record forward-moves forward one database record
Record back-moves backward one database record
Note
When searching for records, the navigation buttons navigate only records that meet the search criteria. To navigate all database records, clear the search criteria.
Tip
You can also navigate in a database by using the slider. As you drag the slider, Quattro Pro displays the record number above the bar. You can drag the slider from the first record to the last record, and you can stop at any record.
To search for a record in a notebook database
1 Click a cell in the database.
2 Click Tools Data tools Form.
3 Click OK.
4 In the Edit records dialog box, click Search.
5 Type the information for which to search in the corresponding fields.
6 Click Record forward.
Note
To narrow the search, fill out as many fields with search criteria as you can. To broaden the search, use wildcards in your search criteria.
To edit a notebook database record
1 Click a cell in the database.
2 Click Tools Data tools Form.
3 Click OK.
4 In the Edit records dialog box, click Record back to locate the record to edit.
5 Edit the record.
6 Click Record forward.
To delete a notebook database record
1 Click a cell in the database.
2 Click Tools Data tools Form.
3 Click OK.
4 In the Edit records dialog box, click Record back until you locate the record to delete.
5 Click Delete.
Note
You cannot retrieve records after you delete them.
Preparing notebook database search queries
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
Note
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.
Note
For an example of this query, see "Example of a notebook database query."
To set up an OR query in a criteria table
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.
Note
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.
Note
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.
Notes
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.
Note
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.
Notes
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.
Querying notebook databases
After creating a criteria table, setting up search formulas, queries, or wildcards, and creating search output cells, you can query notebook databases. For information about query preparation, see "Preparing notebook database search queries."
Using the query function, you can highlight specific records in a notebook database. You can also copy records from the notebook database to output cells on your spreadsheet. As well, you can use search criteria to delete records from the notebook database.
To highlight specific records in a notebook database
1 Click Tools Data tools Notebook query.
2 Select the database cells using the Range picker.
3 Select the criteria table using the Range picker.
4 Click Locate.
Note
Pressing ESC returns you to the Notebook data query dialog box.
Tip
To select database cells in another open notebook, use standard linking syntax. For example, typing [DATA]A1..Z50 specifies cells A1..Z50 in the notebook called DATA.
To copy notebook database records to output cells
1 Click Tools Data tools Notebook query.
2 Select the database cells using the Range picker.
3 Select the criteria table using the Range picker.
4 Select the row of the output cells which contains the field names using the Range picker.
5 Click one of the following buttons:
Extract-copies all records matching the specified criteria to the output cells
Extract unique-copies only unique records matching the specified criteria to the output cells; duplicate entries are ignored
Notes
To select database cells in another open notebook, use standard linking syntax. For example, typing [DATA]A1..Z50 specifies cells A1..Z50 in the notebook called DATA.
Only fields whose names are written on the first row of the output cells are copied. All data in the cells beneath the output cells is erased before it is extracted; do not store notebook data in that area of the spreadsheet. You can avoid losing data by specifying an output area larger than one row; this also limits the number of records the output cells accept.
To delete specific records from a notebook database
1 Click Tools Data tools Notebook query.
2 Select the database cells using the Range picker.
3 Select the criteria table using the Range picker.
4 Click Delete.
Notes
Records below the deleted records move up to fill in the empty rows. Use caution when doing this unless you are certain you want to remove the records.
To select database cells in another open notebook, use standard linking syntax. For example, typing [DATA]A1..Z50 specifies cells A1..Z50 in the notebook called DATA.
Tips
Records that have been accidentally deleted can be restored using the Undo command.
You can use the delete function in conjunction with the highlight function, allowing you to see the records that match your search before they are deleted.
Querying external databases
You can query external databases to access information in database files created using Paradox, Reflex, or dBASE (versions II, III, and IV) applications. Querying external databases lets you extract information from the database without first having to translate the file.
When you query external databases, you must create a criteria table specifying the type of information for which you are searching and output cells to hold the results of the search. For information about criteria tables and output cells, see "Preparing notebook database search queries."
You can also Query By Example (QBE), which, in database management systems, refers to a method of forming queries in which the database program displays a blank record with a space for each field. You can enter conditions for each field to be included in the query. For example, to find all records where the age field is greater than 65, you would type >65 in the age field.
Once you create a .qbe file, you can run the QBE query from Quattro Pro. A QBE query in a notebook selection always starts with the word Query and ends with the word EndQuery. The best way to learn the QBE syntax is to create queries in Paradox, import them into Quattro Pro, and then experiment by revising them.
An alias is a shortcut name that takes the place of a long path name. When using external database tables, it is convenient to assign aliases for your private and working directories. A private directory is a location for your temporary answer tables and other files that are not shared. A working directory is where a database application or a feature looks for external database tables.
To query external databases
1 Click Tools Data tools Notebook query.
2 In the Database cells box, type the path of the external database file using linking syntax.
You do not need to type the path if the database path is already in your working directory.
3 Select the criteria table using the Range picker.
4 Select the row of the output cells which contains the field names using the Range picker.
5 Click Extract.
Notes
The format of linking syntax is [Database]A1..A2, where [Database] is the name of the database, including the path; for example, C:\PARADOX\SAMPLE.DB. The cells specified after the filename can be any valid area with at least two rows. A1..A2 acts as a hook into the database. It remains A1..A2, no matter the size of the database area.
If your criteria contain formulas with cell references to data elsewhere, those cell references must be absolute. For example, for a table with Name and Age as column titles, you could type +~age>25 to identify all entries where age>25. To reference the cell C1 that contained the value 25, typing +AGE>C1 would not work; instead, you would need to type +~AGE>$A:$C$1 as the criteria, using absolute references.
To use Query By Example
1 Click Insert External data Table query.
2 Enable one of the following options:
Query in file-indicates that the query is located in a .qbe file
Query in selection-indicates that the query is located in a notebook selection
3 Type the location of the query in the Query file box.
4 Select the destination for the query results in the Destination box using the Range picker.
Notes
The QBE query and the database table must be in your working directory.
You can make a query selection easy to read by setting the font for the QBE selection to a nonproportional font such as Courier.
To assign aliases for private and working directories
1 Click Insert External data Aliases.
2 Type the path for your private directory in the Private directory box.
3 Type the path for your working directory in the Working directory box.
Tip
You can also click Browse to insert a path.
Importing data from external databases
You can insert into Quattro Pro external databases created using other software applications.
Linking to external database tables means that changes made in the source data are reflected in the linked table when you reopen it. You can open .db or .dbf files directly in Quattro Pro and edit them as if they were spreadsheets, as long as they do not contain index or memo files.
You can import external database tables in Quattro Pro. Database tables contain data extracted from a database, arranged in a specified sequence. Tables are useful when a database contains a large amount of data and you need to work with only a portion of it.
When importing database tables from external databases, you have several options for displaying data. You can display duplicate rows of information in the data you import. For example, if the imported data lists all customers who have ordered books, any customers who have ordered more than one book can be listed on a new row for each book. As well, you can include field names as column headings in the first row of data you import.
You can join external database tables. When using more than one table, you must join common fields in the tables, even if they have different field names, to avoid duplicating their records in the data you import. For example, if two tables contain the same customer identification field, you must use the customer identification field to join the tables.
Because external databases can contain a large amount of information, it is useful to filter the amount of information that you import, retrieving only the portions of data you want. For example, you can list only those customers who have ordered a particular book so that you can send them a letter announcing the sequel.
You can open password-protected tables. For security, as you type the password, the pound symbol (#) represents the characters you type.
To insert an external database into Quattro Pro
1 Click File Open.
2 Choose the drive and folder where the file is stored.
3 Choose the filename extension from the File type list box.
4 Double-click the filename.
Notes
If the external database file has more than 1,000,000 records, close the file when you are done, but do not save it. If you save the file, records that are not displayed are lost.
If no Open Database Connectivity ODBC sources are listed in the Data type list, you can add them through the ODBC options in the Control Panel. If no Borland Database Engine (BDE) sources (also called aliases) are listed, you can create them using the Borland Database Engine 5.01 utilities.
Tip
You can also insert external databases by clicking Insert External data Import database file.
To link to an external database table
1 Click Insert External data Table link.
2 In the Cells box, select the cells using the Range picker.
3 In the Table box, select the database file using the Browse button.
Tip
You can also link to external database tables by clicking the Table link button on the Data manipulation toolbar.
To import an external database table
1 Click Insert External data Expert.
2 From the Data type list box, choose a type of data to import.
3 In the Database path box, type the path where the database table is stored.
4 Click Next.
5 In the Select tables dialog box, select the tables to import.
6 Click Next.
7 In the Set criteria dialog box, select the fields to import.
8 In the Destination box, type a destination for the import results.
You can also
Change the position of a field in the Selected fields list. Click Arrow up or Arrow down.
Remove a field from the Selected fields list. Click the Trash can.
To set import options for external database tables
1 Click Insert External data Expert.
2 Click Options.
3 In the Import options dialog box, enable any of the following check boxes:
Allow duplicate rows-displays duplicate rows of information in the data you import
Include column headings-displays field names as column headings in the first row of data you import
Keep query definition-maintains the parameters you specify the next time you import tables from external databases
Note
The database must be imported before carrying out this procedure. For more information, see "To import an external database table."
To join external database tables
1 Click Insert External data Expert.
2 Choose the database tables to join.
3 Click Query properties.
4 In the Query properties dialog box, click the Joins tab.
5 Choose a field from the first Field list.
6 Choose the corresponding field from the second Field list.
7 Choose the equal sign from the Operator list.
8 Click OK.
9 Click Finish.
Note
The database must be imported before carrying out this procedure. For more information, see "To import an external database table."
Tip
You can join more than two tables by clicking And in the Query properties dialog box.
To filter an external database table
1 Click Insert External data Expert.
2 Click Query properties.
3 In the Query properties dialog box, click the Filters tab.
4 Choose a field to filter from the Criteria field list.
5 Choose an operator from the Operator list.
6 Enable the List values check box.
7 Choose a value from the Value list or type a value of your own.
8 Click OK.
9 Click Finish.
Tips
Clicking And in the Query properties dialog box narrows the filter results. For example, you can generate a list of customers who have a credit limit above a specified amount and who live in a certain city.
Clicking Or in the Query properties dialog box broadens the filter results. For example, you can generate a list of customers who live in a certain city.
To open a password-protected table
1 Click File Open.
2 Choose the drive and folder where the file is stored.
3 Choose the filename extension from the File type list box.
4 Double-click the filename.
5 In the Password dialog box, type the password for the table.
6 Click one of the following buttons:
Add-stores the password in memory, so that if other tables you want to query require the same password, you can open them without retyping the password
Remove-releases the password from memory, so that if any table you want to query requires the password, you must retype the password to open it
Remove all-deletes all passwords from memory, so that you must type a password for any protected table you want to query
Note
When you open a password-protected database table the password for the table is discarded when you close the file.
Reference: Using databases
When entering database cells in Quattro Pro, you must follow certain rules. These rules cover such items as the formatting of field names and the structure of the area filled by the database cells.
As well, queries are a key component of Quattro Pro notebook databases. You can use specific operators and symbols to assist you in developing queries; you can also use wildcards.
Rules for entering database cells in Quattro Pro
When you enter database cells in a Quattro Pro database, you must ensure that:
database cells fill a rectangular area that can contain any number of blank cells
database cells are entered in a contiguous format and are confined to one spreadsheet
field names are entered in the top row of the database cells, one name per column
field names are labels with no blank spaces at the beginning or end
field names are unique; they are different from other field and cell names you use
the first row of data is entered directly below the field names without leaving a blank row or inserting a row of symbols
you use the same type of data throughout each column; do not mix labels and values in the same column
Example of a notebook database query
The following is an example of a database that contains two columns, listing first and last names of company employees, to illustrate how you can use AND and OR queries to search a database.
To create an AND query in a criteria table using the database example, type the criterion Kelly in the row under First Name and the criterion Kind in the row under Last Name. The criteria are entered in different cells of the same row in the criteria table.
Quattro Pro searches for records with the last name Kind and the first name Kelly. The search results reveal only one possible match; the output cells contain a record for Kelly Kind.
To create an OR query in a criteria table using the database example, type the criterion Barbara in the row under First Name and the criterion Kind in the next row under Last Name.
Quattro Pro searches for any records with the last name Kind and the first name Barbara. The search results reveal five matches. The output cells contain records for Kelly Kind, Steve Kind, Barbara Kind, Barbara Smith, and Barbara Wong.
To combine AND and OR queries in a criteria table using the database example, type the criterion Kelly in the row under First Name, type the criterion Kind in the same row but under Last Name, and the criterion Barbara in a new row under First Name.
Quattro Pro searches for any records with the last name Kind and the first name Barbara or Kelly. The search results reveal four matches; the output cells contain records for Kelly Kind, Barbara Kind, Barbara Wong, and Barbara Smith. Records for Kelly Smith, Kelly O'Toole, and Steve Kind do not match the search criteria and are therefore not displayed in the search output cells.
Operators and symbols available in a search formula
The following table shows the operators and symbols you can use in search formulas.
Use To indicate
= Equals
<> Not equal
< Less than
> Greater than
<= Less than or equal to
>= Greater than or equal to
#AND# Both expressions are true
#OR# Either the first or the second expression is true
#NOT# The first expression is true, and the second is false
Wildcards available in a criteria table
The following table lists the wildcards you can use as search criteria.
Use To search for
? Any single character in a label. For example, typing t?p finds tip, top, or tap, but not tape or stop.
* Any number of characters to the end of a label. For example, typing ten* finds tender, tension, and tent, but not attention.
~ All labels in a field except those that match the search condition. This symbol is referred to as tilde. For example, typing ~Boston under the City column in the criteria table finds all records that do not have Boston in the City field.
