SQL assistance needed

[Sophie]

I help organize my school region's math competitions. I've been struggling
with some SQL for days, and would really appreciate some clear assistance
(for reluctant programmers!).

My form currently has two textboxes with Control Sources Total and
TeamName respectively. When I use the following SQL as the Form's Record
Source, everything works fine, giving me a result like... 155.6 Math
Marvels

SELECT TOP 1 Q1.TeamID, Q1.TeamName, Sum(Q1.Score) AS Total FROM
[qryStudent] AS Q1 WHERE (Q1.StudentID In ((SELECT Top 3 Q2.StudentID FROM
[qryStudent] As Q2 WHERE (Q2.TeamID = Q1.TeamID) ORDER BY Score DESC,
StudentID))) GROUP BY Q1.TeamID, Q1.TeamName ORDER BY Sum(Q1.Score) DESC,
Q1.TeamName;


But, what I really am trying to do is a bit different. I want my form to
show the Team winner based on the top 3 scores, then the Team winner based on
the top 4 scores, ... up to the Team winner based on the top 6 scores.
Something like...

Winner, top 3 scores 155.6 Math Marvels
Winner, top 4 scores 200.4 Math Marvels
Winner, top 5 scores 247.1 Group Groupies
Winner, top 6 scores 155.6 Calculus Canines

(we do this so that the organizers can have some flexibility with team
awards depending on the number of 'mathematicians' competing.)

I would really appreciate some help in setting this up. I can't figure out
where the SQL should go, or how the form should be set up, or how to get it
to loop through TOP 3 to TOP 6. I also need to produce the exact same info
in a report, but I imagine this will be handled the same as the form.

 

[Bob Hairgrove]

I help organize my school region's math competitions. I've been struggling
with some SQL for days, and would really appreciate some clear assistance
(for reluctant programmers!).

My form currently has two textboxes with Control Sources Total and
TeamName respectively. When I use the following SQL as the Form's Record
Source, everything works fine, giving me a result like... 155.6 Math
Marvels

SELECT TOP 1 Q1.TeamID, Q1.TeamName, Sum(Q1.Score) AS Total FROM
[qryStudent] AS Q1 WHERE (Q1.StudentID In ((SELECT Top 3 Q2.StudentID FROM
[qryStudent] As Q2 WHERE (Q2.TeamID = Q1.TeamID) ORDER BY Score DESC,
StudentID))) GROUP BY Q1.TeamID, Q1.TeamName ORDER BY Sum(Q1.Score) DESC,
Q1.TeamName;

First of all,

(1) It would help to know what each table structure looks like.
(2) Also, what SQL statement is used for qryStudent?
(3) Including SELECT TOP 1 will not show tied scores; at least I don't think it
would because you will have a different TeamName for each score.

But, what I really am trying to do is a bit different. I want my form to
show the Team winner based on the top 3 scores, then the Team winner based on
the top 4 scores, ... up to the Team winner based on the top 6 scores.
Something like...

Winner, top 3 scores 155.6 Math Marvels
Winner, top 4 scores 200.4 Math Marvels
Winner, top 5 scores 247.1 Group Groupies
Winner, top 6 scores 155.6 Calculus Canines

(we do this so that the organizers can have some flexibility with team
awards depending on the number of 'mathematicians' competing.)

I'm not sure I understand how the scores relate to the names in the rightmost
column, nor why there should be a different winner depending on how many scores
are used ... after all, the topmost score will always win, will it not? If you
could expand upon the example given here a bit, that would be great.

I would really appreciate some help in setting this up. I can't figure out
where the SQL should go, or how the form should be set up, or how to getit
to loop through TOP 3 to TOP 6. I also need to produce the exact same info
in a report, but I imagine this will be handled the same as the form.

Reports do have some very useful tricks which forms don't have, especially when
you are doing a self-join ... for example, you can hide duplicate items and
present query results based on adjacency lists (these are commonly implemented
with a self-join) in a more orderly fashion because the uppermost hierarchies
are only printed just once, even if running the query by itself shows duplicates
in the leftmost columns for every row.

 

[Marshall Barton]

I help organize my school region's math competitions. I've been struggling
with some SQL for days, and would really appreciate some clear assistance
(for reluctant programmers!).

My form currently has two textboxes with Control Sources Total and
TeamName respectively. When I use the following SQL as the Form's Record
Source, everything works fine, giving me a result like... 155.6 Math
Marvels

SELECT TOP 1 Q1.TeamID, Q1.TeamName, Sum(Q1.Score) AS Total FROM
[qryStudent] AS Q1 WHERE (Q1.StudentID In ((SELECT Top 3 Q2.StudentID FROM
[qryStudent] As Q2 WHERE (Q2.TeamID = Q1.TeamID) ORDER BY Score DESC,
StudentID))) GROUP BY Q1.TeamID, Q1.TeamName ORDER BY Sum(Q1.Score) DESC,
Q1.TeamName;


But, what I really am trying to do is a bit different. I want my form to
show the Team winner based on the top 3 scores, then the Team winner based on
the top 4 scores, ... up to the Team winner based on the top 6 scores.
Something like...

Winner, top 3 scores 155.6 Math Marvels
Winner, top 4 scores 200.4 Math Marvels
Winner, top 5 scores 247.1 Group Groupies
Winner, top 6 scores 155.6 Calculus Canines

(we do this so that the organizers can have some flexibility with team
awards depending on the number of 'mathematicians' competing.)

I would really appreciate some help in setting this up. I can't figure out
where the SQL should go, or how the form should be set up, or how to get it
to loop through TOP 3 to TOP 6. I also need to produce the exact same info
in a report, but I imagine this will be handled the same as the form.

That looks like 4 separate queries because the conditions
are completely different. You can then combine those
results for the form's record source:

SELECT 3 As TopNum, * FROM qryTop3
UNION ALL
SELECT 4, * FROM qryTop4
UNION ALL
SELECT 5, * FROM qryTop5
UNION ALL
SELECT 6, * FROM qryTop6
ORDER BY 1

 

[Sophie]

Excellent! This worked on the 1st try. I was getting close, but my code was
getting quite awkward and difficult to follow. Your suggestion was very
clear and will also be easy to modify for related things I'm attempting.
Thanks
Sophie

I help organize my school region's math competitions. I've been struggling
with some SQL for days, and would really appreciate some clear assistance
(for reluctant programmers!).

My form currently has two textboxes with Control Sources Total and
TeamName respectively. When I use the following SQL as the Form's Record
Source, everything works fine, giving me a result like... 155.6 Math
Marvels

SELECT TOP 1 Q1.TeamID, Q1.TeamName, Sum(Q1.Score) AS Total FROM
[qryStudent] AS Q1 WHERE (Q1.StudentID In ((SELECT Top 3 Q2.StudentID FROM
[qryStudent] As Q2 WHERE (Q2.TeamID = Q1.TeamID) ORDER BY Score DESC,
StudentID))) GROUP BY Q1.TeamID, Q1.TeamName ORDER BY Sum(Q1.Score) DESC,
Q1.TeamName;


But, what I really am trying to do is a bit different. I want my form to
show the Team winner based on the top 3 scores, then the Team winner based on
the top 4 scores, ... up to the Team winner based on the top 6 scores.
Something like...

Winner, top 3 scores 155.6 Math Marvels
Winner, top 4 scores 200.4 Math Marvels
Winner, top 5 scores 247.1 Group Groupies
Winner, top 6 scores 155.6 Calculus Canines

(we do this so that the organizers can have some flexibility with team
awards depending on the number of 'mathematicians' competing.)

I would really appreciate some help in setting this up. I can't figure out
where the SQL should go, or how the form should be set up, or how to get it
to loop through TOP 3 to TOP 6. I also need to produce the exact same info
in a report, but I imagine this will be handled the same as the form.

That looks like 4 separate queries because the conditions
are completely different. You can then combine those
results for the form's record source:

SELECT 3 As TopNum, * FROM qryTop3
UNION ALL
SELECT 4, * FROM qryTop4
UNION ALL
SELECT 5, * FROM qryTop5
UNION ALL
SELECT 6, * FROM qryTop6
ORDER BY 1

 

[Marshall Barton]

Hey, that's terrific. I love it when it works first try,
especially when my explanation is understandable ;-)
--
Marsh
MVP [MS Access]

Excellent! This worked on the 1st try. I was getting close, but my code was
getting quite awkward and difficult to follow. Your suggestion was very
clear and will also be easy to modify for related things I'm attempting.
Thanks
Sophie

I help organize my school region's math competitions. I've been struggling
with some SQL for days, and would really appreciate some clear assistance
(for reluctant programmers!).

My form currently has two textboxes with Control Sources Total and
TeamName respectively. When I use the following SQL as the Form's Record
Source, everything works fine, giving me a result like... 155.6 Math
Marvels

SELECT TOP 1 Q1.TeamID, Q1.TeamName, Sum(Q1.Score) AS Total FROM
[qryStudent] AS Q1 WHERE (Q1.StudentID In ((SELECT Top 3 Q2.StudentID FROM
[qryStudent] As Q2 WHERE (Q2.TeamID = Q1.TeamID) ORDER BY Score DESC,
StudentID))) GROUP BY Q1.TeamID, Q1.TeamName ORDER BY Sum(Q1.Score) DESC,
Q1.TeamName;


But, what I really am trying to do is a bit different. I want my form to
show the Team winner based on the top 3 scores, then the Team winner based on
the top 4 scores, ... up to the Team winner based on the top 6 scores.
Something like...

Winner, top 3 scores 155.6 Math Marvels
Winner, top 4 scores 200.4 Math Marvels
Winner, top 5 scores 247.1 Group Groupies
Winner, top 6 scores 155.6 Calculus Canines

(we do this so that the organizers can have some flexibility with team
awards depending on the number of 'mathematicians' competing.)

I would really appreciate some help in setting this up. I can't figure out
where the SQL should go, or how the form should be set up, or how to get it
to loop through TOP 3 to TOP 6. I also need to produce the exact same info
in a report, but I imagine this will be handled the same as the form.

That looks like 4 separate queries because the conditions
are completely different. You can then combine those
results for the form's record source:

SELECT 3 As TopNum, * FROM qryTop3
UNION ALL
SELECT 4, * FROM qryTop4
UNION ALL
SELECT 5, * FROM qryTop5
UNION ALL
SELECT 6, * FROM qryTop6
ORDER BY 1