Stymied by SQL

[Guest]

Hello to all

Here is my situation. Several hundred Students are each given a Score.
Students each belong to one of 19 Classes. Classes compete in one of several
Divisions. On my form, I select a Division (say, Cardinals) from cbxDiv and
a number (say, 5) from cbxTOPn. The form then shows ALL 19 Classes ordered
by the sum of their TOP 5 scores, in descending order. Here's the code,
which works perfectly.

Const strcA = "SELECT Q1.ClassID, Q1.ClassName, Sum(Q1.Score) AS Total FROM
[qryStudent] AS Q1 WHERE (Q1.StudentID In (SELECT Top "

Const strcB = " Q2.StudentID FROM [qryStudent] As Q2 WHERE ((Q2.ClassID =
Q1.ClassID) AND (DivID = "

Const strcC = " )) ORDER BY Score DESC, StudentID)) GROUP BY Q1.ClassID,
Q1.ClassName ORDER BY Sum(Q1.Score) DESC, Q1.ClassName;"

strSQL = strcA & cbxTOPn & strcB & cbxDiv & strcC

Forms!frmTOPn.RecordSource = strSQL


Now my question (2 actually)
a) I want the form to do exactly what it is doing, but show the TOP 3
Classes only, not all 19. This should be easy, but I can't seem to get the
syntax right.

b) I think this is tougher, but any hints would be REALLY appreciated.
(All this SQL is starting to look the same!) I want to make a report that
will repeat the above for EACH Division. In other words, Division Cardinals
would show the TOP 3 Classes based on the sum of their best 5 Scores, then
Division Jays would show the TOP 3 Classes based on the sum of their best 5
Scores, then... etc. Somehow I need to loop through each Division, but I'm
insecure enough in my coding to have success yet. I could really use some
direction here.

Thank you in advance

 

[Guest]

Hello to all

Here is my situation. Several hundred Students are each given a Score.
Students each belong to one of 19 Classes. Classes compete in one of several
Divisions. On my form, I select a Division (say, Cardinals) from cbxDiv and
a number (say, 5) from cbxTOPn. The form then shows ALL 19 Classes ordered
by the sum of their TOP 5 scores, in descending order. Here's the code,
which works perfectly.

Const strcA = "SELECT Q1.ClassID, Q1.ClassName, Sum(Q1.Score) AS Total FROM
[qryStudent] AS Q1 WHERE (Q1.StudentID In (SELECT Top "

Const strcB = " Q2.StudentID FROM [qryStudent] As Q2 WHERE ((Q2.ClassID =
Q1.ClassID) AND (DivID = "

Const strcC = " )) ORDER BY Score DESC, StudentID)) GROUP BY Q1.ClassID,
Q1.ClassName ORDER BY Sum(Q1.Score) DESC, Q1.ClassName;"

strSQL = strcA & cbxTOPn & strcB & cbxDiv & strcC

Forms!frmTOPn.RecordSource = strSQL


Now my question (2 actually)
a) I want the form to do exactly what it is doing, but show the TOP 3
Classes only, not all 19. This should be easy, but I can't seem to get the
syntax right.

b) I think this is tougher, but any hints would be REALLY appreciated.
(All this SQL is starting to look the same!) I want to make a report that
will repeat the above for EACH Division. In other words, Division Cardinals
would show the TOP 3 Classes based on the sum of their best 5 Scores, then
Division Jays would show the TOP 3 Classes based on the sum of their best 5
Scores, then... etc. Somehow I need to loop through each Division, but I'm
insecure enough in my coding to have success yet. I could really use some
direction here.

Thank you in advance

Hi Sophie,
1) If your resultset is showing 19 records, you could use SELECT TOP 3
instead of SELECT at the beginning of your SQL code.

2) In your code, you can loop through each Division in your Divisions table
and store each one as a variable, lets call it strDiv. Instead of your strSQL
string reading:
strSQL = strcA & cbxTOPn & strcB & cbxDiv & strcC

it will then read:
strSQL = strcA & cbxTOPn & strcB & strDiv & strcC

If you're unsure about looping in code, look up the Do Statement in Access
help for some great advice.
Dave