Sub queries In access

Question

Sub queries In access

jeannier1975

SSC Veteran

Points: 238
More actions
February 8, 2019 at 9:43 am

#409498

i 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])));
the denominator query
SELECT MaximoReport.WorkOrder, MaximoReport.WorkType, MaximoReport.Status, MaximoReport.[Target Start]
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])));
What i want is combine two queries so i can have one query do it all instead of three.

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply

sgmunson SSC Guru Points: 110639 More actions · Answer 1

You could write a SELECT that puts each query in a set of parentheses, and then has the divide symbol (/) between them and an AS RESULT afterwards. Potential problem is divide by 0 error.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

jeannier1975 SSC Veteran Points: 238 More actions · Answer 2

sgmunson - Wednesday, February 13, 2019 12:05 PM
You could write a SELECT that puts each query in a set of parentheses, and then has the divide symbol (/) between them and an AS RESULT afterwards. Potential problem is divide by 0 error.

i dont know sub queries at all can you assist

sgmunson SSC Guru Points: 110639 More actions · Answer 3

jeannier1975 - Wednesday, February 13, 2019 12:13 PM
sgmunson - Wednesday, February 13, 2019 12:05 PM
You could write a SELECT that puts each query in a set of parentheses, and then has the divide symbol (/) between them and an AS RESULT afterwards. Potential problem is divide by 0 error.
i dont know sub queries at all can you assist

It''s merely a matter of placing each of those two queries in an outer set of parentheses, similar to this:

SELECT (query for numerator) / (query for denominator) AS RESULT

EDIT: Just be sure not to use an AS to alias the item in the SELECT list for each query.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

jeannier1975 SSC Veteran Points: 238 More actions · Answer 4

sgmunson - Wednesday, February 13, 2019 12:15 PM
jeannier1975 - Wednesday, February 13, 2019 12:13 PM
sgmunson - Wednesday, February 13, 2019 12:05 PM
You could write a SELECT that puts each query in a set of parentheses, and then has the divide symbol (/) between them and an AS RESULT afterwards. Potential problem is divide by 0 error.
i dont know sub queries at all can you assist
It''s merely a matter of placing each of those two queries in an outer set of parentheses, similar to this:
SELECT (query for numerator) / (query for denominator) AS RESULT
EDIT: Just be sure not to use an AS to alias the item in the SELECT list for each query.

jeannier1975 SSC Veteran Points: 238 More actions · Answer 5

jeannier1975 - Wednesday, February 13, 2019 12:23 PM
sgmunson - Wednesday, February 13, 2019 12:15 PM
jeannier1975 - Wednesday, February 13, 2019 12:13 PM
sgmunson - Wednesday, February 13, 2019 12:05 PM
You could write a SELECT that puts each query in a set of parentheses, and then has the divide symbol (/) between them and an AS RESULT afterwards. Potential problem is divide by 0 error.
i dont know sub queries at all can you assist
It''s merely a matter of placing each of those two queries in an outer set of parentheses, similar to this:
SELECT (query for numerator) / (query for denominator) AS RESULT
EDIT: Just be sure not to use an AS to alias the item in the SELECT list for each query.

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

/
(SELECT MaximoReport.WorkOrder, MaximoReport.WorkType, MaximoReport.Status, MaximoReport.[Target Start]
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])));)

As RESULTS

and I get an error that i ahve written a subquery that can return more one field without usint EXITS reserved word in the main query FRom Caluse

sgmunson SSC Guru Points: 110639 More actions · Answer 6

jeannier1975 - Wednesday, February 13, 2019 12:25 PM
jeannier1975 - Wednesday, February 13, 2019 12:23 PM
sgmunson - Wednesday, February 13, 2019 12:15 PM
jeannier1975 - Wednesday, February 13, 2019 12:13 PM
sgmunson - Wednesday, February 13, 2019 12:05 PM
You could write a SELECT that puts each query in a set of parentheses, and then has the divide symbol (/) between them and an AS RESULT afterwards. Potential problem is divide by 0 error.
i dont know sub queries at all can you assist
It''s merely a matter of placing each of those two queries in an outer set of parentheses, similar to this:
SELECT (query for numerator) / (query for denominator) AS RESULT
EDIT: Just be sure not to use an AS to alias the item in the SELECT list for each query.
SELECT (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])));)
/
(SELECT MaximoReport.WorkOrder, MaximoReport.WorkType, MaximoReport.Status, MaximoReport.[Target Start]
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])));)
As RESULTS
and I get an error that i ahve written a subquery that can return more one field without usint EXITS reserved word in the main query FRom Caluse

SELECT (SELECT Count(MaximoReport.WorkOrder) --AS CountOfWorkOrder -- TAKE this alias out altogether 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])));)

/ (SELECT MaximoReport.WorkOrder, MaximoReport.WorkType, MaximoReport.Status, MaximoReport.[Target Start] 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])));)

As RESULTS

Take out the alias in the numerator query, and only select a single number representing your denominator in the denominator query. I can't know which column that is, or whether it's a COUNT() of something. You'll have to figure that part out.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

jeannier1975 SSC Veteran Points: 238 More actions · Answer 7

sgmunson - Wednesday, February 13, 2019 12:30 PM
jeannier1975 - Wednesday, February 13, 2019 12:25 PM
jeannier1975 - Wednesday, February 13, 2019 12:23 PM
sgmunson - Wednesday, February 13, 2019 12:15 PM
jeannier1975 - Wednesday, February 13, 2019 12:13 PM
sgmunson - Wednesday, February 13, 2019 12:30 PM
jeannier1975 - Wednesday, February 13, 2019 12:25 PM
jeannier1975 - Wednesday, February 13, 2019 12:23 PM
sgmunson - Wednesday, February 13, 2019 12:15 PM
jeannier1975 - Wednesday, February 13, 2019 12:13 PM
sgmunson - Wednesday, February 13, 2019 12:05 PM
You could write a SELECT that puts each query in a set of parentheses, and then has the divide symbol (/) between them and an AS RESULT afterwards. Potential problem is divide by 0 error.
i dont know sub queries at all can you assist
It''s merely a matter of placing each of those two queries in an outer set of parentheses, similar to this:
SELECT (query for numerator) / (query for denominator) AS RESULT
EDIT: Just be sure not to use an AS to alias the item in the SELECT list for each query.
SELECT (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])));)
/
(SELECT MaximoReport.WorkOrder, MaximoReport.WorkType, MaximoReport.Status, MaximoReport.[Target Start]
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])));)
As RESULTS
and I get an error that i ahve written a subquery that can return more one field without usint EXITS reserved word in the main query FRom Caluse
SELECT (SELECT Count(MaximoReport.WorkOrder) --AS CountOfWorkOrder -- TAKE this alias out altogether 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])));)
/ (SELECT MaximoReport.WorkOrder, MaximoReport.WorkType, MaximoReport.Status, MaximoReport.[Target Start] 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])));)
As RESULTS
Take out the alias in the numerator query, and only select a single number representing your denominator in the denominator query. I can't know which column that is, or whether it's a COUNT() of something. You'll have to figure that part out.
i dont know sub queries at all can you assist
It''s merely a matter of placing each of those two queries in an outer set of parentheses, similar to this:
SELECT (query for numerator) / (query for denominator) AS RESULT
EDIT: Just be sure not to use an AS to alias the item in the SELECT list for each query.
SELECT (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])));)
/
(SELECT MaximoReport.WorkOrder, MaximoReport.WorkType, MaximoReport.Status, MaximoReport.[Target Start]
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])));)
As RESULTS
and I get an error that i ahve written a subquery that can return more one field without usint EXITS reserved word in the main query FRom Caluse
SELECT (SELECT Count(MaximoReport.WorkOrder) --AS CountOfWorkOrder -- TAKE this alias out altogether 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])));)
/ (SELECT MaximoReport.WorkOrder, MaximoReport.WorkType, MaximoReport.Status, MaximoReport.[Target Start] 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])));)
As RESULTS
Take out the alias in the numerator query, and only select a single number representing your denominator in the denominator query. I can't know which column that is, or whether it's a COUNT() of something. You'll have to figure that part out.

that did not work says im missing ( or ]

sgmunson SSC Guru Points: 110639 More actions · Answer 8

What does your query look like now?

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

drew.allen SSC Guru Points: 76948 More actions · Answer 9

I would take a different approach. Your two queries are very similar. Rewrite your query to pull any record that would meet either criteria and then use two CASE/IF expressions: one to determine whether the record should be included in the numerator and the other for the denominator. I haven't written Access SQL in many, many years, so I can't provide much more help.

Drew

PS ((Access) ((You) (((Can) (Never) (Have)) (((Too) (Many)) (Parens)))))

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

jeannier1975 SSC Veteran Points: 238 More actions · Answer 10

drew.allen - Wednesday, February 13, 2019 1:30 PM
I would take a different approach. Your two queries are very similar. Rewrite your query to pull any record that would meet either criteria and then use two CASE/IF expressions: one to determine whether the record should be included in the numerator and the other for the denominator. I haven't written Access SQL in many, many years, so I can't provide much more help.
Drew
PS ((Access) ((You) (((Can) (Never) (Have)) (((Too) (Many)) (Parens)))))

Drew can you help please show me how I can do that Im not familiar with CASE IF
Im using MS ACCESS 2016

sgmunson SSC Guru Points: 110639 More actions · Answer 11

jeannier1975 - Wednesday, February 13, 2019 1:38 PM
drew.allen - Wednesday, February 13, 2019 1:30 PM
I would take a different approach. Your two queries are very similar. Rewrite your query to pull any record that would meet either criteria and then use two CASE/IF expressions: one to determine whether the record should be included in the numerator and the other for the denominator. I haven't written Access SQL in many, many years, so I can't provide much more help.
Drew
PS ((Access) ((You) (((Can) (Never) (Have)) (((Too) (Many)) (Parens)))))
Drew can you help please show me how I can do that Im not familiar with CASE IF
Im using MS ACCESS 2016

You still haven't provided the query as you have it at the moment. Pretty good bet a typo took out a need parenthesis somewhere. Paste in the query as it was when you got the error and I can fix it fairly quickly.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

sgmunson SSC Guru Points: 110639 More actions · Answer 12

Heading out for the day. Will have to get to this tomorrow if the query finally does get posted.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

jeannier1975 SSC Veteran Points: 238 More actions · Answer 13

sgmunson - Wednesday, February 13, 2019 2:05 PM
Heading out for the day. Will have to get to this tomorrow if the query finally does get posted.

SELECT MaximoReport.WorkOrder, Count(MaximoReport.WorkOrder) AS NoOfWorkOrders, MaximoReport.WorkType, MaximoReport.STATUS, MIN(Nz(MaximoReport.[Target Start],date())) AS StartOfStartTarget
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 WorkOrder, WorkType, STATUS;

sgmunson SSC Guru Points: 110639 More actions · Answer 14

jeannier1975 - Wednesday, February 13, 2019 2:09 PM
sgmunson - Wednesday, February 13, 2019 2:05 PM
Heading out for the day. Will have to get to this tomorrow if the query finally does get posted.
SELECT MaximoReport.WorkOrder, Count(MaximoReport.WorkOrder) AS NoOfWorkOrders, MaximoReport.WorkType, MaximoReport.STATUS, MIN(Nz(MaximoReport.[Target Start],date())) AS StartOfStartTarget
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 WorkOrder, WorkType, STATUS;

Okay, so what happened to the numerator and divisor queries? I see a COUNT here, but wouldn't' you need yet another COUNT of some kind to get the other number, and then add a column that divides one by the other?

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)