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

*tblGradingDates*
DateID (PK)
GradeDate

*tblGrades*
GradeID (PK)
Grade
Requirements

*tblGradingDetails*
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 tblGradingDetails
ON tblGrades.GradeID = tblGradingDetails.GradeID
WHERE (((tblGradingDetails.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","GradeID>" & 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,Requirements from tblGrades WHERE GradeID>" &
CurrentGrade & " ORDER BY GradeID"

HTH
- Turtle
"Stewart Allen" <sa****@ThisPartNotVailid.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

*tblGradingDates*
DateID (PK)
GradeDate

*tblGrades*
GradeID (PK)
Grade
Requirements

*tblGradingDetails*
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 tblGradingDetails
ON tblGrades.GradeID = tblGradingDetails.GradeID
WHERE (((tblGradingDetails.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.GradeID = tblGradingDetails.GradeID + 1

AND never allow a person to get more then the max acceptable grade......
"Stewart Allen" <sa****@ThisPartNotVailid.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

*tblGradingDates*
DateID (PK)
GradeDate

*tblGrades*
GradeID (PK)
Grade
Requirements

*tblGradingDetails*
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 tblGradingDetails
ON tblGrades.GradeID = tblGradingDetails.GradeID
WHERE (((tblGradingDetails.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********@nospam.com> wrote in message
news:lq******************@newsread2.news.atl.earth link.net...

If you have the student's current grade, you could get the next grade like
this:
DMin("GradeID","tblGrades","GradeID>" & 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,Requirements from tblGrades WHERE GradeID>" &
CurrentGrade & " ORDER BY GradeID"

HTH
- Turtle