Not Equal to & Grouping Query

[niri77]

I have Table as PL2008 and RPT2. Both tables have one column named PTUNID. My goal is to first Group, sum certain Columns in the PL2008 then match PTUNID from this Grouping to RPT2 Table's column PTUNID and then show the results which do not match.

I have written following Qry but what really happenning is its giving me all the results ( match and no match in both tables ) and Sum values are incorrect.

SELECT [PL2008].PTUNID, Sum([PL2008].[Total Hours]) AS [SumOfTotal Hours], Sum([PL2008].[Actual Cost]) AS [SumOfActual Cost], [PL2008].[PRFIELD]
FROM RPT2, [PL2008]
WHERE RPT2.PTUNID <> [PL2008].PTUNID
GROUP BY [PL2008].PTUNID, [PL2008].[PRFIELD];


Any suggesstion



Thanks
Niri

 

[WayneRyan]

Niri,

To get all matching ...

SELECT [PL2008].PTUNID, 
       Sum([PL2008].[Total Hours]) AS [SumOfTotal Hours], 
       Sum([PL2008].[Actual Cost]) AS [SumOfActual Cost], 
       [PL2008].[PRFIELD]
FROM RPT2 Inner Join [PL2008] On 
       RPT2.PTUNID = [PL2008].PTUNID
GROUP BY [PL2008].PTUNID, [PL2008].[PRFIELD];

To get all PL2008 without matching RPT2...

SELECT [PL2008].PTUNID, 
       [PL2008].[PRFIELD]
FROM PL2008 Left Join RPT2 On 
       PL2008.PTUNID = RPT2.PTUNID
Where  RPT2.PTUNID Is Null

hth,
Wayne

 

[niri77]

That was really interesting to see that Null could work on Left Join to give not equal to Values.

Thanks a lot.It worked.

Niri

 

[WayneRyan]

Niri,

Glad to help.

See ya,
Wayne