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
|
|||
|
|||
Top N Records minus lower scores
Hello
I am a teacher and am trying to move my students grades from Excel to Access. I plan on using the Classroom management database from MS Office template, and then expanding upon that dbase as needed. One of the expansions, I am struggling with. During the semester, I will give out quizes and homeworks. I may give out 19 quizes, but I will only count 15 of those. The extra four can be dropped. In other words, the student will take all the quizes and then the lowest 4 will drop, so even though they will take 19 quizes, only 15 count toward their final grade. The homeworks will work much the same way, however, the 19th homework is mandatory. So the student will complete 19 homeworks, only the top 15 and the 19th will count toward their final grade. I have been struggling the past couple of days to get the SQL correct for this quiz. At present, I am using the sample data from the Classroom management database to get the SQL correct before entering it on my own database. For the data below, I have run SQL on several tables, as I believe that this would be necessary. Student ID Results ID Name Class Name Assignment Description Score 2 87 Alan Shen Great Works Quiz - Quiz #4 10 2 97 Alan Shen Great Works Quiz - Quiz #6 10 2 102 Alan Shen Great Works Quiz - Quiz #7 10 2 2 Alan Shen Great Works Quiz - Quiz #1 9 2 82 Alan Shen Great Works Quiz - Quiz #3 9 2 77 Alan Shen Great Works Quiz - Quiz #2 8 2 92 Alan Shen Great Works Quiz - Quiz #5 6 3 78 Conor C Great Works Quiz - Quiz #2 10 3 88 Conor C Great Works Quiz - Quiz #4 10 3 103 Conor C Great Works Quiz - Quiz #7 10 3 3 Conor C Great Works Quiz - Quiz #1 9 3 83 Conor C Great Works Quiz - Quiz #3 8 3 93 Conor C Great Works Quiz - Quiz #5 8 3 98 Conor C Great Works Quiz - Quiz #6 8 6 80 Erik A Great Works Quiz - Quiz #2 10 6 85 Erik A Great Works Quiz - Quiz #3 10 6 90 Erik A Great Works Quiz - Quiz #4 10 6 100 Erik A Great Works Quiz - Quiz #6 10 6 105 Erik A Great Works Quiz - Quiz #7 10 6 5 Erik A Great Works Quiz - Quiz #1 0 6 95 Erik A Great Works Quiz - Quiz #5 0 From this data above, I would like to run SQL that takes the top 5 (scaled down version of the real top 15) from each person in the class. So for Erik A, it would show 50 for a Score on Quizes. Below is some SQL for a subquery I have created after going through the discussion groups for other people with similar problems. However, it is still not quite working correctly. I know that Access will show ties in the data, and that appears to be what it is doing in my case. I have tried including the PK, but it still isn't quite right. SELECT A.Name, A.ClassName, A.AssignmentDescription, A.Score FROM Table1 AS A WHERE (((A.Score) In (SELECT Top 5 b.Score FROM Table1 as B WHERE b.StudentID = a.StudentID ORDER BY b.Score DESC))) ORDER BY A.StudentID, A.Score DESC; Any ideas for how to calculate the quizes? How about the homework with the one homework which is mandatory? Thanks D |
#2
|
|||
|
|||
Top N Records minus lower scores
Try this --
SELECT [Student ID], [Results ID], [Name], [Class Name], [Assignment Description], [Score] FROM YourTable WHERE [Assignment Description] = [Quiz - Quiz #19] UNION ALL SELECT TOP 14 [Student ID], [Results ID], [Name], [Class Name], [Assignment Description], [Score] FROM YourTable WHERE [Assignment Description] [Quiz - Quiz #19] ORDER BY [Score] DESC; -- Build a little, test a little. "Devon" wrote: Hello I am a teacher and am trying to move my students grades from Excel to Access. I plan on using the Classroom management database from MS Office template, and then expanding upon that dbase as needed. One of the expansions, I am struggling with. During the semester, I will give out quizes and homeworks. I may give out 19 quizes, but I will only count 15 of those. The extra four can be dropped. In other words, the student will take all the quizes and then the lowest 4 will drop, so even though they will take 19 quizes, only 15 count toward their final grade. The homeworks will work much the same way, however, the 19th homework is mandatory. So the student will complete 19 homeworks, only the top 15 and the 19th will count toward their final grade. I have been struggling the past couple of days to get the SQL correct for this quiz. At present, I am using the sample data from the Classroom management database to get the SQL correct before entering it on my own database. For the data below, I have run SQL on several tables, as I believe that this would be necessary. Student ID Results ID Name Class Name Assignment Description Score 2 87 Alan Shen Great Works Quiz - Quiz #4 10 2 97 Alan Shen Great Works Quiz - Quiz #6 10 2 102 Alan Shen Great Works Quiz - Quiz #7 10 2 2 Alan Shen Great Works Quiz - Quiz #1 9 2 82 Alan Shen Great Works Quiz - Quiz #3 9 2 77 Alan Shen Great Works Quiz - Quiz #2 8 2 92 Alan Shen Great Works Quiz - Quiz #5 6 3 78 Conor C Great Works Quiz - Quiz #2 10 3 88 Conor C Great Works Quiz - Quiz #4 10 3 103 Conor C Great Works Quiz - Quiz #7 10 3 3 Conor C Great Works Quiz - Quiz #1 9 3 83 Conor C Great Works Quiz - Quiz #3 8 3 93 Conor C Great Works Quiz - Quiz #5 8 3 98 Conor C Great Works Quiz - Quiz #6 8 6 80 Erik A Great Works Quiz - Quiz #2 10 6 85 Erik A Great Works Quiz - Quiz #3 10 6 90 Erik A Great Works Quiz - Quiz #4 10 6 100 Erik A Great Works Quiz - Quiz #6 10 6 105 Erik A Great Works Quiz - Quiz #7 10 6 5 Erik A Great Works Quiz - Quiz #1 0 6 95 Erik A Great Works Quiz - Quiz #5 0 From this data above, I would like to run SQL that takes the top 5 (scaled down version of the real top 15) from each person in the class. So for Erik A, it would show 50 for a Score on Quizes. Below is some SQL for a subquery I have created after going through the discussion groups for other people with similar problems. However, it is still not quite working correctly. I know that Access will show ties in the data, and that appears to be what it is doing in my case. I have tried including the PK, but it still isn't quite right. SELECT A.Name, A.ClassName, A.AssignmentDescription, A.Score FROM Table1 AS A WHERE (((A.Score) In (SELECT Top 5 b.Score FROM Table1 as B WHERE b.StudentID = a.StudentID ORDER BY b.Score DESC))) ORDER BY A.StudentID, A.Score DESC; Any ideas for how to calculate the quizes? How about the homework with the one homework which is mandatory? Thanks D |
#3
|
|||
|
|||
Top N Records minus lower scores
Karl
Thanks for the quick response. It is getting closer, but still not quite there. SELECT [StudentID], [ResultsID], [Name], [ClassName], [AssignmentDescription], [Score] FROM tbl1 WHERE [AssignmentDescription] = "Quiz - Quiz #19" UNION ALL SELECT TOP 14 [StudentID], [ResultsID], [Name], [ClassName], [AssignmentDescription], [Score] FROM tbl1 WHERE [AssignmentDescription] "Quiz - Quiz #19" ORDER BY [Score] DESC; Above is the same SQL you presented, slightly tweaked since 'Quiz...' data is a string. It is presenting all of the Quiz 19 data great, but it is only showing the first student, Alan Shen for the Top 14. I would like the Top 14 + 19 for all students. I am sure it has something to do with the ORDER BY, but have tried several tweaks without any success. Any suggestions? Thanks Devon "KARL DEWEY" wrote: Try this -- SELECT [Student ID], [Results ID], [Name], [Class Name], [Assignment Description], [Score] FROM YourTable WHERE [Assignment Description] = [Quiz - Quiz #19] UNION ALL SELECT TOP 14 [Student ID], [Results ID], [Name], [Class Name], [Assignment Description], [Score] FROM YourTable WHERE [Assignment Description] [Quiz - Quiz #19] ORDER BY [Score] DESC; -- Build a little, test a little. "Devon" wrote: Hello I am a teacher and am trying to move my students grades from Excel to Access. I plan on using the Classroom management database from MS Office template, and then expanding upon that dbase as needed. One of the expansions, I am struggling with. During the semester, I will give out quizes and homeworks. I may give out 19 quizes, but I will only count 15 of those. The extra four can be dropped. In other words, the student will take all the quizes and then the lowest 4 will drop, so even though they will take 19 quizes, only 15 count toward their final grade. The homeworks will work much the same way, however, the 19th homework is mandatory. So the student will complete 19 homeworks, only the top 15 and the 19th will count toward their final grade. I have been struggling the past couple of days to get the SQL correct for this quiz. At present, I am using the sample data from the Classroom management database to get the SQL correct before entering it on my own database. For the data below, I have run SQL on several tables, as I believe that this would be necessary. Student ID Results ID Name Class Name Assignment Description Score 2 87 Alan Shen Great Works Quiz - Quiz #4 10 2 97 Alan Shen Great Works Quiz - Quiz #6 10 2 102 Alan Shen Great Works Quiz - Quiz #7 10 2 2 Alan Shen Great Works Quiz - Quiz #1 9 2 82 Alan Shen Great Works Quiz - Quiz #3 9 2 77 Alan Shen Great Works Quiz - Quiz #2 8 2 92 Alan Shen Great Works Quiz - Quiz #5 6 3 78 Conor C Great Works Quiz - Quiz #2 10 3 88 Conor C Great Works Quiz - Quiz #4 10 3 103 Conor C Great Works Quiz - Quiz #7 10 3 3 Conor C Great Works Quiz - Quiz #1 9 3 83 Conor C Great Works Quiz - Quiz #3 8 3 93 Conor C Great Works Quiz - Quiz #5 8 3 98 Conor C Great Works Quiz - Quiz #6 8 6 80 Erik A Great Works Quiz - Quiz #2 10 6 85 Erik A Great Works Quiz - Quiz #3 10 6 90 Erik A Great Works Quiz - Quiz #4 10 6 100 Erik A Great Works Quiz - Quiz #6 10 6 105 Erik A Great Works Quiz - Quiz #7 10 6 5 Erik A Great Works Quiz - Quiz #1 0 6 95 Erik A Great Works Quiz - Quiz #5 0 From this data above, I would like to run SQL that takes the top 5 (scaled down version of the real top 15) from each person in the class. So for Erik A, it would show 50 for a Score on Quizes. Below is some SQL for a subquery I have created after going through the discussion groups for other people with similar problems. However, it is still not quite working correctly. I know that Access will show ties in the data, and that appears to be what it is doing in my case. I have tried including the PK, but it still isn't quite right. SELECT A.Name, A.ClassName, A.AssignmentDescription, A.Score FROM Table1 AS A WHERE (((A.Score) In (SELECT Top 5 b.Score FROM Table1 as B WHERE b.StudentID = a.StudentID ORDER BY b.Score DESC))) ORDER BY A.StudentID, A.Score DESC; Any ideas for how to calculate the quizes? How about the homework with the one homework which is mandatory? Thanks D |
#4
|
|||
|
|||
Top N Records minus lower scores
On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "TopQuery.doc" which illustrates solutions to a number of problems with TOP queries including agregating, grouping, duplicates due to ties, and parameterizing the TOP value. It comes with both a document and sample database. You can download it for free he http://www.rogersaccesslibrary.com/f...ts.asp?TID=233 -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "Devon" wrote in message ... Hello I am a teacher and am trying to move my students grades from Excel to Access. I plan on using the Classroom management database from MS Office template, and then expanding upon that dbase as needed. One of the expansions, I am struggling with. During the semester, I will give out quizes and homeworks. I may give out 19 quizes, but I will only count 15 of those. The extra four can be dropped. In other words, the student will take all the quizes and then the lowest 4 will drop, so even though they will take 19 quizes, only 15 count toward their final grade. The homeworks will work much the same way, however, the 19th homework is mandatory. So the student will complete 19 homeworks, only the top 15 and the 19th will count toward their final grade. I have been struggling the past couple of days to get the SQL correct for this quiz. At present, I am using the sample data from the Classroom management database to get the SQL correct before entering it on my own database. For the data below, I have run SQL on several tables, as I believe that this would be necessary. Student ID Results ID Name Class Name Assignment Description Score 2 87 Alan Shen Great Works Quiz - Quiz #4 10 2 97 Alan Shen Great Works Quiz - Quiz #6 10 2 102 Alan Shen Great Works Quiz - Quiz #7 10 2 2 Alan Shen Great Works Quiz - Quiz #1 9 2 82 Alan Shen Great Works Quiz - Quiz #3 9 2 77 Alan Shen Great Works Quiz - Quiz #2 8 2 92 Alan Shen Great Works Quiz - Quiz #5 6 3 78 Conor C Great Works Quiz - Quiz #2 10 3 88 Conor C Great Works Quiz - Quiz #4 10 3 103 Conor C Great Works Quiz - Quiz #7 10 3 3 Conor C Great Works Quiz - Quiz #1 9 3 83 Conor C Great Works Quiz - Quiz #3 8 3 93 Conor C Great Works Quiz - Quiz #5 8 3 98 Conor C Great Works Quiz - Quiz #6 8 6 80 Erik A Great Works Quiz - Quiz #2 10 6 85 Erik A Great Works Quiz - Quiz #3 10 6 90 Erik A Great Works Quiz - Quiz #4 10 6 100 Erik A Great Works Quiz - Quiz #6 10 6 105 Erik A Great Works Quiz - Quiz #7 10 6 5 Erik A Great Works Quiz - Quiz #1 0 6 95 Erik A Great Works Quiz - Quiz #5 0 From this data above, I would like to run SQL that takes the top 5 (scaled down version of the real top 15) from each person in the class. So for Erik A, it would show 50 for a Score on Quizes. Below is some SQL for a subquery I have created after going through the discussion groups for other people with similar problems. However, it is still not quite working correctly. I know that Access will show ties in the data, and that appears to be what it is doing in my case. I have tried including the PK, but it still isn't quite right. SELECT A.Name, A.ClassName, A.AssignmentDescription, A.Score FROM Table1 AS A WHERE (((A.Score) In (SELECT Top 5 b.Score FROM Table1 as B WHERE b.StudentID = a.StudentID ORDER BY b.Score DESC))) ORDER BY A.StudentID, A.Score DESC; Any ideas for how to calculate the quizes? How about the homework with the one homework which is mandatory? Thanks D |
#5
|
|||
|
|||
Top N Records minus lower scores
Assumption: Results ID is unique either within the table or by student.
SELECT [StudentID], [ResultsID], [Name], [ClassName], [AssignmentDescription], [Score] FROM Table1 as A WHERE [Results ID] in (SELECT TOP 5 [Results ID] FROM Table1 as Temp WHERE Temp.StudentID = A.StudentID ORDER BY Temp.Score DESC, Temp.[Results ID]) The homework problem can also be solved but I don't have the time right now. Gotta go see the cardiologist for my checkup. Good luck John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Devon wrote: Hello I am a teacher and am trying to move my students grades from Excel to Access. I plan on using the Classroom management database from MS Office template, and then expanding upon that dbase as needed. One of the expansions, I am struggling with. During the semester, I will give out quizes and homeworks. I may give out 19 quizes, but I will only count 15 of those. The extra four can be dropped. In other words, the student will take all the quizes and then the lowest 4 will drop, so even though they will take 19 quizes, only 15 count toward their final grade. The homeworks will work much the same way, however, the 19th homework is mandatory. So the student will complete 19 homeworks, only the top 15 and the 19th will count toward their final grade. I have been struggling the past couple of days to get the SQL correct for this quiz. At present, I am using the sample data from the Classroom management database to get the SQL correct before entering it on my own database. For the data below, I have run SQL on several tables, as I believe that this would be necessary. Student ID Results ID Name Class Name Assignment Description Score 2 87 Alan Shen Great Works Quiz - Quiz #4 10 2 97 Alan Shen Great Works Quiz - Quiz #6 10 2 102 Alan Shen Great Works Quiz - Quiz #7 10 2 2 Alan Shen Great Works Quiz - Quiz #1 9 2 82 Alan Shen Great Works Quiz - Quiz #3 9 2 77 Alan Shen Great Works Quiz - Quiz #2 8 2 92 Alan Shen Great Works Quiz - Quiz #5 6 3 78 Conor C Great Works Quiz - Quiz #2 10 3 88 Conor C Great Works Quiz - Quiz #4 10 3 103 Conor C Great Works Quiz - Quiz #7 10 3 3 Conor C Great Works Quiz - Quiz #1 9 3 83 Conor C Great Works Quiz - Quiz #3 8 3 93 Conor C Great Works Quiz - Quiz #5 8 3 98 Conor C Great Works Quiz - Quiz #6 8 6 80 Erik A Great Works Quiz - Quiz #2 10 6 85 Erik A Great Works Quiz - Quiz #3 10 6 90 Erik A Great Works Quiz - Quiz #4 10 6 100 Erik A Great Works Quiz - Quiz #6 10 6 105 Erik A Great Works Quiz - Quiz #7 10 6 5 Erik A Great Works Quiz - Quiz #1 0 6 95 Erik A Great Works Quiz - Quiz #5 0 From this data above, I would like to run SQL that takes the top 5 (scaled down version of the real top 15) from each person in the class. So for Erik A, it would show 50 for a Score on Quizes. Below is some SQL for a subquery I have created after going through the discussion groups for other people with similar problems. However, it is still not quite working correctly. I know that Access will show ties in the data, and that appears to be what it is doing in my case. I have tried including the PK, but it still isn't quite right. SELECT A.Name, A.ClassName, A.AssignmentDescription, A.Score FROM Table1 AS A WHERE (((A.Score) In (SELECT Top 5 b.Score FROM Table1 as B WHERE b.StudentID = a.StudentID ORDER BY b.Score DESC))) ORDER BY A.StudentID, A.Score DESC; Any ideas for how to calculate the quizes? How about the homework with the one homework which is mandatory? Thanks D |
#6
|
|||
|
|||
Top N Records minus lower scores
Did not test before. Again did not test but try this --
SELECT [StudentID], [ResultsID], [Name], [ClassName], [AssignmentDescription], [Score] FROM tbl1 WHERE [AssignmentDescription] = "Quiz - Quiz #19" UNION ALL SELECT [StudentID], [ResultsID], [Name], [ClassName], [AssignmentDescription], [Score] FROM tbl1 WHERE ResultsID IN (SELECT TOP 14 [XX].[ResultsID] FROM tbl1 AS [XX] WHERE [AssignmentDescription] "Quiz - Quiz #19" AND [XX].[StudentID] = tbl1.StudentID ORDER BY [Score] DESC); -- Build a little, test a little. "Devon" wrote: Karl Thanks for the quick response. It is getting closer, but still not quite there. SELECT [StudentID], [ResultsID], [Name], [ClassName], [AssignmentDescription], [Score] FROM tbl1 WHERE [AssignmentDescription] = "Quiz - Quiz #19" UNION ALL SELECT TOP 14 [StudentID], [ResultsID], [Name], [ClassName], [AssignmentDescription], [Score] FROM tbl1 WHERE [AssignmentDescription] "Quiz - Quiz #19" ORDER BY [Score] DESC; Above is the same SQL you presented, slightly tweaked since 'Quiz...' data is a string. It is presenting all of the Quiz 19 data great, but it is only showing the first student, Alan Shen for the Top 14. I would like the Top 14 + 19 for all students. I am sure it has something to do with the ORDER BY, but have tried several tweaks without any success. Any suggestions? Thanks Devon "KARL DEWEY" wrote: Try this -- SELECT [Student ID], [Results ID], [Name], [Class Name], [Assignment Description], [Score] FROM YourTable WHERE [Assignment Description] = [Quiz - Quiz #19] UNION ALL SELECT TOP 14 [Student ID], [Results ID], [Name], [Class Name], [Assignment Description], [Score] FROM YourTable WHERE [Assignment Description] [Quiz - Quiz #19] ORDER BY [Score] DESC; -- Build a little, test a little. "Devon" wrote: Hello I am a teacher and am trying to move my students grades from Excel to Access. I plan on using the Classroom management database from MS Office template, and then expanding upon that dbase as needed. One of the expansions, I am struggling with. During the semester, I will give out quizes and homeworks. I may give out 19 quizes, but I will only count 15 of those. The extra four can be dropped. In other words, the student will take all the quizes and then the lowest 4 will drop, so even though they will take 19 quizes, only 15 count toward their final grade. The homeworks will work much the same way, however, the 19th homework is mandatory. So the student will complete 19 homeworks, only the top 15 and the 19th will count toward their final grade. I have been struggling the past couple of days to get the SQL correct for this quiz. At present, I am using the sample data from the Classroom management database to get the SQL correct before entering it on my own database. For the data below, I have run SQL on several tables, as I believe that this would be necessary. Student ID Results ID Name Class Name Assignment Description Score 2 87 Alan Shen Great Works Quiz - Quiz #4 10 2 97 Alan Shen Great Works Quiz - Quiz #6 10 2 102 Alan Shen Great Works Quiz - Quiz #7 10 2 2 Alan Shen Great Works Quiz - Quiz #1 9 2 82 Alan Shen Great Works Quiz - Quiz #3 9 2 77 Alan Shen Great Works Quiz - Quiz #2 8 2 92 Alan Shen Great Works Quiz - Quiz #5 6 3 78 Conor C Great Works Quiz - Quiz #2 10 3 88 Conor C Great Works Quiz - Quiz #4 10 3 103 Conor C Great Works Quiz - Quiz #7 10 3 3 Conor C Great Works Quiz - Quiz #1 9 3 83 Conor C Great Works Quiz - Quiz #3 8 3 93 Conor C Great Works Quiz - Quiz #5 8 3 98 Conor C Great Works Quiz - Quiz #6 8 6 80 Erik A Great Works Quiz - Quiz #2 10 6 85 Erik A Great Works Quiz - Quiz #3 10 6 90 Erik A Great Works Quiz - Quiz #4 10 6 100 Erik A Great Works Quiz - Quiz #6 10 6 105 Erik A Great Works Quiz - Quiz #7 10 6 5 Erik A Great Works Quiz - Quiz #1 0 6 95 Erik A Great Works Quiz - Quiz #5 0 From this data above, I would like to run SQL that takes the top 5 (scaled down version of the real top 15) from each person in the class. So for Erik A, it would show 50 for a Score on Quizes. Below is some SQL for a subquery I have created after going through the discussion groups for other people with similar problems. However, it is still not quite working correctly. I know that Access will show ties in the data, and that appears to be what it is doing in my case. I have tried including the PK, but it still isn't quite right. SELECT A.Name, A.ClassName, A.AssignmentDescription, A.Score FROM Table1 AS A WHERE (((A.Score) In (SELECT Top 5 b.Score FROM Table1 as B WHERE b.StudentID = a.StudentID ORDER BY b.Score DESC))) ORDER BY A.StudentID, A.Score DESC; Any ideas for how to calculate the quizes? How about the homework with the one homework which is mandatory? Thanks D |
#7
|
|||
|
|||
Top N Records minus lower scores
Karl
Thanks so much for the assistance. Your SQL was nearly perfect. Here is the SQL I used, and everything works the way I need it. Thanks again. SELECT [StudentID], [ResultsID], [Name], [ClassName], [AssignmentDescription], [Score] FROM tbl1 WHERE [AssignmentDescription] = "Quiz - Quiz #19" UNION ALL SELECT [StudentID], [ResultsID], [Name], [ClassName], [AssignmentDescription], [Score] FROM tbl1 WHERE ResultsID IN (SELECT TOP 15 [XX].[ResultsID] FROM tbl1 AS [XX] WHERE [AssignmentDescription] "Quiz - Quiz #19" AND [XX].[StudentID] = tbl1.StudentID) ORDER BY Name DESC , Score DESC; Devon "KARL DEWEY" wrote: Did not test before. Again did not test but try this -- SELECT [StudentID], [ResultsID], [Name], [ClassName], [AssignmentDescription], [Score] FROM tbl1 WHERE [AssignmentDescription] = "Quiz - Quiz #19" UNION ALL SELECT [StudentID], [ResultsID], [Name], [ClassName], [AssignmentDescription], [Score] FROM tbl1 WHERE ResultsID IN (SELECT TOP 14 [XX].[ResultsID] FROM tbl1 AS [XX] WHERE [AssignmentDescription] "Quiz - Quiz #19" AND [XX].[StudentID] = tbl1.StudentID ORDER BY [Score] DESC); -- Build a little, test a little. "Devon" wrote: Karl Thanks for the quick response. It is getting closer, but still not quite there. SELECT [StudentID], [ResultsID], [Name], [ClassName], [AssignmentDescription], [Score] FROM tbl1 WHERE [AssignmentDescription] = "Quiz - Quiz #19" UNION ALL SELECT TOP 14 [StudentID], [ResultsID], [Name], [ClassName], [AssignmentDescription], [Score] FROM tbl1 WHERE [AssignmentDescription] "Quiz - Quiz #19" ORDER BY [Score] DESC; Above is the same SQL you presented, slightly tweaked since 'Quiz...' data is a string. It is presenting all of the Quiz 19 data great, but it is only showing the first student, Alan Shen for the Top 14. I would like the Top 14 + 19 for all students. I am sure it has something to do with the ORDER BY, but have tried several tweaks without any success. Any suggestions? Thanks Devon "KARL DEWEY" wrote: Try this -- SELECT [Student ID], [Results ID], [Name], [Class Name], [Assignment Description], [Score] FROM YourTable WHERE [Assignment Description] = [Quiz - Quiz #19] UNION ALL SELECT TOP 14 [Student ID], [Results ID], [Name], [Class Name], [Assignment Description], [Score] FROM YourTable WHERE [Assignment Description] [Quiz - Quiz #19] ORDER BY [Score] DESC; -- Build a little, test a little. "Devon" wrote: Hello I am a teacher and am trying to move my students grades from Excel to Access. I plan on using the Classroom management database from MS Office template, and then expanding upon that dbase as needed. One of the expansions, I am struggling with. During the semester, I will give out quizes and homeworks. I may give out 19 quizes, but I will only count 15 of those. The extra four can be dropped. In other words, the student will take all the quizes and then the lowest 4 will drop, so even though they will take 19 quizes, only 15 count toward their final grade. The homeworks will work much the same way, however, the 19th homework is mandatory. So the student will complete 19 homeworks, only the top 15 and the 19th will count toward their final grade. I have been struggling the past couple of days to get the SQL correct for this quiz. At present, I am using the sample data from the Classroom management database to get the SQL correct before entering it on my own database. For the data below, I have run SQL on several tables, as I believe that this would be necessary. Student ID Results ID Name Class Name Assignment Description Score 2 87 Alan Shen Great Works Quiz - Quiz #4 10 2 97 Alan Shen Great Works Quiz - Quiz #6 10 2 102 Alan Shen Great Works Quiz - Quiz #7 10 2 2 Alan Shen Great Works Quiz - Quiz #1 9 2 82 Alan Shen Great Works Quiz - Quiz #3 9 2 77 Alan Shen Great Works Quiz - Quiz #2 8 2 92 Alan Shen Great Works Quiz - Quiz #5 6 3 78 Conor C Great Works Quiz - Quiz #2 10 3 88 Conor C Great Works Quiz - Quiz #4 10 3 103 Conor C Great Works Quiz - Quiz #7 10 3 3 Conor C Great Works Quiz - Quiz #1 9 3 83 Conor C Great Works Quiz - Quiz #3 8 3 93 Conor C Great Works Quiz - Quiz #5 8 3 98 Conor C Great Works Quiz - Quiz #6 8 6 80 Erik A Great Works Quiz - Quiz #2 10 6 85 Erik A Great Works Quiz - Quiz #3 10 6 90 Erik A Great Works Quiz - Quiz #4 10 6 100 Erik A Great Works Quiz - Quiz #6 10 6 105 Erik A Great Works Quiz - Quiz #7 10 6 5 Erik A Great Works Quiz - Quiz #1 0 6 95 Erik A Great Works Quiz - Quiz #5 0 From this data above, I would like to run SQL that takes the top 5 (scaled down version of the real top 15) from each person in the class. So for Erik A, it would show 50 for a Score on Quizes. Below is some SQL for a subquery I have created after going through the discussion groups for other people with similar problems. However, it is still not quite working correctly. I know that Access will show ties in the data, and that appears to be what it is doing in my case. I have tried including the PK, but it still isn't quite right. SELECT A.Name, A.ClassName, A.AssignmentDescription, A.Score FROM Table1 AS A WHERE (((A.Score) In (SELECT Top 5 b.Score FROM Table1 as B WHERE b.StudentID = a.StudentID ORDER BY b.Score DESC))) ORDER BY A.StudentID, A.Score DESC; Any ideas for how to calculate the quizes? How about the homework with the one homework which is mandatory? Thanks D |
#8
|
|||
|
|||
Top N Records minus lower scores
Roger
Thanks for the great information on your website. I have printed off the Word doc and will definitely be referring to it in the future. Devon "Roger Carlson" wrote: On my website (www.rogersaccesslibrary.com), is a small Access database sample called "TopQuery.doc" which illustrates solutions to a number of problems with TOP queries including agregating, grouping, duplicates due to ties, and parameterizing the TOP value. It comes with both a document and sample database. You can download it for free he http://www.rogersaccesslibrary.com/f...ts.asp?TID=233 -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "Devon" wrote in message ... Hello I am a teacher and am trying to move my students grades from Excel to Access. I plan on using the Classroom management database from MS Office template, and then expanding upon that dbase as needed. One of the expansions, I am struggling with. During the semester, I will give out quizes and homeworks. I may give out 19 quizes, but I will only count 15 of those. The extra four can be dropped. In other words, the student will take all the quizes and then the lowest 4 will drop, so even though they will take 19 quizes, only 15 count toward their final grade. The homeworks will work much the same way, however, the 19th homework is mandatory. So the student will complete 19 homeworks, only the top 15 and the 19th will count toward their final grade. I have been struggling the past couple of days to get the SQL correct for this quiz. At present, I am using the sample data from the Classroom management database to get the SQL correct before entering it on my own database. For the data below, I have run SQL on several tables, as I believe that this would be necessary. Student ID Results ID Name Class Name Assignment Description Score 2 87 Alan Shen Great Works Quiz - Quiz #4 10 2 97 Alan Shen Great Works Quiz - Quiz #6 10 2 102 Alan Shen Great Works Quiz - Quiz #7 10 2 2 Alan Shen Great Works Quiz - Quiz #1 9 2 82 Alan Shen Great Works Quiz - Quiz #3 9 2 77 Alan Shen Great Works Quiz - Quiz #2 8 2 92 Alan Shen Great Works Quiz - Quiz #5 6 3 78 Conor C Great Works Quiz - Quiz #2 10 3 88 Conor C Great Works Quiz - Quiz #4 10 3 103 Conor C Great Works Quiz - Quiz #7 10 3 3 Conor C Great Works Quiz - Quiz #1 9 3 83 Conor C Great Works Quiz - Quiz #3 8 3 93 Conor C Great Works Quiz - Quiz #5 8 3 98 Conor C Great Works Quiz - Quiz #6 8 6 80 Erik A Great Works Quiz - Quiz #2 10 6 85 Erik A Great Works Quiz - Quiz #3 10 6 90 Erik A Great Works Quiz - Quiz #4 10 6 100 Erik A Great Works Quiz - Quiz #6 10 6 105 Erik A Great Works Quiz - Quiz #7 10 6 5 Erik A Great Works Quiz - Quiz #1 0 6 95 Erik A Great Works Quiz - Quiz #5 0 From this data above, I would like to run SQL that takes the top 5 (scaled down version of the real top 15) from each person in the class. So for Erik A, it would show 50 for a Score on Quizes. Below is some SQL for a subquery I have created after going through the discussion groups for other people with similar problems. However, it is still not quite working correctly. I know that Access will show ties in the data, and that appears to be what it is doing in my case. I have tried including the PK, but it still isn't quite right. SELECT A.Name, A.ClassName, A.AssignmentDescription, A.Score FROM Table1 AS A WHERE (((A.Score) In (SELECT Top 5 b.Score FROM Table1 as B WHERE b.StudentID = a.StudentID ORDER BY b.Score DESC))) ORDER BY A.StudentID, A.Score DESC; Any ideas for how to calculate the quizes? How about the homework with the one homework which is mandatory? Thanks D . |
#9
|
|||
|
|||
Top N Records minus lower scores
John
Thanks for the SQL. This worked great for the quizes. I have some additional SQL from Karl that solved the Homework issue. Thanks for the information. Devon "John Spencer" wrote: Assumption: Results ID is unique either within the table or by student. SELECT [StudentID], [ResultsID], [Name], [ClassName], [AssignmentDescription], [Score] FROM Table1 as A WHERE [Results ID] in (SELECT TOP 5 [Results ID] FROM Table1 as Temp WHERE Temp.StudentID = A.StudentID ORDER BY Temp.Score DESC, Temp.[Results ID]) The homework problem can also be solved but I don't have the time right now. Gotta go see the cardiologist for my checkup. Good luck John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Devon wrote: Hello I am a teacher and am trying to move my students grades from Excel to Access. I plan on using the Classroom management database from MS Office template, and then expanding upon that dbase as needed. One of the expansions, I am struggling with. During the semester, I will give out quizes and homeworks. I may give out 19 quizes, but I will only count 15 of those. The extra four can be dropped. In other words, the student will take all the quizes and then the lowest 4 will drop, so even though they will take 19 quizes, only 15 count toward their final grade. The homeworks will work much the same way, however, the 19th homework is mandatory. So the student will complete 19 homeworks, only the top 15 and the 19th will count toward their final grade. I have been struggling the past couple of days to get the SQL correct for this quiz. At present, I am using the sample data from the Classroom management database to get the SQL correct before entering it on my own database. For the data below, I have run SQL on several tables, as I believe that this would be necessary. Student ID Results ID Name Class Name Assignment Description Score 2 87 Alan Shen Great Works Quiz - Quiz #4 10 2 97 Alan Shen Great Works Quiz - Quiz #6 10 2 102 Alan Shen Great Works Quiz - Quiz #7 10 2 2 Alan Shen Great Works Quiz - Quiz #1 9 2 82 Alan Shen Great Works Quiz - Quiz #3 9 2 77 Alan Shen Great Works Quiz - Quiz #2 8 2 92 Alan Shen Great Works Quiz - Quiz #5 6 3 78 Conor C Great Works Quiz - Quiz #2 10 3 88 Conor C Great Works Quiz - Quiz #4 10 3 103 Conor C Great Works Quiz - Quiz #7 10 3 3 Conor C Great Works Quiz - Quiz #1 9 3 83 Conor C Great Works Quiz - Quiz #3 8 3 93 Conor C Great Works Quiz - Quiz #5 8 3 98 Conor C Great Works Quiz - Quiz #6 8 6 80 Erik A Great Works Quiz - Quiz #2 10 6 85 Erik A Great Works Quiz - Quiz #3 10 6 90 Erik A Great Works Quiz - Quiz #4 10 6 100 Erik A Great Works Quiz - Quiz #6 10 6 105 Erik A Great Works Quiz - Quiz #7 10 6 5 Erik A Great Works Quiz - Quiz #1 0 6 95 Erik A Great Works Quiz - Quiz #5 0 From this data above, I would like to run SQL that takes the top 5 (scaled down version of the real top 15) from each person in the class. So for Erik A, it would show 50 for a Score on Quizes. Below is some SQL for a subquery I have created after going through the discussion groups for other people with similar problems. However, it is still not quite working correctly. I know that Access will show ties in the data, and that appears to be what it is doing in my case. I have tried including the PK, but it still isn't quite right. SELECT A.Name, A.ClassName, A.AssignmentDescription, A.Score FROM Table1 AS A WHERE (((A.Score) In (SELECT Top 5 b.Score FROM Table1 as B WHERE b.StudentID = a.StudentID ORDER BY b.Score DESC))) ORDER BY A.StudentID, A.Score DESC; Any ideas for how to calculate the quizes? How about the homework with the one homework which is mandatory? Thanks D . |
Thread Tools | |
Display Modes | |
|
|