jeannier1975
Registered User.
- Local time
- Today, 14:45
- 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
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