Joining multiple queries (1 Viewer)

[billHassall]

I have created a number of queries that all give an output that gives a Region, company and a result (workstack, failed jobs, issued jobs completed work etc)
What I am trying to do is to combine these queries to give me one output that shows by region, by company the workstack, failed jobs etc

When I run the query that I have created instead of showing region1 company 1 and then all of the data, it duplicates the company info as follows

region company workstack failed
Region 1 comp 1 1400 4
Region 1 comp 1 1400 164
Region 1 comp 2 52 4
Region 1 comp 2 52 164

What I was expecting was
region company workstack failed
Region 1 comp 1 1400 4
Region 1 comp 2 52 164

Any help would be appreciated

 

[Chris RR]

Hi,
Is the third query (the one that joins the previous queries) summing up the number of failed jobs by region, company and workstack? From your data, I'd then expect a result of
region company workstack failed
Region 1 comp 1 1400 168
Region 2 comp 2 52 168

 

[billHassall]

Have tried that many different combinations, I can't honestly remember, will check in the morning when back in work, but from memory I don't think it is SUM in query three, I know it is definitely COUNT in one and two

Thanks

 

[billHassall]

Query 3 had "Group by" and not "sum" against the workstack failed jobs etc, I have changed them all to "sum" and I am now getting the output in the required
Region 1 comp 1 1400 168
Region 2 comp 2 52 168

but instead of 1400 against comp1, which is the right answer, I am getting 2800 and 104 against comp 2

I have included the SQL for Query 3

SELECT DISTINCT [0 Total Workstack].Region, [0 Total Workstack].[Comp Name], Sum([0 Total Workstack].Estimates) AS [Total Ws], Sum([1 Narrowband Workstack].Estimates) AS [NB Ws], Sum([2 Wideband Workstack].Estimates) AS [WB Ws], Sum([3 NGA Workstack].Estimates) AS [NGA Ws], Sum([0 Total Past ARBD].Estimates) AS [Total ARBD], Sum([1 Narrowband Past ARBD National].Estimates) AS [NB ARBD], Sum([2 Wideband Past ARBD National].Estimates) AS [WB ARBD], Sum([3 NGA Past ARBD National].Estimates) AS [NGA ARBD], Sum([0 Total Jobs Issued].Estimates) AS [Total Iss], Sum([1 Narrowband Jobs Issued].Estimates) AS [NB Iss], Sum([2 Wideband Jobs Issued].Estimates) AS [WB Iss], Sum([3 NGA Jobs Issued].Estimates) AS [NGA Iss]
FROM (((((((((([0 Total Workstack] LEFT JOIN [1 Narrowband Workstack] ON [0 Total Workstack].Region = [1 Narrowband Workstack].Region) LEFT JOIN [2 Wideband Workstack] ON [0 Total Workstack].Region = [2 Wideband Workstack].Region) LEFT JOIN [3 NGA Workstack] ON [0 Total Workstack].Region = [3 NGA Workstack].Region) LEFT JOIN [0 Total Past ARBD] ON [0 Total Workstack].Region = [0 Total Past ARBD].Region) LEFT JOIN [1 Narrowband Past ARBD National] ON [0 Total Workstack].Region = [1 Narrowband Past ARBD National].Region) LEFT JOIN [2 Wideband Past ARBD National] ON [0 Total Workstack].Region = [2 Wideband Past ARBD National].Region) LEFT JOIN [3 NGA Past ARBD National] ON [0 Total Workstack].Region = [3 NGA Past ARBD National].Region) LEFT JOIN [0 Total Jobs Issued] ON [0 Total Workstack].Region = [0 Total Jobs Issued].Region) LEFT JOIN [1 Narrowband Jobs Issued] ON [0 Total Workstack].Region = [1 Narrowband Jobs Issued].Region) LEFT JOIN [2 Wideband Jobs Issued] ON [0 Total Workstack].Region = [2 Wideband Jobs Issued].Region) LEFT JOIN [3 NGA Jobs Issued] ON [0 Total Workstack].Region = [3 NGA Jobs Issued].Region
GROUP BY [0 Total Workstack].Region, [0 Total Workstack].[Comp Name]
ORDER BY [0 Total Workstack].Region, [0 Total Workstack].[Comp Name];