How to create a database with changing records while keeping note of previous field data
Hi again. I am really stumped on this one and could use some help figuring out where to begin on this. I have a banking database for students that keeps track of their school/grade/teacher information as well as the deposits they make and their dates. I have data entered into my working database for the 2012/2013 school year. Many of these students will be returning for the 2013/2014 school year. When this happens, their grade, home room, and teacher name will change. I have a table listing this information related to a deposit and record table.
My question is what is the best way to update these records wtih their new information while not losing their old information (teacher name, homeroom number, and grade)? I really want to set it so that in this table listing the student information, I can select the specific school year and have the teacher, homeroom, and grade fields change appropriately. Is this possible? How would I go about doing this?