Comparing queries and graphing results 1

[honeypot3]

Hi there

I have two queries, one which generates monthly averages for the whole company such as below:

Trade CoPercent
Brickwork 82%
Ceramic tiling 68%
Decoration 93%
Electrical 82%
Flooring 45%
Plastering 92%
Plumbing 85%
Roofing 88%
Scaffolding 64%

I need to compare it/join it with another query with specific percentages for a site such as the list below:

Trade SitePercent
Electrical 87%
Plastering 71%
Plumbing 75%
Roofing 86%
Scaffolding 63%

So I can end up with a list such as below that can then be graphed in Access - i.e. where there is no match between the Trade field a value of 0% is entered so the graph will function correctly:

Trade CoPercent SitePercent
Brickwork 82% 0%
Ceramic tiling 68% 0%
Decoration 93% 0%
Electrical 82% 87%
Flooring 45% 0%
Plastering 92% 71%
Plumbing 85% 75%
Roofing 88% 86%
Scaffolding 64% 63%

Thanks Sandra

 

[Remou]

Perhaps something like:
[tt]SELECT CoPercent.Trade, CoPercent.CoPercent, Nz([SitePercent],0) AS Expr1
FROM CoPercent LEFT JOIN SitePercent ON CoPercent.Trade = SitePercent.Trade;[/tt]

You may wish to use a table of trades.

 

[honeypot3]

Hi Remou

Thanks for trying but it seems to produce a lot of duplicates and no zeros - here is how I have translated the SQL above into my actual query (i.e. using query names etc. and selction criteria) Any other additions/changes to suggest to end up with list as above.

SELECT [NewExcelOutputTrade percent avgs per month].Month, [NewExcelOutputTrade percent avgs per month].Year, [NewExcelOutputTradeSchemePartAvg per month].[Contract number], [NewExcelOutputTrade percent avgs per month].Trade, [NewExcelOutputTrade percent avgs per month].Percent, Nz([NewExcelOutputTradeSchemePartAvg per month].[Percentage Average],0) AS Expr1
FROM [NewExcelOutputTrade percent avgs per month] LEFT JOIN [NewExcelOutputTradeSchemePartAvg per month] ON [NewExcelOutputTrade percent avgs per month].Trade = [NewExcelOutputTradeSchemePartAvg per month].Trade
WHERE ((([NewExcelOutputTrade percent avgs per month].Month)="jan") AND (([NewExcelOutputTrade percent avgs per month].Year)="2005") AND (([NewExcelOutputTradeSchemePartAvg per month].[Contract number])="613"));

 

[Remou]

Is the data you posted actual data? Are there duplicates in the tables / queries you are using? Do you have a table of trades that could be used as a definitive list? Perhaps the tables need to be joined by something as well as trade, though from the data posted, this is not clear.

 

[PHV]

My assumptions:
Both tables/queries should have a Month and Year field.

My guess:
SELECT C.Month, C.Year, S.[Contract number], C.Trade, C.Percent, Nz(S.[Percentage Average],0) AS Expr1
FROM [NewExcelOutputTrade percent avgs per month] AS C LEFT JOIN (
SELECT Trade, [Contract number], [Percentage Average] FROM [NewExcelOutputTradeSchemePartAvg per month]
WHERE [Month]='jan' AND [Year]='2005' AND [Contract number]='613'
) AS S ON C.Trade = S.Trade
WHERE C.Month='jan' AND C.Year='2005'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[honeypot3]

Hi PHV

I used your syntax exactly and sort of gout a few steps closer in that I now get zeros but for everything and the contract number column is completely blank. Can you suggest why - even if I remove the actual contract number to show all contracts it still remains blank.

 

[PHV]

Any chance you could post the real names of all the relevant fields, some input samples, actual result with the above input and expected result with same input ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[honeypot3]

Hi PHV - yes I can but I need to know how to make the output line up properly as when I just copy and paste the query results they end up wiggling (technical term!!) all over the page. I know you can use TGML but I cannot find the tag that keeps columns on info in line!! Can you let me know how to do that?

 

[PHV]

use [ignore][tt] and [/tt][/ignore]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[honeypot3]

OK here goes tried [tt] and [/tt] to space columns but still wiggly - have used [tab] and is a little better but still a little wiggly so apologies for display - the first query is called NewExcelOutputTradePercent avgs per month and uses the SQL:

SELECT Trade.Trade, (Avg([Resp])*8)/80 AS [Percent], ContJobQueResMonYearLink.Month, ContJobQueResMonYearLink.Year
FROM (Trade INNER JOIN ContractorDetails ON Trade.TradeID = ContractorDetails.TradeID) INNER JOIN ContJobQueResMonYearLink ON ContractorDetails.[Contractor ID] = ContJobQueResMonYearLink.CoID
GROUP BY Trade.Trade, ContJobQueResMonYearLink.Month, ContJobQueResMonYearLink.Year
ORDER BY Trade.Trade;

This gives the output below when the Month and Year are set to Jan 2005:
Trade[tab][tab][tab][tab]Percent[tab][tab]Month[tab][tab]Year
Brickwork[tab][tab][tab]84.17%[tab][tab]Jan [tab][tab]2005
Ceramic tiling[tab][tab]87.75%[tab][tab]Jan[tab][tab] 2005
Decoration[tab][tab][tab]91.25%[tab][tab]Jan[tab][tab] 2005
Electrical[tab][tab][tab]83.75%[tab][tab]Jan[tab][tab] 2005
Flooring[tab][tab][tab]78.75%[tab][tab]Jan[tab][tab]2005
Plastering[tab][tab][tab]71.88%[tab][tab]Jan[tab][tab]2005
Plumbing[tab][tab][tab]85.50%[tab][tab]Jan[tab][tab]2005
Roofing[tab][tab][tab]88.25%[tab][tab]Jan[tab][tab]2005
Scaffolding[tab][tab][tab]64.17%[tab][tab]Jan[tab][tab]2005

The second query is called NewExcelOutputTradeSchemePartAvg per month and has the SQL:

SELECT [NewExcelOutputPartner contract subquery].[Partnership Name], [NewExcelOutputPartner contract subquery].[Contract number], Trade.Trade, Avg([resp])*8/80 AS [Percentage Average], [NewExcelOutputPartner contract subquery].Month, [NewExcelOutputPartner contract subquery].Year
FROM ([NewExcelOutputPartner contract subquery] INNER JOIN ContractorDetails ON [NewExcelOutputPartner contract subquery].CoID = ContractorDetails.[Contractor ID]) INNER JOIN Trade ON ContractorDetails.TradeID = Trade.TradeID
GROUP BY [NewExcelOutputPartner contract subquery].[Partnership Name], [NewExcelOutputPartner contract subquery].[Contract name], [NewExcelOutputPartner contract subquery].[Contract number], Trade.Trade, [NewExcelOutputPartner contract subquery].Month, [NewExcelOutputPartner contract subquery].Year
HAVING ((([NewExcelOutputPartner contract subquery].[Partnership Name])="Blyth Housing") AND (([NewExcelOutputPartner contract subquery].[Contract number])="521a") AND (([NewExcelOutputPartner contract subquery].Month)="Jan") AND (([NewExcelOutputPartner contract subquery].Year)="2005"))
ORDER BY [NewExcelOutputPartner contract subquery].[Partnership Name], [NewExcelOutputPartner contract subquery].[Contract number], Trade.Trade;



This gives the results below:

Partnership Name Contract number Trade Percentage Average Month Year
Blyth Housing[tab][tab] 521a[tab][tab][tab] Electrical[tab][tab] 87.50%[tab][tab] Jan[tab][tab] 2005
Blyth Housing[tab][tab] 521a[tab][tab][tab] Plumbing[tab][tab] 87.50%[tab][tab] Jan[tab][tab] 2005
Blyth Housing[tab][tab] 521a[tab][tab][tab] Roofing[tab][tab] 86.25%[tab][tab] Jan[tab][tab] 2005

This second query is also based on the subquery NewExcelOutputPartner contract subquery as Access would not allow the precentages to be derived from linking the original tables together. This has the SQL:

SELECT PartnershipDetails.[Partnership Name], JobDetails.[Contract name], JobDetails.[Contract number], ContJobQueResMonYearLink.CoID, ContJobQueResMonYearLink.Resp, ContJobQueResMonYearLink.Month, ContJobQueResMonYearLink.Year
FROM (PartnershipDetails LEFT JOIN JobDetails ON PartnershipDetails.[Partnership ID] = JobDetails.PartnershipID) LEFT JOIN ContJobQueResMonYearLink ON JobDetails.[Contract number] = ContJobQueResMonYearLink.ContractNumber;


So what I am attempting to do is to join the results of those two queries so I can compare the percentages produced by the first query which are company overall percentages with the percentages of the second query which are contract specific so they can be graphed in an Access report. However in order to make this comparison I assume I will need matching columns of data so where a contract does not use a trade type 0% would be entered in its place so the output of the desired query would be:

Trade[tab][tab][tab] Overall Percent[tab]Contract Percent
Brickwork[tab][tab][tab] 84.17%[tab][tab]0.00%
Ceramic tiling[tab][tab] 87.75%[tab][tab]0.00%
Decoration[tab][tab][tab] 91.25%[tab][tab]0.00%
Electrical[tab][tab][tab] 83.75%[tab][tab]87.50%
Flooring[tab][tab][tab] 78.75%[tab][tab]0.00%
Plastering[tab][tab][tab] 71.88%[tab][tab]0.00%
Plumbing[tab][tab][tab] 85.50%[tab][tab]87.50%
Roofing [tab][tab][tab]88.25%[tab][tab]86.25%
Scaffolding[tab][tab] 64.17%[tab][tab]0.00%

However when I have tried previous solutions I have always ended up with duplicates nad no zeros produced until your last suggestion which produced this:

Month[tab] Year Contract number C.Trade[tab] Percent[tab] Expr1
Jan[tab] 2005[tab][tab][tab][tab][tab][tab] Brickwork[tab] 84.17%[tab] 0
Jan[tab] 2005[tab][tab][tab][tab][tab][tab] Ceramic tiling 87.75%[tab] 0
Jan[tab] 2005[tab][tab][tab][tab][tab][tab] Decoration[tab] 91.25%[tab] 0
Jan[tab] 2005[tab][tab][tab][tab][tab][tab] Electrical[tab] 83.75%[tab] 0
Jan[tab] 2005[tab][tab][tab][tab][tab][tab] Flooring[tab] 78.75%[tab] 0
Jan[tab] 2005[tab][tab][tab][tab][tab][tab] Plastering[tab] 71.88%[tab] 0
Jan[tab] 2005[tab][tab][tab][tab][tab][tab] Plumbing[tab] 85.50%[tab] 0
Jan[tab] 2005[tab][tab][tab][tab][tab][tab] Roofing[tab] 88.25%[tab] 0
Jan[tab] 2005[tab][tab][tab][tab][tab][tab] Scaffolding 64.17%[tab] 0

Phew - hope this is enough info to be going on with.

Thanks for your help, Sandra

 

[PHV]

What about replacing this:
AND [Contract number]='613'
with this ?
AND [Contract number]='521a'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[honeypot3]

It makes no difference the contract number is just completely blank regardless of which contract number is used or whether there is not one entered at all. It seems the syntax in your solution is just ignoring the contract number completely.

 

[PHV]

Here my results with your posted data:[tt]
Month Year Contract number Trade Percent Expr1
Jan 2005 Brickwork 84.17% 0
Jan 2005 Ceramic tiling 87.75% 0
Jan 2005 Decoration 91.25% 0
Jan 2005 521a Electrical 83.75% 87.50%
Jan 2005 Flooring 78.75% 0
Jan 2005 Plastering 71.88% 0
Jan 2005 521a Plumbing 85.50% 87.50%
Jan 2005 521a Roofing 88.25% 86.25%
Jan 2005 Scaffolding 64.17% 0[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[honeypot3]

Hi PHV

Well got it working eventually but only by making the two feeder queries into make table queries and then using your SQL on the new tables so thanks for pointing me in the right direction. Guess there must be something weird lurking further down in the query on a query etc., will check it out but thanks as at least I now have a solution.

Sandra

 

[honeypot3]

Hi again PHV - soss for disturbing you again - just one last issue on this - the solution works apart from the Nz function seems to convert the zeros and the percentages it produces to text so that they cannot be graphed properly in Access, I have tried changing the format in the query properties but there are no options listed as there usually are.

Any ideas? Sandra

 

[PHV]

You may try this:
Val(Nz(S.[Percentage Average],0)) AS Expr1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[honeypot3]

Thanks PHV that works a treat!

Sandra