Divide 2 queries (1 Viewer)

want to join two queries so i can take the count of one query and divided by another query to get the percentage of work orders that were completed.

the numerator query:

SELECT Count(MaximoReport.WorkOrder) AS CountOfWorkOrder
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])));[/code]


the denominator query

[SELECT Count(MaximoReport.WorkOrder) AS [Total LEWPM Den]
FROM MaximoReport
WHERE (((MaximoReport.WorkType)="PMINS" Or (MaximoReport.WorkType)="PMOR" Or (MaximoReport.WorkType)="PMPDM" Or (MaximoReport.WorkType)="PMREG" Or (MaximoReport.WorkType)="PMRT") AND ((MaximoReport.Status)<>"CAN") AND ((IIf(Len([Target Start])>10,[Target Start],[Target Start]+[TargetStartHour]))>=DateAdd("h",-11.8,[Forms]![ParameterReportF]![DateFrom]) And (IIf(Len([Target Start])>10,[Target Start],[Target Start]+[TargetStartHour]))<DateAdd("h",23,[Forms]![ParameterReportF]![DateTo])));


What i want is combine two queries so i can have one query do it all instead of three

since they are counting on different things you would have to use a sum and a massive if statement for each count

not going to type the whole thing out, but your first one would start with

That's too much code to parse through, so let me give you general advice: When you want to combine queries based on the same source but with different WHERE clauses, you move the WHERE clause to a calculated field.

This is an oversimplified example to demonstrate:


In the combined query I moved the different WHERE criteria to the SELECT. The shared criterion I left in the WHERE because it needs to be applied to all cases. That's how you shoudl accomplish what you want.

that makes since but since im a novice i dont know how to do that.

As advised in your other post https://stackoverflow.com/questions/54696181/dividing-two-queries-to-get-percentage

Often easier to do percentage calcs in a report.

Queries can be nested to make one long SQL statement and saved as one query object.

that makes since but since im a novice i dont know how to do that

Click to expand...

Both CJ and I explained the process. Give it a shot and then post back here when you get stuck. Be sure to post your SQL and the issue (error message, unexpected results, etc)

I get an missing opperator error





SELECT

Sum(IIf(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],1,0))/ Sum(IIf(Worktype IN (PMINS,PMOR,PMPDM,PMREG,PMRT,1,0)) From MaximoReports
Where((MaximoReport.Status)<>"CAN") AND ((IIf(Len([Target Start])>10,[Target Start],[Target Start]+[TargetStartHour]))>=DateAdd("h",-11.8,[Forms]![ParameterReportF]![DateFrom]) And (IIf(Len([Target Start])>10,[Target Start],[Target Start]+[TargetStartHour]))<DateAdd("h",23,[Forms]![ParameterReportF]![DateTo])));

i also did the following query and it works but the numbers dont match what i need it to do.

SELECT Count(MaximoReport.WorkOrder)/
(SELECT Count(MaximoReport.WorkOrder) AS [Total LEWPM Den]
FROM MaximoReport
WHERE (((MaximoReport.WorkType)="PMINS"
Or (MaximoReport.WorkType)="PMOR"
Or (MaximoReport.WorkType)="PMPDM"
Or (MaximoReport.WorkType)="PMREG" Or (MaximoReport.WorkType)="PMRT")
AND ((MaximoReport.Status)<>"CAN")
AND ((IIf(Len([Target Start])>10,[Target Start],[Target Start]+[TargetStartHour]))
>=DateAdd("h",-11.8,[Forms]![ParameterReportF]![DateFrom])
And (IIf(Len([Target Start])>10,[Target Start],[Target Start]+[TargetStartHour]))
<DateAdd("h",23,[Forms]![ParameterReportF]![DateTo]))))*100
AS PerecentageCompleted
FROM MaximoReport
WHERE (((MaximoReport.WorkType) In ("PMINS","PMOR","PMPDM","PMREG","PMRT"))
AND ((MaximoReport.Status) Like "*COMP")
AND ((MaximoReport.[Target Start])>=DateAdd("h",-1,[Forms]![ParameterReportF]![DateFrom])
AND (MaximoReport.[Target Start])<DateAdd("h",23,[Forms]![ParameterReportF]![DateTo]))
AND ((MaximoReport.ActualLaborHours)<>"00:00")
AND ((MaximoReport.ActualStartDate)>=DateAdd("h",-11.8,[Forms]![ParameterReportF]![DateFrom])
AND (MaximoReport.ActualStartDate)<DateAdd("h",23,[Forms]![ParameterReportF]![DateTo])))

jeannier1975 said:

want to join two queries so i can take the count of one query and divided by another query to get the percentage of work orders that were completed.

Click to expand...

Are you showing the returned values for both queries? If so, you can use a third control to do the actual calculation.

no they dont want to see the count they are only interested in the percentages. in another form i will show the list of the records. Im just about ready to pay someone for help

Please note, even if you don't make it visible, if you can put the returned value on the screen you can work with it there. Have had to do this in the past. Is this going to be on a report or on a form?