Min not working

[Tami]

I'd like the query to drop the lowest of the four scores and add the
highest three scores giving me a final score (TheScore). I grouped on
several
fields. Is this confusing it so that it doesn't know that each student is
it's own group with four scores? It appears to be giving me a total on all of
the students together.

Project No connects the Tables together. Can a query compare all records
with the same Project No. to find the lowest score and add just the three
highest scores?


StudentScores is my 2nd Table with ScoreID
ScienceFair is my 1st Table with STUDENTsfID

This is what I'm getting in my query and the program follows:

LastName FirstName ProjectNo Score TheScore
DelSignore Joslyn 4 75 0
DelSignore Joslyn 4 85 0
DelSignore Joslyn 4 90 0
DelSignore Joslyn 4 100 0
Doherty Elyse 2 50 0
Doherty Elyse 2 80 0
Doherty Elyse 2 87 0
Doherty Elyse 2 99 0
Elia Victoria 1 75 0
Elia Victoria 1 80 0
Elia Victoria 1 90 0
Elia Victoria 1 98 0
Shore Robert 3 85 0
Shore Robert 3 90 0
Shore Robert 3 100 100



SELECT ScienceFair.LastName, ScienceFair.FirstName, StudentScores.ProjectNo,
StudentScores.Score, Sum(Score)-Min(Score) AS TheScore
FROM ScienceFair INNER JOIN StudentScores ON ScienceFair.ProjectNo =
StudentScores.ProjectNo
GROUP BY ScienceFair.LastName, ScienceFair.FirstName,
StudentScores.ProjectNo, StudentScores.Score;

 

[Lou]

I'd like the query to drop the lowest of the four scores and add the
highest three scores giving me a final score  (TheScore).   I groupedon
several  
fields.  Is this confusing it so that it doesn't know that each studentis
it's own group with four scores? It appears to be giving me a total on all of
the students together.  

 Project No connects the Tables together.  Can a query compare all records
with the same Project No. to find the lowest score and add just the three
highest scores?

StudentScores is my 2nd Table with ScoreID
ScienceFair is my 1st Table with STUDENTsfID

This is what I'm getting in my query and the program follows:

LastName FirstName ProjectNo Score TheScore
DelSignore Joslyn     4      75      0
DelSignore Joslyn     4      85      0
DelSignore Joslyn     4      90      0
DelSignore Joslyn     4     100     0
Doherty Elyse           2       50     0
Doherty Elyse           2       80     0
Doherty Elyse           2       87     0
Doherty Elyse           2       99     0
Elia Victoria              1       75     0
Elia Victoria              1       80     0
Elia Victoria              1       90     0
Elia Victoria              1       98     0
Shore Robert            3       85     0
Shore Robert            3       90     0
Shore Robert            3      100    100

SELECT ScienceFair.LastName, ScienceFair.FirstName, StudentScores.ProjectNo,
StudentScores.Score, Sum(Score)-Min(Score) AS TheScore
FROM ScienceFair INNER JOIN StudentScores ON ScienceFair.ProjectNo =
StudentScores.ProjectNo
GROUP BY ScienceFair.LastName, ScienceFair.FirstName,
StudentScores.ProjectNo, StudentScores.Score;

It appears that we want the top three scores. Shore Robert has
exactly three scores. We must exclude the lowest score for the
students who have four scores.

Therefore, we must project a list of CountableScores and SUM those
scores based on ProjectNumber.

Finally, we must join the summary rows of project scores to the
student names.

SELECT ScienceFair.LastName, ScienceFair.FirstName,
ScienceFair.ProjectNo, ProjectScores.SumOfScores
from ScienceFair INNER JOIN
(
SELECT CountableScores.ProjectNo, Sum( CountableScores.Score )
(
SELECT A.ProjectNo, A.Score
from StudentScores as A
where A.Score > ( SELECT min( Score )
from StudentScores
where ProjectNo = A.ProjectNo )
UNION ALL
SELECT A.ProjectNo, A.Score
from StudentScores as A
where 4 > ( SELECT count(*)
from StudentScores
where ProjectNo = A.ProjectNo )
) as CountableScores
GROUP BY CountableScores.ProjectNo
) as ProjectScores
ON ScienceFair.ProjectNo = ProjectScores.ProjectNo

 

[John Spencer]

Remove the StudentScores.Score field from the query

SELECT ScienceFair.LastName
, ScienceFair.FirstName
, StudentScores.ProjectNo
, Sum(Score)-Min(Score) AS TheScore
FROM ScienceFair INNER JOIN StudentScores
ON ScienceFair.ProjectNo = StudentScores.ProjectNo
GROUP BY ScienceFair.LastName, ScienceFair.FirstName,
StudentScores.ProjectNo;

Returns:
DelSignore Joslyn 4 275
Doherty Elyse 2 266
....

If you need all the individual scores shown in individual records along with
the overall score in each individual record then you can use a correlated
subquery to show the calculated score.

SELECT ScienceFair.LastName
, ScienceFair.FirstName
, StudentScores.ProjectNo
, StudentScores.Score
, (SELECT Sum(Score)-Min(Score) AS TheScore
FROM StudentScores as Temp
WHERE Temp.ProjectNo = StudentScores.ProjectNo) as TheScore
FROM ScienceFair INNER JOIN StudentScores
ON ScienceFair.ProjectNo = StudentScores.ProjectNo

Returns:
DelSignore Joslyn 4 75 275
DelSignore Joslyn 4 85 275
DelSignore Joslyn 4 90 275
DelSignore Joslyn 4 100 275
Doherty Elyse 2 50 266
Doherty Elyse 2 80 266
....
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County