Thank You...
I added a new table third table and am linking to the other two detail tables. 1 to Many relationship to both tables. SQL is below.
The issue that I have is that the APPROP(ie.budget) table has one DEPT_BUDGT per year but the salary proj table has the DEPT_BUDGT by employee - so multiple DEP_BDGT fields because it is so much more detail. The EXP_BDGT table has to be summed to the DEPT_BDGT level...it has many accounting periods.
I want to get by DEPT_BUDGET W_SALARY_ACTUALS W_EXPENDED_AMT and Difference
I've narrowed to focus on one DEPT_BUDGT so what I want is:
DEPT_BUDGT W_EXPENDED_AMT W_SALARY_ACTUALS DIFFERENCE
G1001 1000 600 400
I can get one side of the equation easily using running totals and evaluate but I can get both together to compare. I would like to avoid sub-reports if possible.
I think it might be able to be solved with stored procedure but running total sounds easier.
SELECT DISTINCT
APPROP."DEPT_BUDGT", APPROP."BUDGET_PERIOD",
EXP_BDGT."ACCOUNT", EXP_BDGT."W_EXPENDED_AMT",
SAL_PROJ."ACCOUNT", SAL_PROJ."W_SALARY_ACTUALS"
FROM
"SYSADM"."APPROP" APPROP,
"SYSADM"."EXP_BDGT" EXP_BDGT,
"SYSADM"."SAL_PROJ" SAL_PROJ
WHERE
APPROP."DEPT_BUDGT" = EXP_BDGT."DEPT_BUDGT" (+) AND
APPROP."FUND_CODE" = EXP_BDGT."FUND_CODE" (+) AND
APPROP."BUDGET_PERIOD" = EXP_BDGT."BUDGET_PERIOD" (+) AND
APPROP."BUDGET_PERIOD" = SAL_PROJ."FISCAL_YEAR" (+) AND
APPROP."FUND_CODE" = SAL_PROJ."FUND_CODE" (+) AND
APPROP."DEPT_BUDGT" = SAL_PROJ."DEPT_BUDGT" (+) AND
APPROP."BUDGET_PERIOD" = '2013' AND
APPROP."DEPT_BUDGT" = 'G1001' AND
SAL_PROJ."ACCOUNT" = '4100' AND
EXP_BDGT."ACCOUNT" = '4100' AND SAL_PROJ."FISCAL_YEAR" = 2013 AND SAL_PROJ."DEPT_BUDGT" = 'G1001' AND SAL_PROJ."ACCOUNT" = '4100' AND EXP_BDGT."BUDGET_PERIOD" = '2013'
ORDER BY
APPROP."DEPT_BUDGT" ASC