3305 Main Street Ste 019 Vancouver, WA 98663

How can I combine Sage 100 Contractor data from multiple databases in a single query?

Background

The Liberty Reports Query Wizard will create a query that only uses a single database as the source of the information. This article addresses how to edit the SQL statement of the query so that it will query data from more than one database and combine the data into one set of results.

This approach uses a "UNION" query. Click here to learn more about union queries.

Syntax

The approach to combine data from multiple databases in a single query involves the use of a UNION query. Each part of the UNION query is responsible for querying data from a specific database. The following example starts with a simple query that returns a list of jobs. This query was created using the Liberty Reports query wizard.

SELECT   recnum AS Job,   jobnme AS Job_Name FROM   actrec

To amend this query so that it combines the list of jobs from two databases, you would create a UNION query that repeats the same statement and then replace the table name in the FROM clause with the full path to the data files:

SELECT   recnum AS Job,   jobnme AS Job_Name FROM   "M:\MB7\MyCompany\actrec.dbf"

UNION ALL
SELECT   recnum AS Job,   jobnme AS Job_Name FROM   "M:\MB7\2014Archive\actrec.dbf"

NOTE - The table name in each part of the UNION is now proceeded by the full path to the database and is followed by the file extension (.dbf). Also, in the above example, "UNION ALL" is used so that the query returns all rows from each part of the query. If the "ALL" keyword is not included, duplicate rows will not be included in the results.

See also