none
Divide two queries in access for percentages RRS feed

  • Question

  •  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
    Thursday, February 14, 2019 6:59 PM

All replies

  • Hi. I could be wrong but I think either way you try to do this will be slow anyway unless you create a temporary table first. So, combining the queries or keeping them separate and using a third query will probably not make a difference in speed. It will certainly not make a difference in file size for having three objects in the database. Just my 2 cents...
    Thursday, February 14, 2019 7:47 PM
  • Firstly, I'd recommend changing the parameters in both queries to refernces to controls in the form.  You should then be able to include the second query as asubquery in the first:

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

    Ken Sheridan, Stafford, England

    Friday, February 15, 2019 12:25 PM
  • Ken How do I  include the second query as asubquery in the first: 

    Im not familiar with subqueries at all

    Friday, February 15, 2019 7:25 PM
  • How do I  include the second query as asubquery in the first


    A I've shown in my earlier post.  The subquery is enclosed in parentheses and the value returned by COUNT(MaximoReport.WorkOrder) is divided by the value returned by the subquery.  The result is multiplied by 100 to show the resulting fractional value as a percentage.  This is returned as a column named PercentagCompleted.

    The SQL statement I posted is largely made up by copying and pasting from your two queries' SQL statements, so you should be able to copy it from my post and paste it as a new query in the query designer in SQL view.  Once you've done this, with the query still open in SQL view, open the ParameterReportF form and enter the start and end dates into its controls.  Be sure that both are updated by pressing the Enter key after entering a date in each.  With the form still open go back to the query and click on Datasheet View from the View icon's drop down menu in the Design ribbon.  All being well the query should open and give you the percentage completed value.

    Obviously I can't test the query, so I can't guarantee 100 per cent that I haven't missed anything.  If the query doesn't do as expected post back here.


    Ken Sheridan, Stafford, England

    Friday, February 15, 2019 9:49 PM
  • how do i break it up by departments ?
    Tuesday, February 19, 2019 9:17 PM
  • how do i break it up by departments ?

    If you want to return multiple rows in the result table, one per department, then you would need to GROUP the outer query BY department, and correlate the subquery with the outer query on department so that, for each row returned by the outer query, the subquery would return the COUNT for the department returned by the outer query's current row.

    Ken Sheridan, Stafford, England

    Tuesday, February 19, 2019 10:23 PM