combining data in 2 tables via query

[dragct]

I have two tables of budget data.

EssBase

• Pulls original approved budget by various fields (fund, location, cost element, department and account - known as the GL String)
• Ex: 10-0000-4-0000000151-7300, $1500.00 (Approved budget)

PeopleSoft

• Pulls current adjusted budget & expense by various fields (fund, location, cost element, department and account - known as the GL String)
• Ex: 10-0000-4-0000000151-7300, $2000.00 (Adjusted budget)
• Ex: 10-0000-4-0000000151-7300, $500.00 (Expense)

I am trying to build a query that pulls all data between the two that CONTAINS any Data (Dollar Amt) in the following fields

• Approved Budget (1st table)
• Adjusted Budget (2nd table)
• Expense (2nd Table)

Example of what is needed

GL String Appv Budget Adj Budget Expense
10-0000-4-%151-7300 $1500.00 $2000.00 $500.00
10-0000-4-%151-7400 0.00 100.00 50.00
10-0000-6-%174-7200 2000.00 2500.00 500.00
10-0000-6-%174-7300 1000.00 0.00 0.00

Thanks in advance for any assistance you can provide. I know that I am missing something, just not sure what.

Thanks, Kim

I have the following built, but in reviewing my data, I know it is not including everything.


SELECT TBL_Departments.[Department Name], TBL_Departments.Department, TBL_Fund.Fund, TBL_Location.Location, [TBL_Cost Element].[Cost Element], TBL_Accounts.Account, TBL_Accounts.[Account Name], DWNLD_Essbase_Hyperion.Current_Yr_Appv_Budget, DWNLD_OV_Budget.Budget, DWNLD_OV_Budget.Expense
FROM (TBL_Location INNER JOIN ([TBL_Cost Element] INNER JOIN (((DWNLD_Essbase_Hyperion INNER JOIN TBL_Departments ON DWNLD_Essbase_Hyperion.Department = TBL_Departments.Department) INNER JOIN TBL_Accounts ON DWNLD_Essbase_Hyperion.Account = TBL_Accounts.Account) INNER JOIN DWNLD_OV_Budget ON (TBL_Departments.Department = DWNLD_OV_Budget.Dept) AND (TBL_Accounts.Account = DWNLD_OV_Budget.Account)) ON (DWNLD_Essbase_Hyperion.CE = [TBL_Cost Element].[Cost Element]) AND ([TBL_Cost Element].[Cost Element] = DWNLD_OV_Budget.[Cst Element])) ON (DWNLD_Essbase_Hyperion.Loc = TBL_Location.Location) AND (TBL_Location.Location = DWNLD_OV_Budget.Location)) INNER JOIN TBL_Fund ON (TBL_Fund.Fund = DWNLD_OV_Budget.Fund) AND (DWNLD_Essbase_Hyperion.Fund = TBL_Fund.Fund)
WHERE (((TBL_Accounts.Account) Like "##00"))
ORDER BY TBL_Departments.Department, TBL_Accounts.Account;

 

[llkhoutx]

Create a Union query with the 2 tables, then in another query sum the vale columns of the Union query.

 

[arnelgp]

Select EssBase.[GL String], EssBase.[App Budget], PeopleSoft.[Adj Budget], PeopleSoft.[Expense] From EssBase Inner Join PeopleSoft On EssBase.[GL String]=PeopleSoft.[GL String]

 

[dragct]

a Union Query worked for this. Thanks!