Incorrect results from query

[NeilPattison]

Incorrect Information

--------------------------------------------------------------------------------

I have set up 2 queries which are working correctly. The problem is when I try to combine them it brings back incorrect information. The 2 queries that work correctly are set up like this

Query1:
SELECT Projects.[Work Stream], Count(Poles.[New Pole No]) AS [CountOfNew Pole No], Sum(Projects.[Line Length]) AS [SumOfLine Length], Projects.Team
FROM Projects INNER JOIN Poles ON Projects.[Scheme No] = Poles.[Scheme No]
GROUP BY Projects.[Work Stream], Projects.Team
HAVING (((Projects.Team)=[EnterTeam]));

Query2:
SELECT Projects.[Work Stream], Sum([Material Cost]+[Labour Cost]) AS [Total Cost]
FROM Rates INNER JOIN (Projects INNER JOIN [Pole Work Instructions] ON Projects.[Scheme No] = [Pole Work Instructions].[Scheme No]) ON Rates.[Rate No] = [Pole Work Instructions].[Rate No]
GROUP BY Projects.[Work Stream];

Do you have any idea how I can combine these to get accurate results?

 

[easyit]

Hi,

Could you tell us what/how you combine them? Now it is not clear, I just see two queries that work.

EasyIT

 

[DotNetGnat]

combine them in what sense??

you can use UNION ALL to get combined results...

-DNG

 

[PCLewis]

It's not clear what tables your [Material Cost] and [Labour Cost] fields belong to, but if you what to make one query to extract all of those fields, and assuming all of the joins remain the same:

SELECT Projects.[Work Stream], 
Sum([Material Cost]+[Labour Cost]) AS [Total Cost], 
Projects.[Work Stream], 
Count(Poles.[New Pole No]) AS [CountOfNew Pole No], 
Sum(Projects.[Line Length]) AS [SumOfLine Length], 
Projects.Team

FROM (Rates INNER JOIN 
  (Projects INNER JOIN 
    [Pole Work Instructions] ON Projects.[Scheme No] = [Pole Work Instructions].[Scheme No]) 
  ON Rates.[Rate No] = [Pole Work Instructions].[Rate No]) INNER JOIN Poles ON Projects.[Scheme No] = Poles.[Scheme No]

GROUP BY Projects.[Work Stream], Projects.[Work Stream], Projects.Team

HAVING Projects.Team=[EnterTeam];