Category : Tutorials + Patches
Archive   : QPRODOC.ZIP
Filename : TI391.ASC

PRODUCT : Quattro Pro NUMBER : 391
VERSION : All
OS : DOS
DATE : September 11, 1991 PAGE : 1/9

TITLE : DATABASICS: Troubleshooting Database Applications




In every database application, there are several important
components:

1. The Database block is essential in every database
operation. It is the area where all your information is
located. Each database block consists of the following:

a. A row of field names which describe each field in
your database. These field names must always be
located in the first row of your database block,
just above the first record in your database. Each
field name should be unique, no longer than 15
characters in length, and should not contain
blanks. If you wish to add a space between two
words, use the underscore symbol (_).

b. The remaining row(s) in your database block are
record(s). In a phone list, for example, a record
would consists of each person's name and phone
number.

c. Each column of your database block is a field.
Field(s) are individual pieces of information that
fit within a record. In a phone list, for example,
one field may be the person's name; another field
may be the person's phone number.

If you are linking to an external database file (that is,
a Paradox, Reflex, or dBASE file which is not currently
open in a Quattro Pro window), you can specify the
database block using the following syntax:
[FILENAME]A1..A2, where FILENAME is the name of the
external database you wish to query. This syntax allows
you to modify records in the external database without
being forced to redefine your database block each time.
Note that you cannot do a Locate or Delete on an external
database, it is read only. If linking to an external
spreadsheet database (such as Lotus, Quattro, Symphony, or
Surpass), you must link to the entire database block (eg.
[FILENAME]A1..G58]).
















PRODUCT : Quattro Pro NUMBER : 391
VERSION : All
OS : DOS
DATE : September 11, 1991 PAGE : 2/9

TITLE : DATABASICS: Troubleshooting Database Applications




2. The criteria table is also essential in every database
application. It is in the criteria table that you specify
a set of rules which will determine what records you wish
to examine in the main database block. Each criteria
table consists of the following:

a. The first row of your criteria table will always
consist of field names. These field names must all
be spelled exactly as they appear in the database
(use /Edit | Copy to copy the field names directly
from the database into the criteria table; this
ensures that there will be no typos). It is not
necessary to place all of the database's field
names in the criteria table; you only need to
include the field names which contain a criteria
you are using for your query.

b. The second row, and any rows following, contain the
search criteria. It is here that you can specify
what rules you wish to adhere to for extracting
records. A criteria table must always have at
least one of these rows in addition to the field
names. The search criteria consists of one of the
following:

o A Value, such as 25.50. Quattro Pro will
look for this value in the field whose name
is located above the criteria in the first
row of the criteria table.

o A Label, such as Cleveland. The Labels can
contain wildcards, C* for example would
query for all labels which begin with the
letter C. Quattro Pro will look for this
label in the field whose name is located
above the criteria in the first row of the
criteria table.

o A Logical Search Formula. A search formula
is a logical formula which returns either a
one or zero, such as +AGE>30.















PRODUCT : Quattro Pro NUMBER : 391
VERSION : All
OS : DOS
DATE : September 11, 1991 PAGE : 3/9

TITLE : DATABASICS: Troubleshooting Database Applications




o An empty cell, which acts as a place holder,
and indicates no criteria in this cell (in
other words, all records). Note that no row
in your criteria table should consist of
only blank cells, because if it does, you
are telling Quattro Pro to query ALL records
in the database.

3. The last component of your database, the output block, is
only necessary if you are using the database operations
Extract or Unique. It is not necessary if you are using
the Locate or Delete commands. Each output block consists
of the following:

a. The first row contains field names which are taken
from the field names in the database block. It is
not necessary to place all the field names in the
database block into this table, only the names of
the fields you wish to extract into the output
block. These field names must all be spelled
exactly as they appear in the database (use /Edit
| Copy to copy the field names directly from the
database into the criteria table; this ensures that
there will be no typos).

b. Any other rows can be empty, since they are place
holders for where you wish the extracted data to be
placed. If your output block is only one row in
size, then Quattro Pro will delete everything below
the output block and place as many of the records
as it can underneath. If you wish to prevent this,
specify an output block with more than one row.
Quattro Pro will try to fit the extracted records
into only the block you specify, and will return an
error message ("Too Many Records For Output Block")
if they do not all fit.




















PRODUCT : Quattro Pro NUMBER : 391
VERSION : All
OS : DOS
DATE : September 11, 1991 PAGE : 4/9

TITLE : DATABASICS: Troubleshooting Database Applications




Trouble Shooting

If you are having problems with your database application, use
the following list to make sure everything is configured
properly. Databases are very precise, and small mistakes can
produce some pretty strange results.

Before running through this list, go into the Database/Query menu
(/Database | Query), and jot down the following information. It
will help you skim the checklist more easily.

1. The block range defined as the database block (/Database |
Query | Block).

2. The block range defined as the criteria table (/Database |
Query | Criteria Table).

3. The block range defined as the output block, if one is
necessary (/Database | Query | Output Block).


If you are not querying an external database file:

1. Does the first row of the database contain field names
which are no more than 15 characters long, and unique?

2. Is the first record located in the second row of your
database block?

3. Does each field in the database consist of a column of
labels or a column of values (make sure they are not
mixed)?

4. Have you issued the command /Database | Query | Assign
Names to establish the field names in your database? If
you are unsure, or have changed any aspects of the
database, then issue this command again.

5. Have you indicated a criteria table using the /Database |
Query | Criteria Table command?

















PRODUCT : Quattro Pro NUMBER : 391
VERSION : All
OS : DOS
DATE : September 11, 1991 PAGE : 5/9

TITLE : DATABASICS: Troubleshooting Database Applications




6. In your criteria table:

a. Does the first row contain field names which are
entered exactly like the field names in your
database?

b. Does each cell in the row(s) below the first row of
the criteria table contain one of the following:

o A Value, such as 123?

o A Label, such as Address?

o A Label containing a wildcard, such as ABC*?

o A Logical Search Formula which returns the
value 1 or 0, such as +SALES>300, or
+SALES>$A$1? Make sure that all cell
addresses in the search formula which are
not field names are absolute cell addresses
(such as $A$1).

o An Empty Cell (tap on the Del key to make
sure a cell is empty)? No row in your
criteria table should consist of only blank
cells, because if it does, you are telling
Quattro Pro to query ALL records in the
database.

7. If you are performing an Extract, Unique, or Delete
operation:

a. Have you used the /Database | Query | Output Block
command to indicate an output block?

8. Does the first row of your output block contain field
names which are spelled exactly like the field names in
your database?

If you are querying an external database (which is a Paradox,
Reflex, or dBASE file which is not currently open in a Quattro
Pro Window):














PRODUCT : Quattro Pro NUMBER : 391
VERSION : All
OS : DOS
DATE : September 11, 1991 PAGE : 6/9

TITLE : DATABASICS: Troubleshooting Database Applications




1. Is the database block linked to the database in the format
of [DATABASE]A1..A2?

2. Is the file a Paradox, Reflex, or dBASE file?

3. Are the field names in your search formulas linked to the
external database? For example, +[DATABASE]SALES>300.

















































PRODUCT : Quattro Pro NUMBER : 391
VERSION : All
OS : DOS
DATE : September 11, 1991 PAGE : 7/9

TITLE : DATABASICS: Troubleshooting Database Applications




Databasics Quick Reference:


Database Definitions

The Database Block

A B C <ÄÄÄÄ Column
Letter
ÉÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍËÍÍÍÍËÍÍÍÍÍÍÍÍÍÍ»
1 ºNAME ºAGE ºPHONE_NO º <ÄÄÄÄ Field Names
ÌÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÎÍÍÍÍÎÍÍÍÍÍÍÍÍÍ͹
2 ºJohn Doe º12 º000-0000 º <ÄÄÄÄ¿
3 ºNick Derpich º24 º111-1111 º <ÄÄÄÄÅÄÄ Records
4 ºWilhelm Stroup º12 º222-2222 º <ÄÄÄÄÙ
³ ÈÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÊÍÍÍÍÊÍÍÍÍÍÍÍÍÍͼ
³ ³ ³ ³
³ ³ ³ ³
³ ÀÄÄÄÄÄÄÄÄÄÄÅÄÄÄÄÄÄÄÄÄÙ
³ ³
³
ÀÄÄÄ Row # Fields


The database block above would be entered under /Database | Query
| Block as A1..C4. Notice the underscore in the field name
PHONE_NO.


Criteria Table Definitions

A Criteria Table demonstrating a logical OR of two
criteria

D E <ÄÄÄÄÄ Column Letter
ÉÍÍÍÍÍÍÍÍÍÍÍËÍÍÍÍÍ»
1 º NAME º AGE º <ÄÄÄÄÄ The Field Names
ÌÍÍÍÍÍÍÍÍÍÍÍÎÍÍÍÍ͹
2 ºJohn Doe º º <ÄÄÄ¿
3 º º 0 º <ÄÄÄÁÄ The Search Criteria
³ ÈÍÍÍÍÍÍÍÍÍÍÍÊÍÍÍÍͼ
³ ³
ÀÄÄÄ Row # ³













PRODUCT : Quattro Pro NUMBER : 391
VERSION : All
OS : DOS
DATE : September 11, 1991 PAGE : 8/9

TITLE : DATABASICS: Troubleshooting Database Applications




ÀÄÄÄÄÄÄÄÄÄÄ This is the result of
the formula +AGE>30,
which is entered in this
cell. In some cases, it
may return a 1,
depending on what
records you have in the
database block. Logical
search formulas will
always return a 1 or 0
in the cell.

The above criteria table tells Quattro Pro to look for all
records with the label John Doe in the field NAME, OR records
where the value in the field AGE is greater than 30. Note that
the empty cells in this criteria table merely act as place
holders, so that Quattro Pro does not think you wish to use some
criteria in those cells. This criteria table would be entered
under /Database | Query | Criteria Table as D1..E3.


A Criteria Table demonstrating a logical AND of two
criteria

D E <ÄÄÄÄÄ Column Letter
ÉÍÍÍÍÍÍÍÍÍÍÍËÍÍÍÍÍ»
1 º NAME º AGE º <ÄÄÄÄÄ The Field Names
ÌÍÍÍÍÍÍÍÍÍÍÍÎÍÍÍÍ͹
2 ºJohn Doe º 1 º <ÄÄÄÄÄ The Search Criteria
³ ÈÍÍÍÍÍÍÍÍÍÍÍÊÍÍÍÍͼ
³ ³
ÀÄÄÄ Row # ³
ÀÄÄÄÄÄÄÄÄÄÄ This is the result
of the formula
+AGE>30, which is
entered in this
cell. In some
cases, it may return
a 0, depending on
what records you
have in the database
block. Logical
search formulas will













PRODUCT : Quattro Pro NUMBER : 391
VERSION : All
OS : DOS
DATE : September 11, 1991 PAGE : 9/9

TITLE : DATABASICS: Troubleshooting Database Applications




always return a 1 or
0 in the cell.

The above criteria table tells Quattro Pro to look for all
records with the label John Doe in the field NAME, AND values in
the field AGE which are greater than 30. It would be entered
under /Database | Query | Criteria Table as D1..E2.


A Criteria Table demonstrating a logical OR of multiple
criteria

D <ÄÄÄÄÄÄ Column Letter
ÉÍÍÍÍÍÍÍÍÍÍÍ»
1 º NAME º <ÄÄÄÄÄÄ The Field Name
ÌÍÍÍÍÍÍÍÍÍÍ͹
2 ºJohn Doe º <ÄÄÄÄ¿
3 ºJane Grey º <ÄÄÄÄ´
4 ºBill Dill º <ÄÄÄÄ´
5 ºJoe No º <ÄÄÄÄÁÄ The Search Criteria
³ ÈÍÍÍÍÍÍÍÍÍÍͼ
³
ÀÄÄÄ Row #

The above criteria table tells Quattro Pro to look for all
records with the label John Doe, Jane Grey, Bill Dill, OR Joe No
in the field NAME. It would be entered under /Database | Query |
Criteria Table as D1..D5.