Is this posible:
I am working in SQL 2000, using MS Access 03 as the front end.
I have a table 'Mileage' that has the following fields, ID, Vehicle_ID, Current_Mileage, Date Checked, Last_Current_Mileage and Mileage_3000. I am trying to find out if it is posible to create a trigger so that when I add a new record and update the Vehicle_ID field, it will look back at the last record with the same value for Vehicle_ID and insert the Current_Mileage of that record in the Last_Current_Mileage field of the record I am adding. In addition, I am needing the Mileage_3000 field to populate with the value of Last_Current_Mileage + 3000.
Ultimately, what I am trying to put a validation on the Current_Mileage field for new records such that it is between the Last_Current_Mileage and the Last_Current_Mileage + 3000. So, that means that these fields need to be poplated by the trigger upon updating the Vehicle_ID field so that when I update the Current_Mileage field, it has something to validate against. I am pretty new with SQL and a novice with triggers. Any ideas, thoughts, suggestions would be helpful. Thanks