Join and Outer Join Are Case-Sensitive in Excel

SUMMARY

The Join and Outer Join commands are used to combine records from two query files in Microsoft Q+E for Excel based on the common values selected in each window. Both of these commands are case-sensitive when you are using the dBASEFile, ExcelFile, and TextFile drivers. Therefore, they will not join with records that have common values that are of a different case.

The SQLServer driver will only compare case if you have set up your SQLServer to be case-sensitive.

MORE INFORMATION

You may modify the query by hand, adding one of the dBASE operators, to convert the fields to a common case before the comparison is made.

Workarounds

1. Modify one of the files so that all names are the same case.

2. Repeat the Join or Outer Join command in Q+E.

   -or-

3. Follow the eight "Steps to Reproduce Problem" below.

4. From the Select menu, choose SQL Query.

5. Modify the query using the Upper function to make the compared fields the same case:

         SELECT EXAMPLE1.NAME, AGE, SEX
         FROM C:\EXCEL\QE\EXAMPLE1.XLS, C:\EXCEL\QE\EXAMPLE2.XLS
         WHERE upper(EXAMPLE1.NAME) = upper(EXAMPLE2.NAME)
   

6. Choose OK

   Bob     21      M
   Alice   18      F

Steps to Reproduce Problem

1. Create a spreadsheet with the following information:

         A1: NAME        B1: AGE
         A2: Bob         B2: 21
         A3: Alice       B3: 18
   
   

2. Select the range A1 through B3. From the Data menu, choose Set Database.

3. Close and save the file.

4. Create another spreadsheet with the following information:

         A1: NAME        B1: SEX
         A2: BOB         B2: M
         A3: ALICE       B3: F
   
   

5. Select the range A1 through B3. From the Data menu, choose Set Database.

6. Close and save the file.

7. Open both files in Q+E.

8. Select the NAME field in each file and choose Join from the Select menu in Q+E.

Note: If you choose Outer Join in step 8 instead of Join, only the records in the destination window will be displayed in the new query window.

REFERENCES

"Q+E for Microsoft Excel User's Guide." Version 3.0, pages 16-18, 131