PRB: Query Wizard Cannot Set Relation If Tables Have Same Name

• Microsoft FoxPro for Windows, version 2.6
• Microsoft FoxPro for MS-DOS, version 2.6

SYMPTOMS

The Query Wizard will be unable to set a relation between two tables if the filenames of the two tables are the same.

WORKAROUND

There are two possible workarounds:

• Rename one of the tables so that the filenames are no longer identical. This permits the Query Wizard to generate unique alias names.

  -or-

• Use the Relational Query By Example (RQBE) tool to create the query. The RQBE tool allows two tables to be joined based on the alias name. In order for this query to work properly, all tables must be opened in the future with the same alias names as were used when the query was created.

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce Behavior

1. In the Command window, issue the following command:

         USE <FoxPro_directory>\tutorial\customer
   

2. Issue the following command in the Command window where <other_directory> is any valid, existing directory name:

         COPY TO <other_directory>\customer
   

3. Start the Catalog Manager and create a new catalog.

4. Add <FOXPRO_DIRECTORY>\TUTORIAL\CUSTOMER to the catalog.

5. Add <OTHER_DIRECTORY>\CUSTOMER to the catalog.

6. Choose the Query tab and then choose New. Choose the Query Wizard.

7. Select either the SQL Query Wizard or the Updatable Query Wizard.

8. Add both versions of the CUSTOMER table to the query.

This behavior occurs because the Query Wizard depends on the filename matching the alias name for the table.

Under most circumstances, the filename and the alias name are the same. When two files with the same name are opened simultaneously in different work areas, FoxPro assigns an alias to the second file. This alias will change based on which work area is used to open the file. The alias name may be a single letter or the filename with an underscore and number added to the end.

All queries use the alias, not the filename, of the table to retrieve data.

The Query Wizard must create an alias name that will remain consistent regardless of the work area used to open the files. Internal functions are used to create an alias for each open .DBF file that exactly matches the filename. This is impossible to do when two files share exactly the same name.

REFERENCES

For more information about aliases, refer to the ALIAS() and DBF() functions in the FoxPro "Language Reference."