Quattro Pro has several Database Tools which allow simple database queries, reports and sorting of fields.

The structure of a Quattro Pro database is as follows: The first row contains a list of all the field names, such as FirstName, LastName, etc, where each field name should be up to 63 characters with no leading or trailing spaces and must be unique. Each subsequent row is a record in the database. There should be no blank rows, including the first row below the field names, and the block should be continuous, in one spreadsheet page. The database can have up to 256 fields and contain 8191 records.

Database Forms

Select the database field titles and a blank row below it, then select [Tools][Database Tools][Form] to create a form to automate the adding and editing of data.

The form allows you to search for certain criteria. Click the [Search] button, type the criteria next to the field name and press Enter. Quattro Pro then takes you to the first occurrence which fits this criteria, the [Go Next] button takes you to the next occurrence, etc. The search results are only for your editing purposes, Database queries are explained in detail below.

The search criteria can include logical operators, i.e. = (equal), <> (not equal), >= (greater than or equal), > (greater than), <= (less than or equal), < (less than). For example, FirstName <Anthony will find all records whose first name is less than Anthony, e.g. Anne, Andrew, Adrian, Abigail, etc.

Manual Databases

You can just type the database fields and records in a spreadsheet page manually, but you must observe the rules which define the Quattro Pro database structure, as outlined above.

Searching for Records

Initialising Field Names

Whether the database records were entered via the form or manual method, you must initialise the field names in order to search for records which satisfy certain criteria.

There are two methods to do this:

1. Select [Tools][Database Tools][Query]. Select the database block (the field names and data) and click on the [Field Names] button. The field names are then added to the list of block names available.

2. Select [Block][Names] click on [Labels]. Select the block of field names and click on [Down] radio button. Choose [OK] and see the names added to the block names available.

The Criteria Table

The criteria table is a table similar to the database table, in that its first row contains (some) field names, and the second row contains search criteria. There are no blank rows in the criteria table.

For example:

Country	Surname
England	S*

Finds all records with country = England and surname beginning with S.

It is a good idea to name the block 'Criteria' as then it is easier to refer to.

If you have two different criteria on the same line, it is equivalent to Criteria1 *AND* Criteria2. For example, in the criteria table above, the query finds all entries with the surname beginning with 'S' who are resident in England. If the criteria are on separate lines, then the query will find matches to either of the criteria, equivalent to an *OR*.

Tip! Beware that if you edit the criteria table, and remove a line of an alternative (OR) criteria, you must re-define the criteria table block size. Otherwise the blank line will be included as an OR criteria, which of course, all records in the table satisfy!

Querying the Database

The data can be searched for by selecting [Tools][Database Tools][Query]. In the resulting dialogue box specify the database block, the criteria block and the output block. It is a good idea to have the output block on a separate spreadsheet page so as not to overwrite data, and with no limit on size, to ensure the block is adequate for the query.

Click Extract to extract the data which fits the specified criteria. Close the dialogue box and go to the output page to view the results of the query.

Sorting Fields

Define the sort criteria in the [Block] [Sort] dialogue, referencing the blocks and fields by their names or block coordinates. The SpeedSort buttons will then repeat this sort in ascending or descending order.

Data Modelling Desktop

There is a separate program available for designing reports and querying database data in Quattro Pro. The program is run from the [Tools] menu, [Data Modelling Desktop] menu item.

The program first asks the data block that you want to query and the block in your spreadsheet to output the data to. The program then loads the data and opens up several tools for report writing.

The gadget 'Limit' is where you can specify criteria for the data query, making the criteria equal, greater than, containing, etc. The report can have side and top bars specified for a flexible layout, and include as many or few of the data fields in which ever order is preferred.

When you are happy with the layout and results of the query, select [Edit][Copy to Quattro Pro] and the information will be put into the spreadsheet in the block you specified on launching the data modelling desktop.

Next Page > > >