February 14, 2019 at 8:48 am
sgmunson - Thursday, February 14, 2019 6:39 AMjeannier1975 - Wednesday, February 13, 2019 2:09 PMsgmunson - Wednesday, February 13, 2019 2:05 PMHeading 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?
y
February 14, 2019 at 8:48 am
jeannier1975 - Thursday, February 14, 2019 8:48 AMsgmunson - Thursday, February 14, 2019 6:39 AMjeannier1975 - Wednesday, February 13, 2019 2:09 PMsgmunson - Wednesday, February 13, 2019 2:05 PMHeading 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?
y
yes but I dont know how to do that if i knew it would be done.
February 14, 2019 at 8:51 am
jeannier1975 - Wednesday, February 13, 2019 1:38 PMdrew.allen - Wednesday, February 13, 2019 1:30 PMI 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
I already said that I haven't written Access SQL in many, many years, so I can't provide much more help. Specifically, some languages use CASE to choose between alternatives, and some use IF. I don't even remember which one Access uses. In order to help you further, I would have to do exactly what you should be doing yourself and you have the advantage of having all of the tables and data already set up
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 15, 2019 at 7:34 am
jeannier1975 - Thursday, February 14, 2019 8:48 AMjeannier1975 - Thursday, February 14, 2019 8:48 AMsgmunson - Thursday, February 14, 2019 6:39 AMjeannier1975 - Wednesday, February 13, 2019 2:09 PMsgmunson - Wednesday, February 13, 2019 2:05 PMHeading 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?
y
yes but I dont know how to do that if i knew it would be done.
As Drew initially stated, he doesn't know Access well enough to help out with that. Unfortunately, I don't have much time to analyze the two queries and figure out a way to do it within the query. I'd go ahead and at least get something working using the numerator query and the denominator query, as that I can quickly help with. The important thing is that the result of each query has to be a single column that contains the needed value, be it numerator or denominator. As we can't see your data, we can't figure that part out. You would need to know what values you want for each of those.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 15, 2019 at 11:34 am
sgmunson - Friday, February 15, 2019 7:34 AMjeannier1975 - Thursday, February 14, 2019 8:48 AMjeannier1975 - Thursday, February 14, 2019 8:48 AMsgmunson - Thursday, February 14, 2019 6:39 AMjeannier1975 - Wednesday, February 13, 2019 2:09 PMsgmunson - Wednesday, February 13, 2019 2:05 PMHeading 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?
y
yes but I dont know how to do that if i knew it would be done.
As Drew initially stated, he doesn't know Access well enough to help out with that. Unfortunately, I don't have much time to analyze the two queries and figure out a way to do it within the query. I'd go ahead and at least get something working using the numerator query and the denominator query, as that I can quickly help with. The important thing is that the result of each query has to be a single column that contains the needed value, be it numerator or denominator. As we can't see your data, we can't figure that part out. You would need to know what values you want for each of those.
i know the value i just dont know how to construct the queries to one query to get a percentage
February 15, 2019 at 11:56 am
In the outermost query (the first SELECT), you'd just do something like
SELECT [Aggregate1] / [Aggregate2]
FROM...
As long as the two values are correct in the source query, it will work.
February 15, 2019 at 12:00 pm
pietlinden - Friday, February 15, 2019 11:56 AMIn the outermost query (the first SELECT), you'd just do something like
SELECT [Aggregate1] / [Aggregate2]
FROM...As long as the two values are correct in the source query, it will work.
i know basics i just dont know how to apply it to my queries
February 15, 2019 at 12:51 pm
Not sure if this will help at all, but here goes.
Say you create one query that calculates the Numerator (and you include the grouping column you need, so you get one value per group... Something like
SELECT GroupName, MIN(Value)
FROM MyTable
GROUP BY GroupName
and save that as one query qMin
Then you create another query that does the same thing, but MAX(Value) instead of MIN.
Then you create a third query that joins those two on GroupName and does the MAX/MIN
Yeah, I hate Access too.
February 20, 2019 at 1:56 pm
pietlinden - Friday, February 15, 2019 12:51 PMNot sure if this will help at all, but here goes.
Say you create one query that calculates the Numerator (and you include the grouping column you need, so you get one value per group... Something like
SELECT GroupName, MIN(Value)
FROM MyTable
GROUP BY GroupName
and save that as one query qMinThen I create another query that does the same thing, but MAX(Value) instead of MIN.
Then you create a third query that joins those two on GroupName and does the MAX/MINYeah, I hate Access too.
Often, that's asking for Access to tell you that the query is too complex. Better to just specify each value as a parenthesized query with a / between the two. Just be sure you never get zero in the denominator query.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply