Automatically Increase Value in Field - Annually

[Snapperhead]

I have a database of students heres my dilema.....

 

Calucualtion 1

 

I am trying to write a calcualtion that would increase the value of the field gradefield by 1 every year on a given date, say the first of January each year.

 

The values of gradefield range from:

 

Kindergarden,1,2,3,4,5,6,7,8,9,10,11,12, Tafe & Unknown.

 

As a novice, I'm not sure where to start as there are some text values in this field.

 

 

Calcualtion 2

 

I would also like to flag some records when they changed By Calculation 1.

 

For example, records changed to the value "7" in the gradefield , need to be flagged so I can remove some data or change the value of another field typeschoolfield from "primary school" to "Secondary School".

 

I think I could script the second part, but have no idea on where to start with the 1st calcualtion.

 

Thanks

 

Confused

[AHunter3]

So you're going to throw away a year's worth of data at the beginning of each new school year? Why?

 

Why not have a SchoolYear table, a Courses table, and a Students table, and have the table containing grades and whatnot be the confluence of a given Student taken a given Course during a specific SchoolYear? And NOT throw your data away?

[Ender]

... and have the table containing grades and whatnot be the confluence of a given Student taken a given Course during a specific SchoolYear?

 

Maybe call it "Enrollment".

 

This is a really common structure in Student Data Management systems.

[Snapperhead]

So you're going to throw away a year's worth of data at the beginning of each new school year? Why?

 

Why not have a SchoolYear table, a Courses table, and a Students table, and have the table containing grades and whatnot be the confluence of a given Student taken a given Course during a specific SchoolYear? And NOT throw your data away?

 

I'm now rethinking the overall concept of the database and what I want to achieve with it.

 

You raise some valid points about throwing away data. I agree with your comments, I'm now thinking that perhaps there would be a better way to manage the data.

 

Some history, the original application of the data base is primarily designed to report transportation of students to and from school on various bus services.

 

In the past the data was used entirely as a reporting mechanism to a Government agency, giving a snapshot of routes, number of children and so on.

 

This data was only required to be sent once on an annual basis.

 

Each Year the entire database was archived ( by year eg 2007_student_database ) and a NEW data base was copied from the pervious version. Students, who had moved or finished school were deleted from the new version of the database.

 

However the applications of the database has grown and will continue to grow to include other aspects of the business, for example servicing of vehicles and many other applications spring to mind, useful for forward planning as well as day to day operations.

 

The database currently has tables for Students, Routes, Vehicles & Schools plus a few other tables. Most of the records within all tables except for students are pretty much static.

 

I will take you advice onboard and start importing students from older versions (2007,2006 etc) on a experimental copy of the current database.

 

Now, any ideas on those calcualtions? I am currently changing them manually each year through a find then replace field contents.

 

[Ender]

Now, any ideas on those calcualtions? I am currently changing them manually each year through a find then replace field contents.

If you mean your Grade and School Type fields, those are pretty easy to deal with if you record the expected Graduation Year in the Student table. You can then have the Enrollment calculate the Grade based on the difference between the Enrollment's School Year and the Graduation Year. The specifics depend on the format you decide to use for School Year and Graduation Year.

 

The School Type is even easier. Just use a Case() statement with different types for the different Grades.