Greater than last record put on system Pls Help!

[T1na]

Having problems with SQL, what I want is sql alert that pulls off students that are year 6 and new to our system. Is there any function in SQL that I can pickup students that are greater than the late student.id that is in the system

So far I have got:

SELECT
STUDENT.FORENAME, STUDENT.SURNAME, to_char (STUDENT.DOB, 'DD/MM/YYYY'), STUDENT.ID, STUDENT.NCY
FROM
STUDENT
WHERE
STUDENT.NCY = 6
STUDENT.STUD_ID > ??LAST.STUD_ID?? (function that I am having probelms with)

Thank you

 

[rac2]

Well, I hate to mention it, but there is no student id after the last student id. Leastways, not in a table. Do the student ids exist outside of the table?

It seems like a different definition of a new student would help here.

Working with the facts you posted, maybe you are looking for students who received their ids when they were in year 6. Do you still have the rows for year 5? Then the highest id for a year 5 student might be less than the ids assigned to new students.

In a situation like this it sure would be nice to have a table with a column for the date the student was added. Something to think about if this database is under your control; or if the person who controls it is willing to listen; who knows there may be such a column already.

 

[T1na]

I did not explain myself very well. By putting in the last.Stud_ID I was not referring to a field I meant that I want to pull out the record that is greater that the last number entered into the system.

The only tracking that I have is the stud ID. Every time a new student is added they are allocated the next stud ID number up e.g. 001, 002. Any ideas on a formula to pull out this record. It would be good if I could base it on a date.

 

[rac2]

Possibly what is needed here is to add a new record and give it a student id number that is the next number.

An autonumber field does just that. It automatically assigns the next number, you need not do anything, in fact you cannot add or update that value.

The way that works at the nuts-and-bolts level of SQL would be like this-

INSERT STUDENT ( STUDENT.FORENAME, STUDENT.SURNAME, STUDENT.DOB, STUDENT.NCY ) 
VALUES ( "Bob", "Katt", "15/09/2004", 6)

Note that we are not supplying the STUDENT.ID. Autonumber will fillin that for a new row. In fact if we try to supply a value for that column we will get an error.


Are you building an application using Access Forms?


Or, maybe you want to look at the last student added. This would be

SELECT 
   STUDENT.FORENAME, STUDENT.SURNAME, to_char (STUDENT.DOB, 'DD/MM/YYYY'),  STUDENT.ID, STUDENT.NCY
FROM
   STUDENT
WHERE
   STUDENT.NCY = 6
STUDENT.STUD_ID = ( SELECT MAX(STUD_ID) FROM STUDENT )

It may be that Access cannot handle this type of query. In that case you can define and save a query that provides nothing more than the last student id.

SELECT MAX(STUD_ID) AS "HiStudentID" FROM STUDENT

Save this and give it the name StudentLastID. When you display this guery it will have one row with the column name "HiStudentID".

Then define this query

SELECT *
FROM STUDENT
JOIN StudentLastID ON
       StudentLastID.HiStudentID = STUDENT.ID

This will have one row which is the last row added. Actually the highest student id, which might be the last one added if student id is an autonumber column.

It may be that the DMax(expr, domain, criteria) function could be used in place of the StudentLastID query, but I have never figured out how to use it.