How can I combine data from multiple data folders in one query?
- 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.
- Edit the single-folder query.
- 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:
- 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"
- 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' Click OK in the SQL Editor window and confirm that the query still refreshes normally.
- Transform into a multi-folder query.
- Select a cell inside the range containing the returned data and click SQL Editor on the Office Connector toolbar.
- Select all of the SQL text shown in the window and press Ctrl+C to copy it to the clipboard.
- Place your cursor on a new line at the end of the SQL text (with no text selected).
- Type the words: UNION ALL Note - You can eliminate identical rows from the results by omitting the word ALL
- 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.
- 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'
- 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"
- Click OK in the SQL Editor window and confirm that the query still refreshes normally and now returns data from both data folders.
- Repeat step 3 for each additional data folder you wish to include.
- 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 Timberline Office 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
|C:Program FilesTimberline OfficeAccountingConstruction Sample Data||70||C:PROGRA~1TIMBER~1ACCOUN~1CONSTR~1|
|C:Program FilesTimberline OfficeAccountingElectrical Contractor Sample Data||79||C:PROGRA~1TIMBER~1ACCOUN~1ELECTR~1|
|C:Program FilesTimberline OfficeAccountingHome Builder Sample Data||70||C:PROGRA~1TIMBER~1ACCOUN~1HOMEBU~1|
|C:Program FilesTimberline OfficeAccountingPM Sample Data||60||C:PROGRA~1TIMBER~1ACCOUN~1PMSAMP~1|