Max in Query Criteria

[mba_110]

it is giving syntax error now i have 3 queries including the result query.

1. QryEmpEvaluation
2. QryEmpEvaluationSub
3. QryEmpEvaluationResult (Below code is mentioned in this query)

SELECT tblEvaluation.EvaluationID, QryEmpEvaluation.EmployeeNo, QryEmpEvaluationSub.LatestEvaluationYear, Sum(tblEvaluationPerformance.Score) AS TotalPoints
FROM QryEmpEvaluation INNER JOIN ((tblEvaluation INNER JOIN tblEvaluationCategory ON tblEvaluation.EvaluationID = tblEvaluationCategory.EvaluationID) INNER JOIN tblEvaluationPerformance ON tblEvaluationCategory.EP_ID = tblEvaluationPerformance.EP_ID) ON (QryEmpEvaluationSub.EmployeeNo = tblEvaluation.EmployeeNo) AND (QryEmpEvaluationSub.LatestEvaluationYear = tblEvaluation.EvalYear)
GROUP BY tblEvaluation.EvaluationID, QryEmpEvaluationSub.EmployeeNo, QryEmpEvaluationSub.LatestEvaluationYear;

As you said to make a sub query i have the following remember their is no EmpID and i replace it with EmployeeNo.

2. This is mentioned in QryEmpEvaluationSub

SELECT tblEvaluation.EmployeeNo, Max(tblEvaluation.EvalYear) AS LatestEvaluationYear
FROM tblEvaluation
GROUP BY tblEvaluation.EmployeeNo;

1. This is mentioned in QryEmpEvaluation

SELECT tblEvaluation.EmployeeNo, tblEvaluation.EvalYear, tblEvaluationPerformance.Score
FROM tblEvaluation INNER JOIN (tblEvaluationPerformance INNER JOIN tblEvaluationCategory ON tblEvaluationPerformance.EP_ID = tblEvaluationCategory.EP_ID) ON tblEvaluation.EvaluationID = tblEvaluationCategory.EvaluationID;

 

[plog]

QryEmpEvaluation should be this:

SELECT tblEvaluation.EvaluationID, QryEmpEvaluationSub.EmployeeNo, QryEmpEvaluationSub.LatestEvaluationYear, Sum(tblEvaluationPerformance.Score) AS SumOfScore
FROM ((QryEmpEvaluationSub INNER JOIN tblEvaluation ON (QryEmpEvaluationSub.LatestEvaluationYear = tblEvaluation.EvalYear) AND (QryEmpEvaluationSub.EmployeeNo = tblEvaluation.EmployeeNo)) INNER JOIN tblEvaluationCategory ON tblEvaluation.EvaluationID = tblEvaluationCategory.EvaluationID) INNER JOIN tblEvaluationPerformance ON tblEvaluationCategory.EP_ID = tblEvaluationPerformance.EP_ID
GROUP BY tblEvaluation.EvaluationID, QryEmpEvaluationSub.EmployeeNo, QryEmpEvaluationSub.LatestEvaluationYear;

I don't know what the purpose of QryEmpEvaluationResult is.