How can I combine data from multiple data folders in one query?

Background

An ordinary query inserted into a worksheet using Office Connector only queries data from a single Sage 300 Construction and Real Estate data folder. By editing the SQL text of the query afterword, you can include the data from the same tables in other data folders.

Solution

The solution is create a UNION query.  This is essentially like having multiple queries that are combined together so that their results are stacked one on top of the other.  To start the process, you can use the query wizard within Office Connector to create a single-folder query.  This will act as the template for the other queries that will be added and stacked on top of each other.

1. Create a single-folder query.
   Insert your query using the Office Connector wizard. For example, let's assume you selected the AP Vendor table, clicked the Vendor, Name, City, State and Zip columns and then added conditions to only include vendors where the Type is Subcontractor.
2. Edit the single-folder query.
   1. Select a cell inside the range containing the returned data and click SQL Editor on the Office Connector toolbar. Given the example, the SQL text would look like the following:
      
      
   2. The table name of the query is shown following the FROM keyword. In this case, the table name is "MASTER_APM_RECORD_9" which is the internal name for the AP Vendor table. Type a simpler alternate name immediately following the table name as shown here. This name is called the table-alias.
      FROM "MASTER_APM_RECORD_9" "AP Vendor1"
   3. Next, include the table-alias and a period just before each column name in the query. This will result in fully-qualified column name references as shown here:
      
      SELECT
"AP Vendor1"."VENDOR" AS "Vendor",
"AP Vendor1"."VNAME" AS "Name",
"AP Vendor1"."VCITY" AS "City",
"AP Vendor1"."VSTATE" AS "State",
"AP Vendor1"."VZIP" AS "ZIP"

FROM
"MASTER_APM_RECORD_9" "AP Vendor1"

WHERE
"AP Vendor1"."VTYPE"='Subcontractor'
      
      
   4. Click OK in the SQL Editor window and confirm that the query still refreshes normally.
3. Transform into a multi-folder query.
   1. Select a cell inside the range containing the returned data and click SQL Editor on the Office Connector toolbar.
   2. Select all of the SQL text shown in the window and press Ctrl+C to copy it to the clipboard.
   3. Place your cursor on a new line at the end of the SQL text (with no text selected).
   4. Type the words: UNION ALL
      Note - You can eliminate identical rows from the results by omitting the word ALL
   5. Place your cursor on a new line at the end of the SQL text and press Ctrl+V to paste a copy of the SQL text.
   6. In the newly pasted SQL text, replace each occurrence of the original table-alias with a new one as shown here.
      
      SELECT
"AP Vendor2"."VENDOR" AS "Vendor",
"AP Vendor2"."VNAME" AS "Name",
"AP Vendor2"."VCITY" AS "City",
"AP Vendor2"."VSTATE" AS "State",
"AP Vendor2"."VZIP" AS "ZIP"

FROM
"MASTER_APM_RECORD_9" "AP Vendor2"

WHERE
"AP Vendor2"."VTYPE"='Subcontractor'
   7. Add the path of the new data folder just before the table name as shown here.
      FROM
"T:\TimberlineMyOtherCompany"."MASTER_APM_RECORD_9" "AP Vendor2"
   8. Click OK in the SQL Editor window and confirm that the query still refreshes normally and now returns data from both data folders.
4. Repeat step 3 for each additional data folder you wish to include.

Result

The final query would look like this:

SELECT
"AP Vendor1"."VENDOR" AS "Vendor",
"AP Vendor1"."VNAME" AS "Name",
"AP Vendor1"."VCITY" AS "City",
"AP Vendor1"."VSTATE" AS "State",
"AP Vendor1"."VZIP" AS "ZIP"

FROM
"MASTER_APM_RECORD_9" "AP Vendor1"

WHERE
"AP Vendor1"."VTYPE"='Subcontractor'

UNION ALL

SELECT
"AP Vendor2"."VENDOR" AS "Vendor",
"AP Vendor2"."VNAME" AS "Name",
"AP Vendor2"."VCITY" AS "City",
"AP Vendor2"."VSTATE" AS "State",
"AP Vendor2"."VZIP" AS "ZIP"

FROM
"T:\TimberlineMyOtherCompany"."MASTER_APM_RECORD_9" "AP Vendor2"

WHERE
"AP Vendor2"."VTYPE"='Subcontractor'

UNION ALL

SELECT
"AP Vendor3"."VENDOR" AS "Vendor",
"AP Vendor3"."VNAME" AS "Name",
"AP Vendor3"."VCITY" AS "City",
"AP Vendor3"."VSTATE" AS "State",
"AP Vendor3"."VZIP" AS "ZIP"

FROM
"T:\TimberlineAnotherCompany"."MASTER_APM_RECORD_9" "AP Vendor3"

WHERE
"AP Vendor3"."VTYPE"='Subcontractor'

Notes

• To eliminate identical rows from the results of any given query, type the word DISTINCT immediate after the word SELECT.  Example:
  SELECT DISTINCT
• To eliminate identical rows that may appear if the same data is in two or more different data folders, don't include the word "ALL" after the word "UNION".
• If you would like the results of the UNION query to be sorted in a specific way, add an ORDER BY clause at the end of the last query.  Instead of identifying columns by name, use their ordinal position.  For example:
  ORDER BY 2, 3, 4
• The Sage 300 Construction and Real Estate ODBC Driver does not support data folder paths longer than 68 characters.  To work around this limitation, you may use the short-path equivalent.  The table below shows examples of data folder paths and their short-path equivalent.  To determine the short-name for a folder, you can use the DIR command at a command prompt with the /X switch.  Example:  DIR /X