Summing Top 4 Scores 1

[TCARPENTER]

I have two tables, one with the school name and the other with the events, athletes, and their scores. The top 4 scores are the ones that count towards the total.

What's wrong with my query - it's not returning correct number:

SELECT Schools.Name, Sum(Scores.Bars) AS TopOfBars, Sum(Scores.Beam) AS TopOfBeam, Sum(Scores.Floor) AS TopOfFloor, Sum(Scores.Vault) AS TopOfVault, [TopOfBars]+[TopOfBeam]+[TopOfFloor]+[TopOfVault] AS Totals
FROM Schools INNER JOIN Scores ON Schools.ID = Scores.School
WHERE (((Scores.Bars) In (SELECT TOP 4 Bars from Scores WHERE Id = Scores.School ORDER BY Bars DESC)) AND ((Scores.Beam) In (SELECT TOP 4 Beam from Scores WHERE Id = Scores.School ORDER BY Beam DESC)) AND ((Scores.Floor) In (SELECT TOP 4 Floor from Scores WHERE Id = Scores.School ORDER BY Floor DESC)) AND ((Scores.Vault) In (SELECT TOP 4 Vault from Scores WHERE Id = Scores.School ORDER BY Vault DESC)))
GROUP BY Schools.Name
ORDER BY Schools.Name;

Bars
7.1
0
6.325
5.475
0
6.425

These values should return 25. but it's returning 11.9 - I thought TOP returned the highest values...?

Thanks for any help.

 

[dhookom]

I think you can do these only with a single score like bars. Have you considered normalizing your table structure?

Can we get some context? Are you looking for the top 4 scores from each school for each event?

Duane
Hook'D on Access
MS Access MVP

 

[TCARPENTER]

Hi Duane,

The database could probably be better normalized, it's fairly straight forward, so I didn't think it needed much more but any advice would be welcomed.

I've attached the database - I think that's the easiest way to get you some content. And to answer you're other question, yes we need to get the top 4 scores for each event, for each school, and then total those top 4 scores. Don't know if it matters or helps, but each gymnast will place (each meet can choose how low to go) individually...

Thanks for the help.

 

[dhookom]

I would start by normalizing your scores table in a union query [quniScores] like:

SELECT ScoresID, School, GymnastID, Gymnast, "Bars" AS Event, Bars AS Score
FROM Scores
WHERE Bars>0
UNION ALL
SELECT ScoresID, School, GymnastID, Gymnast, "Beam", Beam
FROM Scores
WHERE Beam>0
UNION ALL
SELECT ScoresID, School, GymnastID, Gymnast, "Floor", Floor
FROM Scores
WHERE Floor>0
UNION ALL
SELECT ScoresID, School, GymnastID, Gymnast, "Vault", Vault
FROM Scores
WHERE Vault>0;

Then create a top 4 query [qrnkTop4BySchoolEvent] with SQL of:

SELECT quniScores.Event, quniScores.ScoresID, quniScores.School, quniScores.GymnastID, quniScores.Gymnast, quniScores.Score, quniScores.ScoresID
FROM quniScores
WHERE (((quniScores.ScoresID) In (SELECT TOP 4 ScoresID from quniScores Q where Q.School = [quniScores].[School] AND q.Event = [quniScores].Event ORDER BY q.Score DESC, q.ScoresID)))
ORDER BY quniScores.Event, quniScores.School, quniScores.Score DESC;

You can also get the school totals in [qgrpSchoolTotal] with SQL of:

SELECT quniScores.School, Sum(quniScores.Score) AS SumOfScore
FROM quniScores
WHERE (((quniScores.ScoresID) In (SELECT TOP 4 ScoresID from quniScores Q where Q.School = [quniScores].[School] AND q.Event = [quniScores].Event ORDER BY q.Score DESC, q.ScoresID)))
GROUP BY quniScores.School
ORDER BY quniScores.School;

Duane
Hook'D on Access
MS Access MVP

 

[TCARPENTER]

Thanks Duane! It never occurred to me to use an initial query to normalize the table - you can tell I'm still a rookie...

Thanks again - and have a star!