looping over query 2 consecutive diff date?

Hello everyone,

I'm very new to CF and I have to do some CF programming for work and I'm not quite sure how to do this one problem.

I have 2 tables,
+ tblExams {ExamID, ClassID, ExamName, Date, FullScore, ExtraCredit, Week }
+ tblClassExams { ClassExamID, StudentID, ExamID, Score, Grade }

I'm trying to query out all the students that have 2 consecutive exam grades but from different dates that are under 70, so if a student gets 2 scores under 70 on the same date, it would only count once. If they have 60, 71 on consecutive dates then they're ok. So far I've gotten the query to be:

SELECT * FROM tblClassExams INNER JOIN tblExams ON tblExams.examid = tblClassExams.examid WHERE grade < 70 and [DATE] > #10/1/04# ORDER BY StudentID ASC

I'm not sure how I could loop over the result and extract out the information I needed, I've tried searching this forum but no luck so far. Any tips is greatly appreciated. Thanks..

We're running on CF 5 with Access.
Johnny

Well I don't see how that query is going to give you back 2 consecutive scores under 70 per student. It looks like the query is just bringing back all records for studies that ever got less than a 70 after a certain date.

If that's what you want, you could then use cfquery to loop over the result set, grouping on studentID, and try to determine if for any studentID there are consecutive records.

I still don't see how you're going to know if the failures are "consecutive" though...the query is just pulling out all the records below 70. How would you know that the student didn't fail one test, then pass one (that isn't in the result set because you're specifying less than 70), and then fail another one later?

To be honest, I think you might be biting off more than you can chew with this type of challenge if you are really new to CF. It would be better to get a handle on the basics first and build up to something like this.

Hi Kiteless

I didn't say that this query will do that that's just how far i've gotten, I dont know how to loop over it and retrieve out the correct result. CF isn't my choice, I've just started this job and the current registrar system is running on CF from the previous IT guy that was there. So now I'm inheriting all the overdue projects, requested features and he's only got a chance to show me some of the basic cf tags and left. Sad huh? hehe

Johnny

If CF isn't your choice, how would you do this with another language (whichever one is your choice)?