Divide 2 queries

jeannier1975

Registered User.
Local time
Today, 06:31
Joined
May 17, 2018
Messages
48
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

Code:
SELECT
sum(iif(worktype in ("PMINS"...) AND Status Like "*COMP" AND Target_Start>=DateAdd……..,1,0))/sum(iif( WorkType="PMINS"...….,1,0)) AS [percentage Works Orders Completed]
FROM MaximoReport
WHERE..... include any common criteria here (if any)
 
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:

Code:
q1:
SELECT SalesPersonID, Sales FROM tblSales WHERE SalesYear=2018 AND SalesType='Outside'

q2:
SELECT SalesPersonID, Sales FROM tblSales WHERE SalesYear=2019 AND SalesType='Outside'

Combined query:
SELECT SalesPersonID, iif(SalesYear=2018, Sales,0) AS Sales2018, iif(SalesYear=2019, Sales, 0) AS Sales2019
FROM tblSales
WHERE SalesType='Outside'

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.
 
that makes since but since im a novice i dont know how to do that

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])))
 
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.

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?
 

Users who are viewing this thread

Back
Top Bottom