How do I modify this SQL subquery in Access 2007 for "Top 5?"

I have a query:
Accepted as the solution here: http://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, TBL_Defects_DCRs.[EDR_#], 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

FROM TBL_Defects_DCRs INNER JOIN TBL_ISSUES ON TBL_Defects_DCRs.[EDR_#]=TBL_ISSUES.[EDR#]
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)=True))
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)=False))  AND ((TBL_ISSUES.Rank)
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: http://www.experts-exchange.com/questions/28724619/How-do-I-limit-the-items-per-group-in-this-SQL-query-for-Access-2007.html
LVL 1
David BigelowStaff Operations SpecialistAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

aikimarkCommented:
@David

What determines whether a task/project is being worked on?
0
David BigelowStaff Operations SpecialistAuthor Commented:
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)=True)) .
0
PortletPaulfreelancerCommented:
Step 1
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;

Open in new window

I saved this as Qry_ListOf15

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_#];

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

David BigelowStaff Operations SpecialistAuthor Commented:
@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.
Rank starts at top again, after the Top5 items are listed, for items not marked Top5.Rank continues in descent, after the Top5 items are listed, for items not marked top 5
0
aikimarkCommented:
@David

Please answer my question
0
David BigelowStaff Operations SpecialistAuthor Commented:
@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)=True)) .

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.
0
PortletPaulfreelancerCommented:
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
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
;

Open in new window

ordering by "top 5" and rank
0
aikimarkCommented:
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

Open in new window

0
David BigelowStaff Operations SpecialistAuthor Commented:
@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.
0
David BigelowStaff Operations SpecialistAuthor Commented:
@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.
0
aikimarkCommented:
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;

Open in new window

0
David BigelowStaff Operations SpecialistAuthor Commented:
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_#]=TBL_ISSUES.[EDR#] Sorry for the inconsistency in naming the EDR fields.
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.
0
aikimarkCommented:
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;

Open in new window

0
David BigelowStaff Operations SpecialistAuthor Commented:
@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.
0
aikimarkCommented:
I added the other table into the correlated sub-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#]

WHERE 
Status Not In (11,12,24) 
And [TBL_Defects_DCRs].Top5 = True

UNION ALL 

SELECT Top 10 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_Defects_DCRs].Top5=False 
And Status Not In (11,12,24) 
And TBL_ISSUES.Rank In  
(SELECT top 10 T10.Rank 
FROM TBL_Defects_DCRs As D INNER JOIN TBL_ISSUES As T10 ON D.[EDR_#]=T10.[EDR#]
WHERE T10.Status Not In (11,12,24) 
And T10.system = [TBL_ISSUES].system 
And D.Top5=False
ORDER BY T10.Rank DESC )


ORDER BY TBL_ISSUES.System, TBL_Defects_DCRs.Top5, TBL_ISSUES.Rank DESC;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
David BigelowStaff Operations SpecialistAuthor Commented:
@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.
0
David BigelowStaff Operations SpecialistAuthor Commented:
@Paul,

I appreciate you staying with me through this in looking for a solution!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.