This article is reprinted from the October 1990 edition of
TechNotes/dBASE IV.  Due to the limitations of this media, certain
graphic elements such as screen shots, illustrations and some tables
have been omitted.  Where possible, reference to such items has been
deleted.  As a result, continuity may be compromised.  

TechNotes is a monthly publication from the Ashton-Tate Software
Support Center.  For subscription information, call 800-545-9364.

The Redemption of QBE
Joe Stolz

You consider yourself an expert in dBASE IV.  You've been programming
in the dBASE language for all of it's derivatives.  You even help your
friends and neighbors create their own checkbook programs and mailing
lists.  You are a whiz .  But you hid an awful secret from even your
closest confidantsyou have no idea how to make "relational
databases". 

Perhaps you do understand the basics of relating two files, but you
are still extremely unsure of the outcome of the relation.  Perhaps
you can say "parent and child" like the best of them, but to make your
one-to-many relation fly you have to rely on QBE to create the
relation.   

Don't feel bad.  It seems that nobody really feels solid about setting
up related files at first.   It sure is nice to know that dBASE IV
supplies the QBE engine which makes relating files about as easy at is
to point.  

When you have two or more files that contain some common field (like
Social Security number or last name) you can relate those files.  If
one file contains names, addresses and an account number, and a second
file has the same account number field and a transaction amount field
(among others) you probably can see that by relating these files you
can create reports or statements that combine important information
from both files.   

QBE offers an easy methodology for linking files.  You can also
calculate summary amounts, or create calculated fields based on data
found in one or more fields in the files.   All in all, with QBE to
help, linking files doesn't seem so bad!

Single Links

How do you link files in QBE?  For simplicity, let's discuss the case
of linking just two files.  First you have to create a query and then
open the files that you want to link.  You add files through the
Layout: Add file to query menu option.  Add both files to the query
work surface.   

To link the files, you need to determine which field is in common.  If
you have a field that has the same data in both files, that's probably
your best candidate for a field on which to link.   

Some important information about linking fields: 

               The length of the common fields and of course, the type of
data (for example,  numeric or character) must be the same.  If you
first make an effort to make your linking fields the same length, you
will save yourself a lot of grief later! 

               One or both of the files in the relation should be indexed on
the key field.  Usually you will find yourself linking a "many" file
to a "one" fileone parent record relates to many child records in the
other file.  As a general rule, index the child file on the linking
key field.

Now, place an example variable, a descriptive word or identifying
letter (like LINK1 or x), under the key field in both file skeletons. 
Congratulations!  You have successfully linked your two files!  Now
press F2 to display your results.

Double Linking

Now that you are an expert on linking files, you are bound to get
yourself into trouble one day.  You think you know all there is to
know about linking files, but you feel that you need to customize and
improve on what you already have. 

You've read all the books (including the small library of dBASE IV
manuals) about data normalization.  You have separated your clients
names into first name and last name fields thus, allowing you to index
on the last name field only.   But  you now realize that you need
single unique field in common between the two files but, alas, you
don't have one.  But wait, you do have two fields in both files that
have names in them.  The names are separated, with first name in one
and last name in the other.  

Now what do you do?  If you are like so many other folks, you will try
to solve the problem by linking the two files with two links!  It
sounds sensible: Just link last name to last name, and  first name to
first name.  Two links between the two files is an ingenious
solution!  You even set it up and view the results and it works. 
However, it takes a long time to set up the whole process.  The query
is just plain slow.   Is there a better way?

Complex Indexes

If you've done your homework, you probably saw something mentioned in
Using the Menu System about complex index tags and how they can be
used in QBE.  In fact, you can create a complex index on the
combination of last name and first name (for example Last + First) and
then, choosing Fields: Include indexes, display this combined, complex
index in the file skeleton, as if it were a field!  This gives you a
means of ordering a file by a complex index by placing the ASC sort
operator below the pseudo field, be it a combination of two fields, or
the result of many fields combined with any number of dBASE
functions.  Granted, you can do the functional equivalent of a complex
tag that simply combines two or three fields in QBE by placing ASC1
under the field that you want to have the highest indexing priority,
and ASC2 under the next field, and so on.  The problem with doing it
that way is that it forces dBASE IV to physically SORT your file which
can be a time-consuming process for a large file.  

A major advantage of the complex index scheme is that placing ASC1
under a field that has an associated index tag which will activate an
existing index tag..  This is indicated in QBE by a pound sign (#) in
front of a field when you have chosen Fields: Include indexes.  This
takes no extra time, and indexes perform rapidly by logically
ordering  your file.

File Skeletons In the Closet 

Hopefully, you now realize that you now have a helpful tool to use in
relating two files on a complex index by creating tags in both files
on the same complex index expression, then placing linking variables
below these two pseudo fields and link them!  This was a functionality
that was supposed to exist  in version 1.0  but it didn't work.  If
you tried to link two files by placing a example variable below the
two complex index pseudo fields, you got an annoying and confusing
error.  

Enter dBASE IV version 1.1, the capability of linking on complex index
tags (pseudo fields) is now available!  If you have created the
complex index tags, your query will be optimized in terms of speed of
creation and speed of operation.  If you don't have these indexes
previously created, QBE may have to set up filters to display related
data which can take a good chunk of time.    

Multiple links between files is now unnecessary.  The need for
long-winded filter conditions in conditions boxes is gone.  You could
even get to like QBE!   

So, when you see fit to link your files, but you have no single field
in common, don't despair, create!  Create a complex index tag that
contains some expression that is in common between the two files. 
Move into QBE and link up these two complex indexes as if they were
fields.  s
