joining queries together

[jeannier1975]

i have two queries I want to join so i can get the percentages from each department of completed work orders but not sure how to go about it. I know i want a join and a crosstab query so i can display the results in a report.
The first query calculates the numerator,

SELECT Count(MaximoReport.WorkOrder) AS CountOfWorkOrder, 
       MaximoReport.[Assigned Owner Group] 
FROM MaximoReport
WHERE (((MaximoReport.WorkType) In ("PMINS","PMOR","PMPDM","PMREG","PMRT")) 
AND ((MaximoReport.Status) Like "*COMP") 
AND ((MaximoReport.[Target Start])>=DateAdd("h",-1,[Enter the start date]) 
And (MaximoReport.[Target Start])<DateAdd("h",23,[Enter the end date])) 
AND ((MaximoReport.ActualLaborHours)<>"00:00") 
AND ((MaximoReport.ActualStartDate)>=DateAdd("h",-11.8,[Enter the start date]) 
And (MaximoReport.ActualStartDate)<DateAdd("h",23,[Enter the end date])))
GROUP BY MaximoReport.[Assigned Owner Group];

while the second query calculates the denominator:

SELECT Count(MaximoReport.WorkOrder) AS CountOfWorkOrder, 
       MaximoReport.[Assigned Owner Group]
FROM MaximoReport
WHERE (((MaximoReport.WorkType) In ("PMINS","PMOR","PMPDM","PMREG","PMRT")) 
AND ((MaximoReport.Status)<>"CAN") 
AND ((MaximoReport.[Target Start])>=DateAdd("h",-11.8,[Enter the start date]) 
And (MaximoReport.[Target Start])<DateAdd("h",23,[Enter the end date])))
GROUP BY MaximoReport.[Assigned Owner Group];

please advise how i cann join the two queries to get the percentages of the departments and then do a crosstab query.
If there a better way of doing this please also let me know.

Thought of running the query this way but I get errors.

SELECT SUM(iif((MaximoReport.Status Like "*COMP"),iif((MaximoReport.ActualLaborHours<>"00:00"),1,0),0)) AS CountCompleted,
       SUM(iif((MaximoReport.Status) <> "CAN",1,0)) AS CountTotal, 
       Round([CountCompleted]/[CountTotal]*100,1) AS PercentCompleted, 
       MaximoReport.[Assigned Owner Group]
FROM MaximoReport
WHERE (((MaximoReport.WorkType) In ("PMINS","PMOR","PMPDM","PMREG","PMRT")) 
AND ((MaximoReport.[Target Start])>=DateAdd("h",-1,[Enter the start date]) 
AND (MaximoReport.[Target Start])<DateAdd("h",23,[Enter the end date])) 
AND ((MaximoReport.ActualStartDate)>=DateAdd("h",-11.8,[Enter the start date]) 
AND (MaximoReport.ActualStartDate)<DateAdd("h",23,[Enter the end date])))
GROUP BY MaximoReport.[Assigned Owner Group];

[twinnyfo]

Similar to yours, but try this (I can't vouch for my parentheses):

SELECT MaximoReport.[Assigned Owner Group], 
       Sum(IIf([MaximoReport].[Status] Like "*COMP", 1, 0)) AS CompleteWorkOrders,
       Sum(IIf([MaximoReport].[Status]<>"CAN", 1, 0)) AS TotalWorkOrders, 
       Round(CompleteWorkOrders/CompleteWorkOrders*100, 1) AS PercentCompleted 
FROM MaximoReport
WHERE (((MaximoReport.WorkType) In ("PMINS","PMOR","PMPDM","PMREG","PMRT")) 
AND (((MaximoReport.[Target Start])>=DateAdd("h",-1,[Enter the start date]) 
And (MaximoReport.[Target Start])<DateAdd("h",23,[Enter the end date])) 
AND ((MaximoReport.ActualLaborHours)<>"00:00") 
AND ((MaximoReport.ActualStartDate)>=DateAdd("h",-11.8,[Enter the start date]) 
And (MaximoReport.ActualStartDate)<DateAdd("h",23,[Enter the end date])))
GROUP BY MaximoReport.[Assigned Owner Group];

[jeannier1975]

when i do that it doesnt get the right results for department presentages

[jeannier1975]

The numbers are not coming out is there a better way to do what im trying if i run the department queries seperate they work but when i try to do it with one query the numbers dont match.

[twinnyfo]

I trust that your response is correct. However, how do you know that the values are incorrect? What indicators do you have that the total work orders and the total complete work orders are not being counted correctly.

This is what troubleshooting is all about.

I.e., which records are being missed that should be included or which records are included that should be excluded? Then, after you look at those records, we determine how to include/exclude them....

[jeannier1975]

Because i took the cheese department and calculated the cheese department which is 159/184 and i 86.41 but when i run the queries with all the drpartments i get 95.2

[twinnyfo]

And what are those same values with the new query (not just the percentage)? Which records are missing and why. This is what you need to figure out.

Keep in mind that from this thread and previous threads concerning this group of queries. I am still confused by the ActualLaborHours<>“00:00”. I guess, either it is a work order or it’s not. BUT—I don’t really understand what it represents....

[jeannier1975]

I need to count the work orders for each department that is comp ,fcomp with actual labor hours that the work order was complete not in 00:00 and between the the start date and end date.

[twinnyfo]

However, you’re still not telling us which records are being included or excluded, based upon the query.

When asked a specific question, please provide an answer.

Based upon the queries you provided, it “appears” the the only differences between the queries is the actual start/stop dates and the ActualLaborHours.... It would seem logical that you would want to compare apples to apples and not apples to oranges.