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

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

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

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

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

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

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

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




.

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


.