Excel SQL Query - Need an Outer Join

[rwn]

I have this Excel Query below. I need to do an Outer Join to show all Accounts(field name) in the Accounts Table and Accounts (field name) in the Cost table. Plus, i need the other Tables as they are linked. How can one get around excel's limitation for not allowing out linking? thank you!

SELECT Cost.Account, Sum(MTD_Amt)*-1, Account.Account
FROM TRAINING.dbo.Account Account, TRAINING.dbo.Cost Cost, TRAINING.dbo.Fiscal_Period Fiscal_Period, TRAINING.dbo.Fiscal_Year Fiscal_Year
WHERE Fiscal_Period.Fiscal_Year = Fiscal_Year.Fiscal_Year AND Cost.Period = Fiscal_Period.Fiscal_Period AND Account.Account = Cost.Account AND ((Fiscal_Year.Name='2014'))
GROUP BY Cost.Account, Account.Account

 

[SkipVought]

Hi,

Yes, MS Query has some limitations regarding outer joins.

I'd do two queries on separate sheets: one reporting all accounts in accounts and the other reporting accounts in cost.

Then do a lookup from the accounts sheet to return values from the cost sheet where they exist.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[rwn]

Thank you. I have no idea how to create two queries with-in Excel SQL and have them linked as an Outer Join.

 

[SkipVought]

One querytable in Sheet1, from Account and fiscals
One querytable in Sheet2 from Cost and fiscals

Presumable, Sheet1 will have more accounts than Sheet2, which is why you wanted to do an outher join, right?
So, use the Account in Sheet1 in a lookup(s) into the table in Sheet2, to return to Sheet1 the value(s) from Sheet2 that are there.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[rwn]

I have one query in Sheet one using Table Accounts, i have the other query in Sheet two using Table Cost. How do i link them now from Accounts.Accounts Outer Join (Sheet one) to Cost.Account (Sheet Two)? im not sure where in excel this is done? thanks!!

 

[SkipVought]

How do i link them now from Accounts.Accounts Outer Join (Sheet one) to Cost.Account (Sheet Two)?

Well you could on Sheet3. faq68-5829

But I was suggesting that you can ALSO achieve the same result doing a lookup.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}