Joining queries

[jbphoenix]

I need to join these 2 queries and I can't seem to get it to work

select [Part Number], [Invt GLG], (Sum(TEST.CYUsage)*-1) as 'CY', (sum(TEST.LYUsage)*-1) as 'LY'
from
(Select [Part Number], [Trans Dt], [Invt GLG],
case
when year([Trans Dt]) = '2008' then [Qty]
Else 0
End 'CYUsage',
case
when year([Trans Dt])='2007' then [Qty]
Else 0
End 'LYUsage'
From [tblEMG_LIVE_INVTRAIL-3] where [Qty] < 0) as TEST
Where [Part Number] is not null
Group By [Part Number], [Invt GLG]
Order by [Part Number]

SELECT [tblEMG_LIVE_INVWHS-3].[Part Number], SUM(CONVERT(numeric(18, 2), [tblEMG_LIVE_INVWHS-3].[Qty On Hand])) AS 'QOH',
SUM(CONVERT(numeric(18, 6), [tblEMG_LIVE_INVWHS-3].[Average Cost])) AS 'AvgCost', [tblEMG_LIVE_INVTRAIL-3].[Invt GLG]
FROM [tblEMG_LIVE_INVWHS-3] INNER JOIN
[tblEMG_LIVE_INVTRAIL-3] ON [tblEMG_LIVE_INVWHS-3].[Part Number] = [tblEMG_LIVE_INVTRAIL-3].[Part Number]
WHERE ([tblEMG_LIVE_INVWHS-3].[Average Cost] <> '')
GROUP BY [tblEMG_LIVE_INVWHS-3].[Part Number], [tblEMG_LIVE_INVTRAIL-3].[Invt GLG]

 

[Fear Naught]

Have you tried using the UNION operator? You will need to remove the Order By clause from the the first query though for it to work.

 

[namliam]

1) Please dont plash code on the forum and expect us to be able to read it
a) Use the [code ] and [/code ] (without the spaces) around the SQL
b) Indent the SQL to be readable

2) Dont use ' for Column names/aliasses, ' are for strings not aliasses (yes its allowed, but... GRMBL) use " instead.

3)
Where [Part Number] isnotnull

isnotnull???? Or Is Not Null ? <= Probably a forum formatting thing?

4)
If you want to join two queries, the easiest way (without looking into -more- detail of your queries) is to make subselects out of them.

Select 
....
From  (Query1) as Q1
join   (Query2) as Q2 on ...
where ...

Good luck! And MERRY NEW YEAR!