consecutive results

[mudman]

im doin a2 ict and the coursework is an ict assessment system, students take certain amount of tests, their info is split into 2 different tables:

tblStudent: (PupilID, FirstName, SecondName, TMG, Year, Form, SetRef)
tblAchievement: (PupilRef, TestID, Grade)

in student table is their tmg i need to bring up results for students who are getting higher than that grade in more than two tests.
anyone know how?

if ya need any more info pls email me, any help gratefully received.

 

[FoFa]

If I understand correctly:
SELECT FirstName, SecondName
FROM tblStudent
INNER JOIN tblAchievement on PupilRef = PupilID
WHERE TMG > Grade
GROUP BY PupilID, FirstName, SecondName
HAVING count(PupileID) > 1

Basically that should do it, you may have to play around with it a little.

 

[mudman]

i think i get what your saying how to do this,
but the count of pupil id is coming up as one for all of the results,

 

[FoFa]

Oopsie, I meant
HAVING count(PupileRef) > 1

 

[mudman]

got it,

i got what i was doin wrong,

i already had a query showing students that were getting higher than tmg, i just based another query on this query just on the fields pupil ID firstname and surname, with the count function for pupil ID and the critera >2
that did the job.

thanks alot dude, u saved me alot of hassle.