Update the linked record to next record (Students promoted to next class)

[Khan]

Hi guys!



I have student and class databases, at the end of session students are transferred to next class, so an update is needed to change class to next class in student database from Class database

Student.ClassID is linked to Class.ClassID to show Class Name.

Now I want an update Query to update all records to next class.

I tried Class.ClassID+1, it displays the next class but how to update the same in student.classid.

Thanks in advance for any help.
Regards
Khan

[Welshgasman]

I sure you mean tables and not databases

Just run an update query adding 1 to student.classid, except for the last class, going by your description

[Khan]

I sure you mean tables and not databases

You are right, my mistake.

Just run an update query adding 1 to student.classid, except for the last class, going by your description

I did it but it says Query not updatable.
I will share the screenshot here.

[isladogs]

You may need to specify unique records as you have a one to many join.
Try changing the query SQL to start with UPDATE DISTINCTROW ...

[Khan]

How to avoid updating last Class?
It's updating all but for the last class (that cannot be updated) it's giving error.

[Welshgasman]

How to avoid updating last Class?
It's updating all but for the last class (that cannot be updated) it's giving error.

Test for last class?

[Khan]

I sure you mean tables and not databases

Just run an update query adding 1 to student.classid, except for the last class, going by your description

How can I avoid updating Last Class???

For the time being, I have added LEFT SCHOOL after EIGHT class but that's not the solution, if I have to add more classes.

Hope you can suggest something workable.

[Welshgasman]

Well presumably the Class has a number and you are incrementing that?
So only do so for Classes where their number is less than Max(Class) - 1 ?

Simple math really?