Show more relevant field

[Ray]

I need to create a report showing the candidate, class, score of judge 1, 2,
3, 4. Currently, I manage to obtain the result of candidate and score of
judge 1, 2, 3 and 4 but not the class. I would appreciate any suggestions to
accomplish this job.

I have a table containing the details of candidate, class, score of judge 1,
2, 3 and 4. Each record represents the score of each judge for each
candidate for a particular date. Each judge may have more than one score for
each candidate in different dates and need to show the latest one only.
Below are nested queries I currently use.

--------------------------------------------
First query to find out the latest scores of each judges for each candidate
from the table, tblScore.

SELECT tblScore.ClassID, tblScore.CandidateID, tblScore.JudgeID,
Max(tblScore.Updated) AS MaxOfUpdated
FROM qryJudge INNER JOIN (qryCandidate INNER JOIN tblScore ON
qryCandidate.CandidateID = tblScore.CandidateID) ON qryJudge.JudgeID =
tblScore.JudgeID
GROUP BY tblScore.ClassID, tblScore.CandidateID, tblScore.JudgeID;

----------------------------------------
Second query selects the relevant record if the candidate is active.


SELECT TqryScore0.CandidateID, TqryScore0.JudgeID, tblScore.Score,
TqryScore0.MaxOfUpdated, tblScore.Active
FROM TqryScore0 INNER JOIN tblScore ON (TqryScore0.ClassID =
tblScore.ClassID) AND (TqryScore0.CandidateID = tblScore.CandidateID) AND
(TqryScore0.MaxOfUpdated = tblScore.Updated) AND (TqryScore0.JudgeID =
tblScore.JudgeID)
WHERE (((tblScore.Active)=-1));

----------------------------------------------------------
Third query twists the result to the required format.

TRANSFORM First(TqryScore1.Score) AS [The Value]
SELECT TqryScore1.CandidateID
FROM TqryScore1
GROUP BY TqryScore1.CandidateID
PIVOT TqryScore1.JudgeID In ("1","2","3","4");