queryproblem ac2003

[Junior]

Hi
i've a problem to make a query as followed.
In a tabel(tblEvaluation) i've a field with the evaluationValue; Its a
numeriek field going from 1 to 5, which means 1 for awfull and going to 5
superb.
Each pupil has several evaluation, but its possible that one pupil has only
one evaluation and another maybe two or more evaluations.
In the tblEvaluation there is a corresponding field from the tblPupils
which contains the pupilsId
Every time the pupil has an evaluation, we shows his evaluation on the
mainform, but also we put the evaluationscore into the tblevaluation through
a sql instruction.
In the tblEvaluation we have all the evaluations op the pupils and here is
my question : How can i know how many pupils have more than one evaluation
and in how many cases the second evaluation is better or worser than the
first or previous evalualtion. ?
The first part of my question i can fix it but to the second part i really
do not known how to handle this .

many regards

 

[John Spencer]

To get a count of pupils with more than one evaluation you can use a query
like the following.

SELECT Count(PupilID) as TheCount
FROM
(SELECT PupilID
FROM tblEvalution
GROUP BY PupilID
HAVING Count(*) > 1)

To determine whether or not the last evaluation was better or worse than the
previous evaluation, you need some way to determine the order of the
evaluations (a date field for the evaluation would be one way to determine this).

Also, if a pupil should have (for example) 4 evaluations, do you compare the
4th evaluation to the 3rd evaluation or to the 2nd or the first?


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County