How do I modify this SQL subquery in Access 2007 for "Top 5?"
I have a query:
Accepted as the solution here: https://www.experts-exchange.com/questions/28724619/How-do-I-limit-the-items-per-group-in-this-SQL-query-for-Access-2007.html
And listed below:
SELECT *
FROM TBL_Defects_DCRs INNER JOIN TBL_ISSUES ON TBL_Defects_DCRs.[EDR_#] = TBL_ISSUES.[EDR#]
WHERE TBL_ISSUES.Rank In
(SELECT top 10 I.Rank
FROM TBL_ISSUES As I
WHERE I.Status Not In (11,12,24)
And I.system = [TBL_ISSUES].system
ORDER BY I.Rank DESC
)
ORDER BY TBL_ISSUES.System, TBL_ISSUES.Rank DESC;
I modified it to select all EDR#s that are given a Top5 designation in the checkbox.
SELECT TBL_Defects_DCRs.EDR_Type,
FROM TBL_Defects_DCRs INNER JOIN TBL_ISSUES ON TBL_Defects_DCRs.[EDR_#]=T
WHERE (((TBL_ISSUES.System)<>2) AND ((TBL_ISSUES.Status)<>11 And (TBL_ISSUES.Status)<>12 And (TBL_ISSUES.Status)<>24) AND ((TBL_Defects_DCRs.Top5)=T
OR (((TBL_ISSUES.System)<>2) AND ((TBL_ISSUES.Status)<>11 And (TBL_ISSUES.Status)<>12 And (TBL_ISSUES.Status)<>24) AND ((TBL_Defects_DCRs.Top5)=F
In (SELECT top 5 I.Rank
FROM TBL_ISSUES As I
WHERE I.Status Not In (11,12,24) And I.system = [TBL_ISSUES].system
ORDER BY I.Rank DESC ))
ORDER BY TBL_ISSUES.System DESC , TBL_ISSUES.Rank DESC;
Now, (and the above SQL includes this attempt) I want to select the top 5 in the ranking that are not part of the "Top5" designation. (The Top5 means they are working on it, not that they limited it to 5 per system. I want the next top 5 by rank just to see which ones we should pick from next to work on).
Some of the EDR#s in the Top5 designation are at a lower ranking than ones not being worked on. But, they were next in line at the time, so they are being fixed.
The end report should look something like:
Tripoli 9 defects and DCRs
Rank Top 5 Description
82 Y
79 Y
68 Y
21 Y
87 N
65 N
58 N
53 N
43 N
So, notice, there doesn't have to be 5 in the Top5, it's just however many they're working on.
But, I want the top 5 ranked that are not being worked on listed below it.
There is a sample database and screenshots here: https://www.experts-exchange.com/questions/28724619/How-do-I-limit-the-items-per-group-in-this-SQL-query-for-Access-2007.html
Accepted as the solution here: https://www.experts-exchange.com/questions/28724619/How-do-I-limit-the-items-per-group-in-this-SQL-query-for-Access-2007.html
And listed below:
SELECT *
FROM TBL_Defects_DCRs INNER JOIN TBL_ISSUES ON TBL_Defects_DCRs.[EDR_#] = TBL_ISSUES.[EDR#]
WHERE TBL_ISSUES.Rank In
(SELECT top 10 I.Rank
FROM TBL_ISSUES As I
WHERE I.Status Not In (11,12,24)
And I.system = [TBL_ISSUES].system
ORDER BY I.Rank DESC
)
ORDER BY TBL_ISSUES.System, TBL_ISSUES.Rank DESC;
I modified it to select all EDR#s that are given a Top5 designation in the checkbox.
SELECT TBL_Defects_DCRs.EDR_Type,
FROM TBL_Defects_DCRs INNER JOIN TBL_ISSUES ON TBL_Defects_DCRs.[EDR_#]=T
WHERE (((TBL_ISSUES.System)<>2) AND ((TBL_ISSUES.Status)<>11 And (TBL_ISSUES.Status)<>12 And (TBL_ISSUES.Status)<>24) AND ((TBL_Defects_DCRs.Top5)=T
OR (((TBL_ISSUES.System)<>2) AND ((TBL_ISSUES.Status)<>11 And (TBL_ISSUES.Status)<>12 And (TBL_ISSUES.Status)<>24) AND ((TBL_Defects_DCRs.Top5)=F
In (SELECT top 5 I.Rank
FROM TBL_ISSUES As I
WHERE I.Status Not In (11,12,24) And I.system = [TBL_ISSUES].system
ORDER BY I.Rank DESC ))
ORDER BY TBL_ISSUES.System DESC , TBL_ISSUES.Rank DESC;
Now, (and the above SQL includes this attempt) I want to select the top 5 in the ranking that are not part of the "Top5" designation. (The Top5 means they are working on it, not that they limited it to 5 per system. I want the next top 5 by rank just to see which ones we should pick from next to work on).
Some of the EDR#s in the Top5 designation are at a lower ranking than ones not being worked on. But, they were next in line at the time, so they are being fixed.
The end report should look something like:
Tripoli 9 defects and DCRs
Rank Top 5 Description
82 Y
79 Y
68 Y
21 Y
87 N
65 N
58 N
53 N
43 N
So, notice, there doesn't have to be 5 in the Top5, it's just however many they're working on.
But, I want the top 5 ranked that are not being worked on listed below it.
There is a sample database and screenshots here: https://www.experts-exchange.com/questions/28724619/How-do-I-limit-the-items-per-group-in-this-SQL-query-for-Access-2007.html
Last Comment
ASKER
Whether a task/project is being worked on is determined by whether the Yes or No Top5 checkbox is checked in the Defects and DCRs table: ((TBL_Defects_DCRs.Top5)=T
Step 1
Then INNER JOIN that saved query
SELECT TOP 10
TBL_Defects_DCRs.[EDR_#]
, TBL_ISSUES.Rank
, TBL_Defects_DCRs.Top5
FROM TBL_Defects_DCRs
INNER JOIN TBL_ISSUES ON TBL_Defects_DCRs.[EDR_#] = TBL_ISSUES.[EDR#]
WHERE TBL_Defects_DCRs.Top5 = TRUE
ORDER BY TBL_ISSUES.Rank DESC
UNION ALL SELECT TOP 5
TBL_Defects_DCRs.[EDR_#]
, TBL_ISSUES.Rank
, TBL_Defects_DCRs.Top5
FROM TBL_Defects_DCRs
INNER JOIN TBL_ISSUES ON TBL_Defects_DCRs.[EDR_#] = TBL_ISSUES.[EDR#]
WHERE TBL_Defects_DCRs.Top5 = FALSE
ORDER BY TBL_ISSUES.Rank DESC;
Then INNER JOIN that saved query
SELECT TBL_Defects_DCRs.EDR_Type
, TBL_Defects_DCRs.[EDR_#]
, TBL_Defects_DCRs.DCR_Status
, TBL_Defects_DCRs.DCR_Submit_Date
, TBL_Defects_DCRs.DCR_Headline
, TBL_Defects_DCRs.DCR_Description
, TBL_Defects_DCRs.DCR_ICBS_Impact
, TBL_Defects_DCRs.DCR_Target_Release
, TBL_Defects_DCRs.DCR_Comments
, TBL_ISSUES.[WHITEBOARD#]
, TBL_ISSUES.[TICKET#]
, TBL_ISSUES.[WORKAROUND#]
, TBL_ISSUES.Workload
, TBL_ISSUES.System
, TBL_ISSUES.[DATABASE_ISSUE_#]
, TBL_ISSUES.STATUS
, TBL_ISSUES.Rank
, IsNull([Workload]) AS Expr1
, TBL_ISSUES.Priority
, TBL_ISSUES.[DATABASE_ISSUE_#]
, TBL_Defects_DCRs.Top5
, TBL_Defects_DCRs.DCR_EDR_Priority
, ([TBL_ISSUES].[Workload]) = [Which workload: 2-PIT; 3-BE; 8-PIT and BE; 4-Corr; or blank-All?] OR [Which workload: 2-PIT; 3-BE; 8-PIT and BE; 4-Corr; or blank-All?] IS NULL AS Expr2
FROM (TBL_Defects_DCRs
INNER JOIN TBL_ISSUES ON TBL_Defects_DCRs.[EDR_#] = TBL_ISSUES.[EDR#])
INNER JOIN Qry_ListOf15 ON TBL_Defects_DCRs.[EDR_#] = Qry_ListOf15.[EDR_#];
ASKER
@Paul,
Thank you for posting.
I'm trying to modify what you wrote to the following requirements:
1. Top5 doesn't seem to have a numeric value with the solution providers, it just means they're working on it, so I removed the Top 10 at the beginning of Step 1. (5 was their limit, but they don't stick to it).
2. The Rank descending needs to start over for the items not in the top 5. I've attached two picture examples.
Thank you for posting.
I'm trying to modify what you wrote to the following requirements:
1. Top5 doesn't seem to have a numeric value with the solution providers, it just means they're working on it, so I removed the Top 10 at the beginning of Step 1. (5 was their limit, but they don't stick to it).
2. The Rank descending needs to start over for the items not in the top 5. I've attached two picture examples.
@David
Please answer my question
Please answer my question
ASKER
@aikimark,
aikimark, thank you for checking in.
Whether a task/project is being worked on is determined by whether the Yes or No Top5 checkbox is checked in the Defects and DCRs table: ((TBL_Defects_DCRs.Top5)=T
When the solution teams resolve a defect or DCR, we give them the next highest ranked defect or DCR (issue). Since these are constantly coming in, newer issues might have a higher rank than the ones being worked on in Top5, based on our scoring system that ranks the issues.
aikimark, thank you for checking in.
Whether a task/project is being worked on is determined by whether the Yes or No Top5 checkbox is checked in the Defects and DCRs table: ((TBL_Defects_DCRs.Top5)=T
When the solution teams resolve a defect or DCR, we give them the next highest ranked defect or DCR (issue). Since these are constantly coming in, newer issues might have a higher rank than the ones being worked on in Top5, based on our scoring system that ranks the issues.
Seems I may have misread the question quite badly.... sorry
As I now understand it you are after a way to ORDER your results, not a way to limit the rows.
Don't you just need this?
ORDER BY TBL_Defects_DCRs.Top5 ASC, TBL_ISSUES.Rank DESC
& In case TBL_ISSUES.Rank is NULL this can be used
ORDER BY TBL_Defects_DCRs.Top5 ASC, Nz(TBL_ISSUES.Rank,0) DESC
This will place [Top5] TRUE first, and for those highest [Rank] first to last
then
[Top5] FALSE, and for those highest [Rank] first to last
As I now understand it you are after a way to ORDER your results, not a way to limit the rows.
Don't you just need this?
ORDER BY TBL_Defects_DCRs.Top5 ASC, TBL_ISSUES.Rank DESC
& In case TBL_ISSUES.Rank is NULL this can be used
ORDER BY TBL_Defects_DCRs.Top5 ASC, Nz(TBL_ISSUES.Rank,0) DESC
This will place [Top5] TRUE first, and for those highest [Rank] first to last
then
[Top5] FALSE, and for those highest [Rank] first to last
SELECT
TBL_Defects_DCRs.Top5
, TBL_ISSUES.Rank
, TBL_Defects_DCRs.EDR_Type
, TBL_Defects_DCRs.[EDR_#]
, TBL_Defects_DCRs.DCR_Status
, TBL_Defects_DCRs.DCR_Submit_Date
, TBL_Defects_DCRs.DCR_Headline
, TBL_Defects_DCRs.DCR_Description
, TBL_Defects_DCRs.DCR_ICBS_Impact
, TBL_Defects_DCRs.DCR_Target_Release
, TBL_Defects_DCRs.DCR_Comments
, TBL_ISSUES.[WHITEBOARD#]
, TBL_ISSUES.[TICKET#]
, TBL_ISSUES.[WORKAROUND#]
, TBL_ISSUES.Workload
, TBL_ISSUES.System
, TBL_ISSUES.[DATABASE_ISSUE_#]
, TBL_ISSUES.STATUS
, IsNull([Workload]) AS Expr1
, TBL_ISSUES.Priority
, TBL_ISSUES.[DATABASE_ISSUE_#]
, TBL_Defects_DCRs.DCR_EDR_Priority
, ([TBL_ISSUES].[Workload]) = [Which workload: 2-PIT; 3-BE; 8-PIT and BE; 4-Corr; or blank-All?] OR [Which workload: 2-PIT; 3-BE; 8-PIT and BE; 4-Corr; or blank-All?] IS NULL AS Expr2
FROM TBL_Defects_DCRs
INNER JOIN TBL_ISSUES ON TBL_Defects_DCRs.[EDR_#] = TBL_ISSUES.[EDR#]
WHERE (
((TBL_ISSUES.Workload) IS NOT NULL) AND ((TBL_ISSUES.STATUS) <> 11 AND (TBL_ISSUES.STATUS) <> 12 AND (TBL_ISSUES.STATUS) <> 24) AND (
(TBL_ISSUES.Rank) IN (
SELECT TOP 10 Rank
FROM TBL_ISSUES AS R
WHERE R.System = TBL_ISSUES.System
ORDER BY TBL_ISSUES.Rank ASC
)
) AND ((IsNull([Workload])) = False)
) OR (
((TBL_ISSUES.Workload) IS NULL) AND ((TBL_ISSUES.STATUS) <> 11 AND (TBL_ISSUES.STATUS) <> 12 AND (TBL_ISSUES.STATUS) <> 24) AND (
(TBL_ISSUES.Rank) IN (
SELECT TOP 10 Rank
FROM TBL_ISSUES AS R
WHERE R.System = TBL_ISSUES.System
ORDER BY TBL_ISSUES.Rank ASC
)
)
)
ORDER BY
TBL_Defects_DCRs.Top5 ASC
, Nz(TBL_ISSUES.Rank, 0) DESC
;
Please try this
SELECT TBL_Defects_DCRs.EDR_Type, TBL_Defects_DCRs.[EDR_#], TBL_Defects_DCRs.DCR_Status, TBL_Defects_DCRs.DCR_Submit_Date, TBL_Defects_DCRs.DCR_Headline, TBL_Defects_DCRs.DCR_Description, TBL_Defects_DCRs.DCR_ICBS_Impact, TBL_Defects_DCRs.DCR_Target_Release, TBL_Defects_DCRs.DCR_Comments, TBL_ISSUES.[WHITEBOARD#], TBL_ISSUES.[TICKET#], TBL_ISSUES.[WORKAROUND#], TBL_ISSUES.Workload, TBL_ISSUES.System, TBL_ISSUES.[DATABASE_ISSUE_#], TBL_ISSUES.Status, TBL_ISSUES.Rank, IsNull([Workload]) AS Expr1, TBL_ISSUES.Priority, TBL_ISSUES.[DATABASE_ISSUE_#], TBL_Defects_DCRs.Top5, TBL_Defects_DCRs.DCR_EDR_Priority, ([TBL_ISSUES].[Workload])=[Which workload: 2-PIT; 3-BE; 8-PIT and BE; 4-Corr; or blank-All?] Or [Which workload: 2-PIT; 3-BE; 8-PIT and BE; 4-Corr; or blank-All?] Is Null AS Expr2
FROM TBL_Defects_DCRs INNER JOIN TBL_ISSUES ON TBL_Defects_DCRs.[EDR_#]=TBL_ISSUES.[EDR#]
WHERE TBL_ISSUES.Rank In (SELECT top 5 T5.Rank FROM TBL_ISSUES As T5 WHERE T5.Status Not In (11,12,24)
And T5.system = [TBL_ISSUES].system
ORDER BY T5.Rank DESC )
UNION ALL
SELECT TBL_Defects_DCRs.EDR_Type, TBL_Defects_DCRs.[EDR_#], TBL_Defects_DCRs.DCR_Status, TBL_Defects_DCRs.DCR_Submit_Date, TBL_Defects_DCRs.DCR_Headline, TBL_Defects_DCRs.DCR_Description, TBL_Defects_DCRs.DCR_ICBS_Impact, TBL_Defects_DCRs.DCR_Target_Release, TBL_Defects_DCRs.DCR_Comments, TBL_ISSUES.[WHITEBOARD#], TBL_ISSUES.[TICKET#], TBL_ISSUES.[WORKAROUND#], TBL_ISSUES.Workload, TBL_ISSUES.System, TBL_ISSUES.[DATABASE_ISSUE_#], TBL_ISSUES.Status, TBL_ISSUES.Rank, IsNull([Workload]) AS Expr1, TBL_ISSUES.Priority, TBL_ISSUES.[DATABASE_ISSUE_#], TBL_Defects_DCRs.Top5, TBL_Defects_DCRs.DCR_EDR_Priority, ([TBL_ISSUES].[Workload])=[Which workload: 2-PIT; 3-BE; 8-PIT and BE; 4-Corr; or blank-All?] Or [Which workload: 2-PIT; 3-BE; 8-PIT and BE; 4-Corr; or blank-All?] Is Null AS Expr2
FROM TBL_Defects_DCRs INNER JOIN TBL_ISSUES ON TBL_Defects_DCRs.[EDR_#]=TBL_ISSUES.[EDR#]
WHERE TBL_ISSUES.Rank In (SELECT top 5 T5.Rank FROM TBL_ISSUES As T5 WHERE T5.Status Not In (11,12,24)
And T5.system = [TBL_ISSUES].system
And TBL_Defects_DCRs.Top5=False
ORDER BY T5.Rank DESC )
ORDER BY TBL_ISSUES.System, TBL_Defects_DCRs.Top5, TBL_ISSUES.Rank DESC
ASKER
@aikimark,
Thank you.
When I try the query, it takes only the Top 5 "Top5" from the whole universe of defects and DCRs and then groups them by city(system) and orders them by rank. It then, still within each group, takes all items not in the "Top5" and orders them by rank.
I need all items that are check marked as "Top5" to be grouped by city (system) and sorted by rank. Then the Top 10 of the items that are not check marked as "Top5" sorted by rank below them in each group. This is illustrated in the picture right above where you last posted.
Thank you.
When I try the query, it takes only the Top 5 "Top5" from the whole universe of defects and DCRs and then groups them by city(system) and orders them by rank. It then, still within each group, takes all items not in the "Top5" and orders them by rank.
I need all items that are check marked as "Top5" to be grouped by city (system) and sorted by rank. Then the Top 10 of the items that are not check marked as "Top5" sorted by rank below them in each group. This is illustrated in the picture right above where you last posted.
ASKER
@Paul,
Thanks for double checking. I forgive you. Such are the shortfalls of forums. But overall, it sure is nice to be able to post and answer on our own time!
Your query worked to list all the "Top5" defects and DCRs by System with the rank ordered Descending. And below them, the items not in the "Top5", ordered by rank descending.
But, it's not limiting the false "Top5" to 10. Second, it is excluding those items that are not ranked, I think because of the Nz. I would rather allow the unranked because sometimes there are less than 10 overall, in a given group, that are not in the "Top5" category.
Thanks for double checking. I forgive you. Such are the shortfalls of forums. But overall, it sure is nice to be able to post and answer on our own time!
Your query worked to list all the "Top5" defects and DCRs by System with the rank ordered Descending. And below them, the items not in the "Top5", ordered by rank descending.
But, it's not limiting the false "Top5" to 10. Second, it is excluding those items that are not ranked, I think because of the Nz. I would rather allow the unranked because sometimes there are less than 10 overall, in a given group, that are not in the "Top5" category.
Ok. Then try this. Top 5 for the checked followed by top 10 for the unchecked
SELECT TBL_Defects_DCRs.EDR_Type, TBL_Defects_DCRs.[EDR_#], TBL_Defects_DCRs.DCR_Status, TBL_Defects_DCRs.DCR_Submit_Date, TBL_Defects_DCRs.DCR_Headline, TBL_Defects_DCRs.DCR_Description, TBL_Defects_DCRs.DCR_ICBS_Impact, TBL_Defects_DCRs.DCR_Target_Release, TBL_Defects_DCRs.DCR_Comments, TBL_ISSUES.[WHITEBOARD#], TBL_ISSUES.[TICKET#], TBL_ISSUES.[WORKAROUND#], TBL_ISSUES.Workload, TBL_ISSUES.System, TBL_ISSUES.[DATABASE_ISSUE_#], TBL_ISSUES.Status, TBL_ISSUES.Rank, IsNull([Workload]) AS Expr1, TBL_ISSUES.Priority, TBL_ISSUES.[DATABASE_ISSUE_#], TBL_Defects_DCRs.Top5, TBL_Defects_DCRs.DCR_EDR_Priority, ([TBL_ISSUES].[Workload])=[Which workload: 2-PIT; 3-BE; 8-PIT and BE; 4-Corr; or blank-All?] Or [Which workload: 2-PIT; 3-BE; 8-PIT and BE; 4-Corr; or blank-All?] Is Null AS Expr2
FROM TBL_Defects_DCRs INNER JOIN TBL_ISSUES ON TBL_Defects_DCRs.[EDR_#]=TBL_ISSUES.[EDR#]
WHERE TBL_ISSUES.Rank In (SELECT top 5 T5.Rank FROM TBL_ISSUES As T5 WHERE T5.Status Not In (11,12,24)
And T5.system = [TBL_ISSUES].system
ORDER BY T5.Rank DESC )
UNION ALL SELECT TBL_Defects_DCRs.EDR_Type, TBL_Defects_DCRs.[EDR_#], TBL_Defects_DCRs.DCR_Status, TBL_Defects_DCRs.DCR_Submit_Date, TBL_Defects_DCRs.DCR_Headline, TBL_Defects_DCRs.DCR_Description, TBL_Defects_DCRs.DCR_ICBS_Impact, TBL_Defects_DCRs.DCR_Target_Release, TBL_Defects_DCRs.DCR_Comments, TBL_ISSUES.[WHITEBOARD#], TBL_ISSUES.[TICKET#], TBL_ISSUES.[WORKAROUND#], TBL_ISSUES.Workload, TBL_ISSUES.System, TBL_ISSUES.[DATABASE_ISSUE_#], TBL_ISSUES.Status, TBL_ISSUES.Rank, IsNull([Workload]) AS Expr1, TBL_ISSUES.Priority, TBL_ISSUES.[DATABASE_ISSUE_#], TBL_Defects_DCRs.Top5, TBL_Defects_DCRs.DCR_EDR_Priority, ([TBL_ISSUES].[Workload])=[Which workload: 2-PIT; 3-BE; 8-PIT and BE; 4-Corr; or blank-All?] Or [Which workload: 2-PIT; 3-BE; 8-PIT and BE; 4-Corr; or blank-All?] Is Null AS Expr2
FROM TBL_Defects_DCRs INNER JOIN TBL_ISSUES ON TBL_Defects_DCRs.[EDR_#]=TBL_ISSUES.[EDR#]
WHERE TBL_ISSUES.Rank In (SELECT top 10 T10.Rank FROM TBL_ISSUES As T10 WHERE T10.Status Not In (11,12,24)
And T10.system = [TBL_ISSUES].system
And TBL_Defects_DCRs.Top5=False
ORDER BY T10.Rank DESC )
ORDER BY TBL_ISSUES.System, TBL_Defects_DCRs.Top5, TBL_ISSUES.Rank DESC;
ASKER
Maybe a bullet list of requirements is a better way to describe this.
All defects and DCRs.
These are the EDR# and EDR_# fields in FROM TBL_Defects_DCRs INNER JOIN TBL_ISSUES ON TBL_Defects_DCRs.[EDR_#]=T
These are the EDR# and EDR_# fields in FROM TBL_Defects_DCRs INNER JOIN TBL_ISSUES ON TBL_Defects_DCRs.[EDR_#]=T
Grouped by city(system)
Under each system all the "Top5" defects and DCRs for that system ordered by rank descending. It is not limited to the coding Top 5; it is all items that are check marked "Top5".
Followed by the Top 10 defects and DCRs that are not check marked "Top5", ordered by rank descending.
SELECT TBL_Defects_DCRs.EDR_Type, TBL_Defects_DCRs.[EDR_#], TBL_Defects_DCRs.DCR_Status, TBL_Defects_DCRs.DCR_Submit_Date, TBL_Defects_DCRs.DCR_Headline, TBL_Defects_DCRs.DCR_Description, TBL_Defects_DCRs.DCR_ICBS_Impact, TBL_Defects_DCRs.DCR_Target_Release, TBL_Defects_DCRs.DCR_Comments, TBL_ISSUES.[WHITEBOARD#], TBL_ISSUES.[TICKET#], TBL_ISSUES.[WORKAROUND#], TBL_ISSUES.Workload, TBL_ISSUES.System, TBL_ISSUES.[DATABASE_ISSUE_#], TBL_ISSUES.Status, TBL_ISSUES.Rank, IsNull([Workload]) AS Expr1, TBL_ISSUES.Priority, TBL_ISSUES.[DATABASE_ISSUE_#], TBL_Defects_DCRs.Top5, TBL_Defects_DCRs.DCR_EDR_Priority, ([TBL_ISSUES].[Workload])=[Which workload: 2-PIT; 3-BE; 8-PIT and BE; 4-Corr; or blank-All?] Or [Which workload: 2-PIT; 3-BE; 8-PIT and BE; 4-Corr; or blank-All?] Is Null AS Expr2
FROM TBL_Defects_DCRs INNER JOIN TBL_ISSUES ON TBL_Defects_DCRs.[EDR_#]=TBL_ISSUES.[EDR#]
WHERE
Status Not In (11,12,24)
And Top5 = True
UNION ALL
SELECT TBL_Defects_DCRs.EDR_Type, TBL_Defects_DCRs.[EDR_#], TBL_Defects_DCRs.DCR_Status, TBL_Defects_DCRs.DCR_Submit_Date, TBL_Defects_DCRs.DCR_Headline, TBL_Defects_DCRs.DCR_Description, TBL_Defects_DCRs.DCR_ICBS_Impact, TBL_Defects_DCRs.DCR_Target_Release, TBL_Defects_DCRs.DCR_Comments, TBL_ISSUES.[WHITEBOARD#], TBL_ISSUES.[TICKET#], TBL_ISSUES.[WORKAROUND#], TBL_ISSUES.Workload, TBL_ISSUES.System, TBL_ISSUES.[DATABASE_ISSUE_#], TBL_ISSUES.Status, TBL_ISSUES.Rank, IsNull([Workload]) AS Expr1, TBL_ISSUES.Priority, TBL_ISSUES.[DATABASE_ISSUE_#], TBL_Defects_DCRs.Top5, TBL_Defects_DCRs.DCR_EDR_Priority, ([TBL_ISSUES].[Workload])=[Which workload: 2-PIT; 3-BE; 8-PIT and BE; 4-Corr; or blank-All?] Or [Which workload: 2-PIT; 3-BE; 8-PIT and BE; 4-Corr; or blank-All?] Is Null AS Expr2
FROM TBL_Defects_DCRs INNER JOIN TBL_ISSUES ON TBL_Defects_DCRs.[EDR_#]=TBL_ISSUES.[EDR#]
WHERE TBL_ISSUES.Rank In (SELECT top 10 T10.Rank FROM TBL_ISSUES As T10 WHERE T10.Status Not In (11,12,24)
And T10.system = [TBL_ISSUES].system
And TBL_Defects_DCRs.Top5=False
ORDER BY T10.Rank DESC )
ORDER BY TBL_ISSUES.System, TBL_Defects_DCRs.Top5, TBL_ISSUES.Rank DESC;
ASKER
@aikimark,
This looks like it should work and it is almost there.
It is listing all the items in the "Top5" category.
It is listing some items that are not in the "Top5" category, but not all the top 10.
Here's what it's doing.
Given a list of the top ranked items, it's selecting the ones that are in the Top 10 and then from that, taking the ones that are not "Top5". For example,
No. Rank Top5
1. 87 Y
2. 75 N
3. 63 Y
4. 62 Y
5. 62 N
6. 58 Y
7. 55 Y
8. 48 Y
9. 43 N
10. 39 Y
11. 35 Y
12. 28 N
It's going into the Top 10 overall and selecting numbers 2, 5, and 9 as part of the Top5=false.
I need it to continue selecting all Top5=True.
And listed next, I need the Top5=false selected with the Top 10 in rank from that subset. Here, that would be numbers 2, 5, 9, and 12 and any other top 10 ranked issues whose Top5=false.
I've been trying to change up the SQL to get that since you and Paul last posted, but haven't been able to.
This looks like it should work and it is almost there.
It is listing all the items in the "Top5" category.
It is listing some items that are not in the "Top5" category, but not all the top 10.
Here's what it's doing.
Given a list of the top ranked items, it's selecting the ones that are in the Top 10 and then from that, taking the ones that are not "Top5". For example,
No. Rank Top5
1. 87 Y
2. 75 N
3. 63 Y
4. 62 Y
5. 62 N
6. 58 Y
7. 55 Y
8. 48 Y
9. 43 N
10. 39 Y
11. 35 Y
12. 28 N
It's going into the Top 10 overall and selecting numbers 2, 5, and 9 as part of the Top5=false.
I need it to continue selecting all Top5=True.
And listed next, I need the Top5=false selected with the Top 10 in rank from that subset. Here, that would be numbers 2, 5, 9, and 12 and any other top 10 ranked issues whose Top5=false.
I've been trying to change up the SQL to get that since you and Paul last posted, but haven't been able to.
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
@aikimark,
I took out the Top 10 limit in the portion:
UNION ALL
SELECT Top 10 TBL_Defects_DCRs.EDR_Type,
and it worked like a charm. That limit was restricting the primary set to the Top 10, so the subquery only had the Top 10 to look through for the Top5=false. Wow, working through all that really is a discipline in subsets. Thank you for taking the time to understand what I wanted and giving it several go-rounds.
I took out the Top 10 limit in the portion:
UNION ALL
SELECT Top 10 TBL_Defects_DCRs.EDR_Type,
and it worked like a charm. That limit was restricting the primary set to the Top 10, so the subquery only had the Top 10 to look through for the Top5=false. Wow, working through all that really is a discipline in subsets. Thank you for taking the time to understand what I wanted and giving it several go-rounds.
ASKER
@Paul,
I appreciate you staying with me through this in looking for a solution!
I appreciate you staying with me through this in looking for a solution!
Microsoft Access
Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.
226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
What determines whether a task/project is being worked on?