Finding next value in table

[Stewart Allen]

I'm trying to find the next value in a table that a student needs to achieve
once he has already passed one grade.

*tblStudents*
StudentID (PK)
FirstName

*tblGradingDate s*
DateID (PK)
GradeDate

*tblGrades*
GradeID (PK)
Grade
Requirements

*tblGradingDeta ils*
DateID (FK) M -1 tblGradingDates
StudentID (FK) M -1 tblStudents
GradeID (FK) M -1 tblGrades
Cost

One student can go for many grades but only one grade at a time. One grading
can have many students going for a grade. What I'm after is to find what the
next Grade(ID) and requirements are for a student. The requirements are what
the student has to do before he can go for that grade.

I can find a student's current grade (StudentID =1) by using:
SELECT Last(tblGrades. Grade) AS LastGrade
FROM tblGrade INNER JOIN tblGradingDetai ls
ON tblGrades.Grade ID = tblGradingDetai ls.GradeID
WHERE (((tblGradingDe tails.StudentID )=1));

Is there any SQL statement that I can use that will find from tblGrades what
the next grade and requirements for that grade are for a student? Looping
through a recordset using DAO is something I'd do as the last resort. The
GradeID's are in numeric order.

TIA
Stewart

[MacDermott]

If you have the student's current grade, you could get the next grade like
this:
DMin("GradeID", "tblGrades","Gr adeID>" & CurrentGrade)
You could then use DLookup to get the requirements.

You can use the same logic in a query, too.
Or you could use something like this:
"SELECT TOP 1 GradeID,Require ments from tblGrades WHERE GradeID>" &
CurrentGrade & " ORDER BY GradeID"

HTH
- Turtle
"Stewart Allen" <sa****@ThisPar tNotVailid.wave .co.nz> wrote in message
news:bp******** **@news.wave.co .nz...

I'm trying to find the next value in a table that a student needs to achieve once he has already passed one grade.

*tblStudents*
StudentID (PK)
FirstName

*tblGradingDate s*
DateID (PK)
GradeDate

*tblGrades*
GradeID (PK)
Grade
Requirements

*tblGradingDeta ils*
DateID (FK) M -1 tblGradingDates
StudentID (FK) M -1 tblStudents
GradeID (FK) M -1 tblGrades
Cost

One student can go for many grades but only one grade at a time. One grading can have many students going for a grade. What I'm after is to find what the next Grade(ID) and requirements are for a student. The requirements are what the student has to do before he can go for that grade.

I can find a student's current grade (StudentID =1) by using:
SELECT Last(tblGrades. Grade) AS LastGrade
FROM tblGrade INNER JOIN tblGradingDetai ls
ON tblGrades.Grade ID = tblGradingDetai ls.GradeID
WHERE (((tblGradingDe tails.StudentID )=1));

Is there any SQL statement that I can use that will find from tblGrades what the next grade and requirements for that grade are for a student? Looping
through a recordset using DAO is something I'd do as the last resort. The
GradeID's are in numeric order.

TIA
Stewart

[MeadeR]

I would add one additional record to Grades (one more then the max grade that
can be acheived - something like - you're at the max type of grade)

In the select just add one to GradeID (since you state they're in numeric order)
ON tblGrades.Grade ID = tblGradingDetai ls.GradeID + 1

AND never allow a person to get more then the max acceptable grade......
"Stewart Allen" <sa****@ThisPar tNotVailid.wave .co.nz> wrote in message news:<bp******* ***@news.wave.c o.nz>...

I'm trying to find the next value in a table that a student needs to achieve
once he has already passed one grade.

*tblStudents*
StudentID (PK)
FirstName

*tblGradingDate s*
DateID (PK)
GradeDate

*tblGrades*
GradeID (PK)
Grade
Requirements

*tblGradingDeta ils*
DateID (FK) M -1 tblGradingDates
StudentID (FK) M -1 tblStudents
GradeID (FK) M -1 tblGrades
Cost

One student can go for many grades but only one grade at a time. One grading
can have many students going for a grade. What I'm after is to find what the
next Grade(ID) and requirements are for a student. The requirements are what
the student has to do before he can go for that grade.

I can find a student's current grade (StudentID =1) by using:
SELECT Last(tblGrades. Grade) AS LastGrade
FROM tblGrade INNER JOIN tblGradingDetai ls
ON tblGrades.Grade ID = tblGradingDetai ls.GradeID
WHERE (((tblGradingDe tails.StudentID )=1));

Is there any SQL statement that I can use that will find from tblGrades what
the next grade and requirements for that grade are for a student? Looping
through a recordset using DAO is something I'd do as the last resort. The
GradeID's are in numeric order.

TIA
Stewart

[Stewart Allen]

Thanks for that Turtle. Didn't think about using DMin and DLookup in that
way. I'll probably use your query solution to utilise Jet's engine.

Stewart
"MacDermott " <ma********@nos pam.com> wrote in message
news:lq******** **********@news read2.news.atl. earthlink.net.. .

If you have the student's current grade, you could get the next grade like
this:
DMin("GradeID", "tblGrades","Gr adeID>" & CurrentGrade)
You could then use DLookup to get the requirements.

You can use the same logic in a query, too.
Or you could use something like this:
"SELECT TOP 1 GradeID,Require ments from tblGrades WHERE GradeID>" &
CurrentGrade & " ORDER BY GradeID"

HTH
- Turtle