|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
June 30th, 2008, 09:33 PM
posted to microsoft.public.access.queries
|
|||
|
|||
display top 5 records for each subcategory
I need to know how to return just the top 5 DUs (Based on sum of cases lost)
for each Code desc. Below is my current SQL that returns back all records for all CodeDesc Please help? Thanks! SELECT [(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat, [(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat_Description, [(0005a1)InternalDrilldown - HPCStockAvail].CutDelayCode, [(0005a1)InternalDrilldown - HPCStockAvail].CodeDesc, [(0005a1)InternalDrilldown - HPCStockAvail].DU, [(0005a1)InternalDrilldown - HPCStockAvail].Description, Sum([(0005a1)InternalDrilldown - HPCStockAvail].CasesLost) AS SumOfCasesLost FROM [(0005a1)InternalDrilldown - HPCStockAvail] GROUP BY [(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat, [(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat_Description, [(0005a1)InternalDrilldown - HPCStockAvail].CutDelayCode, [(0005a1)InternalDrilldown - HPCStockAvail].CodeDesc, [(0005a1)InternalDrilldown - HPCStockAvail].DU, [(0005a1)InternalDrilldown - HPCStockAvail].Description ORDER BY Sum([(0005a1)InternalDrilldown - HPCStockAvail].CasesLost) DESC; 
|
|
#2
June 30th, 2008, 10:56 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
display top 5 records for each subcategory
On Jun 30, 3:33*pm, lay wrote:
I need to know how to return just the top 5 DUs (Based on sum of cases lost) for each Code desc. Below is my current SQL that returns back all records for all CodeDesc Please help? Thanks! SELECT [(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat, [(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat_Description, [(0005a1)InternalDrilldown - HPCStockAvail].CutDelayCode, [(0005a1)InternalDrilldown - HPCStockAvail].CodeDesc, [(0005a1)InternalDrilldown - HPCStockAvail].DU, [(0005a1)InternalDrilldown - HPCStockAvail].Description, Sum([(0005a1)InternalDrilldown - HPCStockAvail].CasesLost) AS SumOfCasesLost FROM [(0005a1)InternalDrilldown - HPCStockAvail] GROUP BY [(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat, [(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat_Description, [(0005a1)InternalDrilldown - HPCStockAvail].CutDelayCode, [(0005a1)InternalDrilldown - HPCStockAvail].CodeDesc, [(0005a1)InternalDrilldown - HPCStockAvail].DU, [(0005a1)InternalDrilldown - HPCStockAvail].Description ORDER BY Sum([(0005a1)InternalDrilldown - HPCStockAvail].CasesLost) DESC; use the TOP VALUES predicate. SELECT TOP 5 [(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat, [(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat_Description, [(0005a1)InternalDrilldown - HPCStockAvail].CutDelayCode,...
|
|
#3
July 1st, 2008, 12:23 AM
posted to microsoft.public.access.queries
|
|||
|
|||
|
display top 5 records for each subcategory
Try this --
SELECT Q.DU, Q.CasesLost, (SELECT COUNT(*) FROM [(0005a1)InternalDrilldown - HPCStockAvail] Q1 WHERE Q1.[DU] = Q.[DU] AND Q1.[CasesLost] = Q.[CasesLost]) AS Rank FROM [(0005a1)InternalDrilldown - HPCStockAvail] AS Q WHERE ((((SELECT COUNT(*) FROM [(0005a1)InternalDrilldown - HPCStockAvail] Q1 WHERE Q1.[DU] = Q.[DU] AND Q1.[CasesLost] = Q.[CasesLost]))=5)) ORDER BY Q.DU, Q.CasesLost DESC; -- KARL DEWEY Build a little - Test a little "lay" wrote: I need to know how to return just the top 5 DUs (Based on sum of cases lost) for each Code desc. Below is my current SQL that returns back all records for all CodeDesc Please help? Thanks! SELECT [(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat, [(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat_Description, [(0005a1)InternalDrilldown - HPCStockAvail].CutDelayCode, [(0005a1)InternalDrilldown - HPCStockAvail].CodeDesc, [(0005a1)InternalDrilldown - HPCStockAvail].DU, [(0005a1)InternalDrilldown - HPCStockAvail].Description, Sum([(0005a1)InternalDrilldown - HPCStockAvail].CasesLost) AS SumOfCasesLost FROM [(0005a1)InternalDrilldown - HPCStockAvail] GROUP BY [(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat, [(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat_Description, [(0005a1)InternalDrilldown - HPCStockAvail].CutDelayCode, [(0005a1)InternalDrilldown - HPCStockAvail].CodeDesc, [(0005a1)InternalDrilldown - HPCStockAvail].DU, [(0005a1)InternalDrilldown - HPCStockAvail].Description ORDER BY Sum([(0005a1)InternalDrilldown - HPCStockAvail].CasesLost) DESC;
|
|
#4
July 1st, 2008, 02:06 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
display top 5 records for each subcategory
What does the Q represent? Do I substitute it for the table name?
I pasted the whole string (as is) into my current SQL, right before the "FROM" and I got the error message: "The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect" Sorry, i'm new at SQL "KARL DEWEY" wrote: Try this -- SELECT Q.DU, Q.CasesLost, (SELECT COUNT(*) FROM [(0005a1)InternalDrilldown - HPCStockAvail] Q1 WHERE Q1.[DU] = Q.[DU] AND Q1.[CasesLost] = Q.[CasesLost]) AS Rank FROM [(0005a1)InternalDrilldown - HPCStockAvail] AS Q WHERE ((((SELECT COUNT(*) FROM [(0005a1)InternalDrilldown - HPCStockAvail] Q1 WHERE Q1.[DU] = Q.[DU] AND Q1.[CasesLost] = Q.[CasesLost]))=5)) ORDER BY Q.DU, Q.CasesLost DESC; -- KARL DEWEY Build a little - Test a little "lay" wrote: I need to know how to return just the top 5 DUs (Based on sum of cases lost) for each Code desc. Below is my current SQL that returns back all records for all CodeDesc Please help? Thanks! SELECT [(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat, [(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat_Description, [(0005a1)InternalDrilldown - HPCStockAvail].CutDelayCode, [(0005a1)InternalDrilldown - HPCStockAvail].CodeDesc, [(0005a1)InternalDrilldown - HPCStockAvail].DU, [(0005a1)InternalDrilldown - HPCStockAvail].Description, Sum([(0005a1)InternalDrilldown - HPCStockAvail].CasesLost) AS SumOfCasesLost FROM [(0005a1)InternalDrilldown - HPCStockAvail] GROUP BY [(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat, [(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat_Description, [(0005a1)InternalDrilldown - HPCStockAvail].CutDelayCode, [(0005a1)InternalDrilldown - HPCStockAvail].CodeDesc, [(0005a1)InternalDrilldown - HPCStockAvail].DU, [(0005a1)InternalDrilldown - HPCStockAvail].Description ORDER BY Sum([(0005a1)InternalDrilldown - HPCStockAvail].CasesLost) DESC;
|
|
#5
July 1st, 2008, 02:07 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
display top 5 records for each subcategory
Thanks. I've tried that before, but all it does is return the top 5 values
for the whole record set. I need it to be the top 5 (or less) values of each subcategory in the record set. " wrote: On Jun 30, 3:33 pm, lay wrote: I need to know how to return just the top 5 DUs (Based on sum of cases lost) for each Code desc. Below is my current SQL that returns back all records for all CodeDesc Please help? Thanks! SELECT [(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat, [(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat_Description, [(0005a1)InternalDrilldown - HPCStockAvail].CutDelayCode, [(0005a1)InternalDrilldown - HPCStockAvail].CodeDesc, [(0005a1)InternalDrilldown - HPCStockAvail].DU, [(0005a1)InternalDrilldown - HPCStockAvail].Description, Sum([(0005a1)InternalDrilldown - HPCStockAvail].CasesLost) AS SumOfCasesLost FROM [(0005a1)InternalDrilldown - HPCStockAvail] GROUP BY [(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat, [(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat_Description, [(0005a1)InternalDrilldown - HPCStockAvail].CutDelayCode, [(0005a1)InternalDrilldown - HPCStockAvail].CodeDesc, [(0005a1)InternalDrilldown - HPCStockAvail].DU, [(0005a1)InternalDrilldown - HPCStockAvail].Description ORDER BY Sum([(0005a1)InternalDrilldown - HPCStockAvail].CasesLost) DESC; use the TOP VALUES predicate. SELECT TOP 5 [(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat, [(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat_Description, [(0005a1)InternalDrilldown - HPCStockAvail].CutDelayCode,...
|
|
#6
July 1st, 2008, 02:48 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
display top 5 records for each subcategory
Never mind. Please disregard the post above - I just needed my coffee before
attempting to dissect any SQL strings! The reason why the error message popped up is because Q1 was never defined - the "AS" was missing. Once i popped that in, it runs fine...although extremely slowly. Thanks for your help! "lay" wrote: What does the Q represent? Do I substitute it for the table name? I pasted the whole string (as is) into my current SQL, right before the "FROM" and I got the error message: "The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect" Sorry, i'm new at SQL "KARL DEWEY" wrote: Try this -- SELECT Q.DU, Q.CasesLost, (SELECT COUNT(*) FROM [(0005a1)InternalDrilldown - HPCStockAvail] Q1 WHERE Q1.[DU] = Q.[DU] AND Q1.[CasesLost] = Q.[CasesLost]) AS Rank FROM [(0005a1)InternalDrilldown - HPCStockAvail] AS Q WHERE ((((SELECT COUNT(*) FROM [(0005a1)InternalDrilldown - HPCStockAvail] Q1 WHERE Q1.[DU] = Q.[DU] AND Q1.[CasesLost] = Q.[CasesLost]))=5)) ORDER BY Q.DU, Q.CasesLost DESC; -- KARL DEWEY Build a little - Test a little "lay" wrote: I need to know how to return just the top 5 DUs (Based on sum of cases lost) for each Code desc. Below is my current SQL that returns back all records for all CodeDesc Please help? Thanks! SELECT [(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat, [(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat_Description, [(0005a1)InternalDrilldown - HPCStockAvail].CutDelayCode, [(0005a1)InternalDrilldown - HPCStockAvail].CodeDesc, [(0005a1)InternalDrilldown - HPCStockAvail].DU, [(0005a1)InternalDrilldown - HPCStockAvail].Description, Sum([(0005a1)InternalDrilldown - HPCStockAvail].CasesLost) AS SumOfCasesLost FROM [(0005a1)InternalDrilldown - HPCStockAvail] GROUP BY [(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat, [(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat_Description, [(0005a1)InternalDrilldown - HPCStockAvail].CutDelayCode, [(0005a1)InternalDrilldown - HPCStockAvail].CodeDesc, [(0005a1)InternalDrilldown - HPCStockAvail].DU, [(0005a1)InternalDrilldown - HPCStockAvail].Description ORDER BY Sum([(0005a1)InternalDrilldown - HPCStockAvail].CasesLost) DESC;
|
|
#7
July 2nd, 2008, 01:29 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
display top 5 records for each subcategory
To improve speed, you can try:
"lay" wrote in message ... Never mind. Please disregard the post above - I just needed my coffee before attempting to dissect any SQL strings! The reason why the error message popped up is because Q1 was never defined - the "AS" was missing. Once i popped that in, it runs fine...although extremely slowly. Thanks for your help! "lay" wrote: What does the Q represent? Do I substitute it for the table name? I pasted the whole string (as is) into my current SQL, right before the "FROM" and I got the error message: "The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect" Sorry, i'm new at SQL "KARL DEWEY" wrote: Try this -- SELECT Q.DU, Q.CasesLost, (SELECT COUNT(*) FROM [(0005a1)InternalDrilldown - HPCStockAvail] Q1 WHERE Q1.[DU] = Q.[DU] AND Q1.[CasesLost] = Q.[CasesLost]) AS Rank FROM [(0005a1)InternalDrilldown - HPCStockAvail] AS Q WHERE ((((SELECT COUNT(*) FROM [(0005a1)InternalDrilldown - HPCStockAvail] Q1 WHERE Q1.[DU] = Q.[DU] AND Q1.[CasesLost] = Q.[CasesLost]))=5)) ORDER BY Q.DU, Q.CasesLost DESC; -- KARL DEWEY Build a little - Test a little "lay" wrote: I need to know how to return just the top 5 DUs (Based on sum of cases lost) for each Code desc. Below is my current SQL that returns back all records for all CodeDesc Please help? Thanks! SELECT [(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat, [(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat_Description, [(0005a1)InternalDrilldown - HPCStockAvail].CutDelayCode, [(0005a1)InternalDrilldown - HPCStockAvail].CodeDesc, [(0005a1)InternalDrilldown - HPCStockAvail].DU, [(0005a1)InternalDrilldown - HPCStockAvail].Description, Sum([(0005a1)InternalDrilldown - HPCStockAvail].CasesLost) AS SumOfCasesLost FROM [(0005a1)InternalDrilldown - HPCStockAvail] GROUP BY [(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat, [(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat_Description, [(0005a1)InternalDrilldown - HPCStockAvail].CutDelayCode, [(0005a1)InternalDrilldown - HPCStockAvail].CodeDesc, [(0005a1)InternalDrilldown - HPCStockAvail].DU, [(0005a1)InternalDrilldown - HPCStockAvail].Description ORDER BY Sum([(0005a1)InternalDrilldown - HPCStockAvail].CasesLost) DESC;
|
|
#8
July 2nd, 2008, 01:46 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
display top 5 records for each subcategory
To increase speed, you can try:
SELECT Q.du, Q.casesLost FROM [(0005a1)InternalDrilldown - HPCStockAvail] AS Q INNER JOIN [(0005a1)InternalDrilldown - HPCStockAvail] AS Q1 ON Q.du = Q1.du AND Q.casesLost = Q1.casesLost GROUP BY Q.du, Q.casesLost HAVING COUNT(*) = 5 You can even improve the speed further if you can make a temp table of your query [(0005a1)InternalDrilldown - HPCStockAvail], and index fields du and casesLost (you cannot add indexes to a query, that is why you need a temp table). You can even speed up furthermore the whole thing by using a temporary table and an intermediate query, and some extra work: have a table temp, empty, with 3 fields: an autonumber, au, the primary key, plus the two fields du, and casesLost. Append the data to temp from your query with: INSERT INTO temp(du, casesLost) SELECT du, casesLost FROM [(0005a1)InternalDrilldown - HPCStockAvail] ORDER BY du, casesLost The autonumber will automatically be filled. then: SELECT du, MIN(au) AS myMin FROM temp GROUP BY du as saved query qmin, return the autonumber value which starts each sequence, so finally SELECT a.du, a.casesLost, a.au-b.myMin + 1 AS rank FROM temp AS a INNER JOIN qmin AS b ON a.du = b.du AND a.au = 5=b.myMin should return the desired values. Note that you have to repeat the sequence: empty table temp, append data, run the last query each time some data changed. Maybe better doing that with a VBA subroutine rather than calling these steps, manually. Vanderghast, Access MVP "lay" wrote in message ... Never mind. Please disregard the post above - I just needed my coffee before attempting to dissect any SQL strings! The reason why the error message popped up is because Q1 was never defined - the "AS" was missing. Once i popped that in, it runs fine...although extremely slowly. Thanks for your help! "lay" wrote: What does the Q represent? Do I substitute it for the table name? I pasted the whole string (as is) into my current SQL, right before the "FROM" and I got the error message: "The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect" Sorry, i'm new at SQL "KARL DEWEY" wrote: Try this -- SELECT Q.DU, Q.CasesLost, (SELECT COUNT(*) FROM [(0005a1)InternalDrilldown - HPCStockAvail] Q1 WHERE Q1.[DU] = Q.[DU] AND Q1.[CasesLost] = Q.[CasesLost]) AS Rank FROM [(0005a1)InternalDrilldown - HPCStockAvail] AS Q WHERE ((((SELECT COUNT(*) FROM [(0005a1)InternalDrilldown - HPCStockAvail] Q1 WHERE Q1.[DU] = Q.[DU] AND Q1.[CasesLost] = Q.[CasesLost]))=5)) ORDER BY Q.DU, Q.CasesLost DESC; -- KARL DEWEY Build a little - Test a little "lay" wrote: I need to know how to return just the top 5 DUs (Based on sum of cases lost) for each Code desc. Below is my current SQL that returns back all records for all CodeDesc Please help? Thanks! SELECT [(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat, [(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat_Description, [(0005a1)InternalDrilldown - HPCStockAvail].CutDelayCode, [(0005a1)InternalDrilldown - HPCStockAvail].CodeDesc, [(0005a1)InternalDrilldown - HPCStockAvail].DU, [(0005a1)InternalDrilldown - HPCStockAvail].Description, Sum([(0005a1)InternalDrilldown - HPCStockAvail].CasesLost) AS SumOfCasesLost FROM [(0005a1)InternalDrilldown - HPCStockAvail] GROUP BY [(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat, [(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat_Description, [(0005a1)InternalDrilldown - HPCStockAvail].CutDelayCode, [(0005a1)InternalDrilldown - HPCStockAvail].CodeDesc, [(0005a1)InternalDrilldown - HPCStockAvail].DU, [(0005a1)InternalDrilldown - HPCStockAvail].Description ORDER BY Sum([(0005a1)InternalDrilldown - HPCStockAvail].CasesLost) DESC;
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
