How do I limit the items per group in this SQL query for Access 2007?

In the report for this query in Access 2007, defects and DCRs are grouped by system and ordered by descending rank. The grouping is done on the report level. I'm open to it being done in the query. I want to get the top 10 ranked defects and DCRs for each system.

This is the query I need to modify. It has two tables: TBL_DEFECTS_DCRs and TBL_ISSUES, joined on EDR_#, which is the Defect/DCR number.

Rank and System are on the table, TBL_ISSUES.

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.Workload) Is Not Null) AND ((TBL_ISSUES.Status)<>11 And (TBL_ISSUES.Status)<>12 And (TBL_ISSUES.Status)<>24) 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))
ORDER BY TBL_ISSUES.Rank DESC;

I tried the following, but it cuts out a lot of the higher ranked defects and DCRs.

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.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))

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 DESC;
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.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
>>I want to get the top 10 ranked defects and DCRs for each system.
I guess we may need to use subquery in Access to achieve this...

Do you have a sample (mask your critical data) to upload so that it's easier for us to diagnose your issue?
0
RayData AnalystCommented:
Shot in the dark here as I could be totally wrong, but I added ONE line to each of your subqueries (the order by rank statement).  I have no way to test this theory easily here, so give it a spin.

Normally you wouldn't be able to order by in a subquery, but since your sub query is using a "top" statement, it should be included.

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.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 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 rank asc
				)
			)
		)
ORDER BY TBL_ISSUES.Rank DESC ;

Open in new window

0
David BigelowStaff Operations SpecialistAuthor Commented:
Thanks. Right now I'm looking into why that's giving me a "Reserved error, 3099."
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

David BigelowStaff Operations SpecialistAuthor Commented:
I took some time to strip the data and have uploaded a sample file.

Pertinent objects are:
rptDefectsDCRsTop5
qryDefectsDCRsTop5
and associated tables

Top 5 refers to issues that are in resolution. This report is Top 10 so that we can give the next issue to move into resolution once a position opens up. I understand there may be more than 10 when issues have equal ranking, that's fine.
CopyofIssuesDatabase.accdb
0
ste5anSenior DeveloperCommented:
hmm, does your WHERE conditions contain a redundant part? When I clean it up, I'll get:

SELECT  *
FROM    TBL_Defects_DCRs D
        INNER JOIN TBL_ISSUES I ON D.[EDR_#] = I.[EDR#]
WHERE   ( I.Workload IS NOT NULL
          AND I.Status <> 11
          AND I.Status <> 12
          AND I.Status <> 24
          AND I.Rank IN ( SELECT TOP 10
                                    R.Rank
                          FROM      TBL_ISSUES AS R
                          WHERE     R.System = I.System )
        )
        OR ( I.Workload IS NULL
             AND I.Status <> 11
             AND I.Status <> 12
             AND I.Status <> 24
             AND I.Rank IN ( SELECT TOP 10
                                    R.Rank
                             FROM   TBL_ISSUES AS R
                             WHERE  R.System = I.System )
           )
ORDER BY I.Rank DESC; 

Open in new window


Which is imho the same as

SELECT  *
FROM    TBL_Defects_DCRs D
        INNER JOIN TBL_ISSUES I ON D.[EDR_#] = I.[EDR#]
WHERE   I.Status <> 11
        AND I.Status <> 12
        AND I.Status <> 24
        AND I.Rank IN ( SELECT TOP 10
                                R.Rank
                        FROM    TBL_ISSUES AS R
                        WHERE   R.System = I.System )
ORDER BY I.Rank DESC; 

Open in new window

0
aikimarkCommented:
I've never been successful in doing this strictly with (only) queries in Access.  I've always had to populate a temporary table with intermediate results.
0
ste5anSenior DeveloperCommented:
Using "temporary" tables for reporting is a common design pattern.
0
David BigelowStaff Operations SpecialistAuthor Commented:
Thank you, ste5an,

The D and I substitutions were creating a lot of Expression columns for me and then the query wouldn't return any records, so I took them out, as such:

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.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 R.Rank                                                  
FROM   TBL_ISSUES as R                                                  
WHERE R.System = TBL_ISSUES.System  ) )                
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 R.Rank                                                  
FROM   TBL_ISSUES as R                                                  
WHERE R.System = TBL_ISSUES.System  ) )
ORDER BY TBL_ISSUES.Rank DESC;

Notice how when you use this query in the top 5 report (in the sample database I posted earlier), but take out the Top 10 limit, higher values appear, for example, under the system London. Several issues ranked at 84, 76, 74, and 65 are on the report that don't show up when the top 10 limit is in place. Instead, the highest rank is 61 with the top 10 limit in place. That's the quandary I've been trying to overcome.
0
aikimarkCommented:
In the database you uploaded, only EDR# 17286 has more than one Issue that meets the status and rank critera.
0
David BigelowStaff Operations SpecialistAuthor Commented:
Here are two screenshots of what's happening when I put the query above into the database (attached earlier) in the query qryDefectsDCRsTop5 and run the report rptDefectsDCRsTop5. The Top 10 does limit the issues per system, but in so doing, it cuts out some of the higher ranked issues that should be in the Top 10 ranked issues.Query without Top 10 limitQuery with Top 10 limit
0
aikimarkCommented:
Are you trying to find ranks within EDR# or something else?
0
David BigelowStaff Operations SpecialistAuthor Commented:
Yes.
0
aikimarkCommented:
Please post some data where that data might be reflected.
0
David BigelowStaff Operations SpecialistAuthor Commented:
Well, I just did, both in the database and in the pictures from the report. It represents exactly what is wrong. Maybe I'm not understanding how it could be clearer.
0
David BigelowStaff Operations SpecialistAuthor Commented:
If you look at the first report picture, it has all the defects and DCRs listed. I want to limit that report to the top 10 ranked Defects and DCRs per system. Sorry, I was getting frustrated. Please let me know what else I'm not explaining.
0
David BigelowStaff Operations SpecialistAuthor Commented:
EDR# refers to the tracking number for the  "Defect and DCR." I used EDR# in the background while the database was being developed when it was unclear how it should be labeled.
0
David BigelowStaff Operations SpecialistAuthor Commented:
And, I wouldn't say it is ranks "within an EDR#."  Rather, I am listing a lot of EDR#s, which are the tracking numbers for defects and DCRs (Design Change Requests). I want to sort those defects and DCRs by rank so that we can work on the most important, first. Limiting the count to the top 10 ranked defects and DCRs per system will avoid printing 40 pages of defects and DCRs for a report.
0
aikimarkCommented:
You are looking for the top 10 problems within CITY, not within EDR#
0
David BigelowStaff Operations SpecialistAuthor Commented:
Yes, you're right. I misconstrued what you said. And in my comments, I talk about system. The city label is system.
0
aikimarkCommented:
Let's start with this and discuss:
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 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;

Open in new window

If you want a more clear picture, add tbl_Systems to the mix and display the city.
1

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,

Splendidly awesome!

And thanks for opening it to discussion. That will help me to learn.

First, this coding seems to remove the coding that allowed the parameter prompts to work, to wit the Null, Not Null, and False statements in the coding below. But, since this report is short, just the Top 10, I think leaving the parameter prompts out completely is preferable. I'll remove them.

WHERE (((TBL_ISSUES.Workload) Is Not Null) AND ((TBL_ISSUES.Status) <> 11 And (TBL_ISSUES.Status)<> 12 And (TBL_ISSUES.Status) <> 24) 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))

Second, I used some letter substitutions such as the R and you used an I, but I'm not sure I understand them completely. My thought was that when setting an equal that the columns would be the same, but the tables would be different. For example: tblDefect.System = tblIssues.System. Using D and T, D.System = I.System. But you used I for TBL_ISSUES and then had I.system = TBL_ISSUES.system, which to me is saying the same thing. Would you please clarify that coding logic for me and/or refer me to some good reading on it?

Third, if there are less than 10 defects and DCRs in a system (city), how could this be modified to include the defects and DCRs that didn't get ranked? For example, in the Montezuma system, three defects and DCRs show up in the Top 10 report, but there is a fourth one that doesn't display because it was never ranked. Since there are some positions left in the Top 10 limit, I'd like to include any non-ranked defects and DCRs. I'm thinking that it again uses some Null coding, but this subquery wrinkle makes everything new to me.

Again, thank you so much!
0
aikimarkCommented:
Those letters are aliases for the table names.  Since the correlated sub-query is accessing the same table as the outer (main) query, the table needs to be aliased.  I tend to use "I" for aliases in INNER queries.  These aliases are not limited to single letters.  I could have used "T10" or "T10LU" to help (self-) document that part of the query.

It is unclear which items will be included in your unranked complement.  I barely understand what you are trying to do, only inferring meaning from the data you posted.  You will need to supply a much more detailed explanation.
0
aikimarkCommented:
In this example, you will only see groups where there are 10 or more ranking rows in the result.  I assume that the unranked hybrid result set will be unioned to this 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 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 
ORDER BY T10.Rank DESC )
And
(Select Count(*) FROM TBL_ISSUES As GrpCnt WHERE GrpCnt.Status Not In (11,12,24) And GrpCnt.system = [TBL_ISSUES].system )>=10

ORDER BY TBL_ISSUES.System, TBL_ISSUES.Rank DESC;

Open in new window

0
David BigelowStaff Operations SpecialistAuthor Commented:
aikimark,

Thank you for your help! In looking further into what I asked to modify the code, I would really need to develop a whole other set of criteria of what to display when the rank is null. Some systems have upwards of 50 defects not ranked, so I would need to develop another set of criteria of what to display next in the top 10 if not based on rank. I think that would be another question. And for the purpose of this report, I don't think it's necessary. We just need the top 10 of what's been ranked.

Thank you for explaining how the abbreviation was used in the subquery. You've been a phenomenal help.
0
aikimarkCommented:
@David
You've been a phenomenal help.
Thanks.  Glad to have helped.

When you post a new question, include a link to this question as it is prior related material, beneficial to the experts.  Also, come back to this thread and post a link to your new question.
0
David BigelowStaff Operations SpecialistAuthor Commented:
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.