The Sage 100 Contractor database does not provide a single table that lists all of the cost codes associated with a given job. The cost codes that are associated with (have been used with) a given job can be found in four separate tables. They include the original budget, budget changes, actual costs, and purchase orders. This article provides an SQL statement that combines the list of cost codes from each of these tables into a single list of the distinct codes that have been used to-date.Step 1 - Create a simple query
Use the Query Wizard to create a query using the PM Budget Line (bdglin) table. Include the Phase (phsnum) and Cost Code (cstcde) columns and set a condition based on the job so that it only returns the cost codes for the desired job (e.g., using a cell on the worksheet to identify the job).Step 2 - Replace the SQL
- Select a cell anywhere inside the list of cost codes returned in step 1.
- On the Liberty Reports tab, click Edit SQL to display the Edit SQL Text window.
- Replace the text shown in the window with the following text:
SELECT DISTINCT bdglin.phsnum AS Phase, bdglin.cstcde AS Cost_Code FROM bdglin WHERE bdglin.recnum=? UNION SELECT DISTINCT prmchg.phsnum AS Phase, sbcgln.cstcde AS Cost_Code FROM sbcgln LEFT OUTER JOIN prmchg ON sbcgln.recnum = prmchg.recnum WHERE prmchg.jobnum=? UNION SELECT DISTINCT jobcst.phsnum AS Phase, jobcst.cstcde AS Cost_Code FROM jobcst WHERE jobcst.jobnum=? UNION SELECT DISTINCT pchord.phsnum AS Phase, pcorln.cstcde AS Cost_Code FROM pcorln LEFT OUTER JOIN pchord ON pcorln.recnum = pchord.recnum WHERE pchord.jobnum=? AND pchord.status<5 ORDER BY 1,2
- Click OK
- You will be prompted to supply a value for Parameter1 through Parameter4. For each, select the cell containing the job number and check the box entitled Use this value/refernece for future refreshes.
The above SQL statement consists of four separate queries (SELECT statements), one for each of the four tables that contain cost codes associated with the job. The four queries are combined into one query by using the UNION statements. The
DISTINCT keyword that appears near the top (on the first query) applies to the entire union query so that the query does not return duplicates. The
ORDER BY statement at the end causes the results of the entire union query to be sorted by columns 1 and 2.
Use the Function Wizard or other Excel formulas to add columns adjacent to the column list that will pull other data values or perform calculations. For example, the Function Wizard could be used to insert a DBLookup function that returns the description of each cost code:
=DBLookup("Sage 100 Contractor", "cstcde", "cdenme", $B5) & ""
Other functions (such as DBSum or DBFind) may be useful for obtaining other values such as budget amounts and actual cost.