[AccessD] Subquery Theory Part 3

Now, this also allows for a join to use calculations too.  Let's normalize
our test system.  Instead of tblStudentGrades, let's create three tables:

 

tblStudents:

StudentID    StudentName

1                Bob

2                Drew

3                Kathy

4                Chris

5                Donald

 

tblTests:

TestID    TestName    TestDate    AvailablePoints

1            Math Test    12/12/2006        40

 

tblTestResults:

TestID    StudentID    PointsReceived

1            1                36

1            2                35

1            3                26

1            4                15

1            5                29

 

We already have tblGradeDetails, but now, to get the grade, to match with
tblGradeDetails, we have to calculate the grade from PointsReceived (in
tblTestResults) and AvailablePoints (in tblTests).

 

Pretty simple really, first we join tblStudents and tblTestResults on
StudentID (in the FROM statement) and also tblTests with tblTestResults (in
the FROM statement), then we just add tblGradeDetails (and join it with a
subquery in the WHERE statement)

 

SELECT TestName, StudentName, ((PointsReceived/AvailablePoints)*100) AS
GradePercentage, GradeLetter, GPA, BonusPoints

FROM tblStudents INNER JOIN (tblTests AS T1 INNER JOIN tblTestResults AS T2
ON T1.TestID =T2.TestID) ON tblStudents.StudentID = T2.StudentID,
tblGradeDetails

WHERE TopGrade=(SELECT MIN(TopGrade) FROM tblGradeDetails WHERE
TopGrade>=((T2.PointsReceived/T1.AvailablePoints)*100))

 

And we get the following:

 

TestName    StudentName    GradePercentage    GradeLetter    GPA
BonusPoints

Math Test    Bob                            90                        A
4                20

Math Test    Drew                          87.5                      B
3                10

Math Test    Kathy                         65                         D
1                0

Math Test    Chris                          37.5                      F
0                 0

Math Test    Donald                        72.5                     C
2                 5

 

Note how we aliased two of the four tables in the FROM statement (tblTests
as T1 and tblTestResults as T2).  Then we simply added tblGradeDetails with
a comma.  I'm sure many use the query builder in access.  Access' query
builder will let you do exactly what we just did.  Just add all four tables
to a query.  Join tblStudents, tblTests and tblTestResults on the
appropriate fields.  Leave tblGradeDetails unjoined.  Now right click on
tblTests and go to properties.  One of the properties is Alias, change it
from tblTests to T1.  Do the same for tblTestResults (but alias it as T2).
Now, add the fields, TestName, StudentName, GradePercentage:
((PointsReceived/AvailablePoints)*100), GradeLetter, GPA, BonusPoints
TopGrade.  In the Criteria beneath TopGrade, enter the subquery: (SELECT
MIN(TopGrade) FROM tblGradeDetails WHERE
TopGrade>=((T2.PointsReceived/T1.AvailablePoints)*100))

 

Whalla.  A little tip, when building a subquery in the query builder, use
the Zoom option, easier to see the whole thing.  If you are building the SQL
of a subquery using another instance of the SQL Builder, just be sure to
change the tablenames that are aliased in the primary query, when you paste
it into your main query.

 

Hope this helps someone somewhere, I'm heading home!

 

Drew