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,
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)<&g
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,
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)<&g
In (SELECT TOP 10 Rank
FROM TBL_ISSUES as R WHERE R.System = TBL_ISSUES.System))
AND ((IsNull([Workload]))=Fals
In (SELECT TOP 10 Rank FROM TBL_ISSUES as R WHERE R.System = TBL_ISSUES.System)))
ORDER BY TBL_ISSUES.Rank DESC;
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,
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)<&g
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,
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)<&g
In (SELECT TOP 10 Rank
FROM TBL_ISSUES as R WHERE R.System = TBL_ISSUES.System))
AND ((IsNull([Workload]))=Fals
In (SELECT TOP 10 Rank FROM TBL_ISSUES as R WHERE R.System = TBL_ISSUES.System)))
ORDER BY TBL_ISSUES.Rank DESC;
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.
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 ;ASKER
Thanks. Right now I'm looking into why that's giving me a "Reserved error, 3099."
ASKER
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
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
hmm, does your WHERE conditions contain a redundant part? When I clean it up, I'll get:
Which is imho the same as
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; 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; 
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.
Using "temporary" tables for reporting is a common design pattern.
ASKER
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,
FROM TBL_Defects_DCRs INNER JOIN TBL_ISSUES ON TBL_Defects_DCRs.[EDR_#]=T
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.
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,
FROM TBL_Defects_DCRs INNER JOIN TBL_ISSUES ON TBL_Defects_DCRs.[EDR_#]=T
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.
In the database you uploaded, only EDR# 17286 has more than one Issue that meets the status and rank critera.
ASKER
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.
Are you trying to find ranks within EDR# or something else?
ASKER
Yes.
Please post some data where that data might be reflected.
ASKER
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.
ASKER
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.
ASKER
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.
ASKER
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.
You are looking for the top 10 problems within CITY, not within EDR#
ASKER
Yes, you're right. I misconstrued what you said. And in my comments, I talk about system. The city label is system.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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]))=Fals
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!
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]))=Fals
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!
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.
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.
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;ASKER
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.
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.
@David
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.
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.
ASKER
I posted a follow-up question, here: https://www.experts-exchange.com/questions/28737456/How-do-I-modify-this-SQL-subquery-in-Access-2007-for-Top-5.html
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?