Generate Year Group from Date

[MikeKD]

Hi Folks,

I've got a school database underway at the moment.

My current problem is that I want a way to move pupils up a year group annually.

 

In the past I've just run a script that +1 to the year group field.  But I reckon that's not the best way to do it...

 

What I've got now is a year of entry to Year 7 field. I'd like to be able to work out the pupils current year group (7,8,9,10,11,12 or 13) using that and the current date.  August is the ideal time to transition between the two as it's a clear month with no teaching.

 

What's the best way to do this? With a calculation field or a script I run when needed?

 

Thanks!

Mike

[LaRetta]

 

My current problem is that I want a way to move pupils up a year group annually.

 

In the past I've just run a script that +1 to the year group field.  But I reckon that's not the best way to do it...

 

 

Hi Mike,

 

And what do you do if a student doesn't move up?

 

I suggest instead that you use a table and create records for each year/student combination.  In this way, you can generate reports showing how many students were in a specific year (or class) AND you have an audit trail in case something goes wrong in the middle of a script.  With records, you can see what has happened whereas simply changing the value in a field leaves no clue to the prior history.

 

This is same problem when folks try to just change their inventory balance (when they use a single number field) ... 

 

Using related join records is what I would recommend.  If you would like to explore that option, we can help you with it.  :-)

[MikeKD]

Hi LaRetta,

What you say makes sense. It's almost unheard of for a pupil not to go up, but it is possible so should be accounted for.

 

So you suggest something like:

YearGroup - JoinTable - Pupil

 

Or do I not need the join table as pupils will only be in one year. AIUI you're saying that the join table record is useful for keeping track of things.  I should be OK with the relationships, I'm really struggling with all scripting though, and I'm not quite sure of the theory of the idea.

 

The database I'm working on is in the Many Many to Many Relationships + Headache thread, though it's had a bit of work since I last uploaded it.

 

Many thanks!

Mike

[LaRetta]

Unfortunately I haven't had time to review that thread but if you require multiple bits of information in a single field (whether checkbox or multiline or whether you remove prior value and insert new), it usually suggests a table would work best, which resolves a many-to-many situation.

 

Can you provide simple screen shot of just the few tables involved (pull other table occurrences out of the way) and open those occurrences so I can see the current key names for their joins?

 

If there is nothing else to track about the year, you may not need a join table (class years?) but you should have SOME table which is a join.  Something like:

 

Students -- <  Class Years >-- Classes

 

On students side ... Students::StudentID = Class Years::StudentID

On Classes side ... Classes::ClassID = Class Years::ClassID

 

Some classes may not be offered on some years.  So this table would provide that track.  And it would track whether a student attended that year (by their absence or by creating the join record and marking them absent).  And there is probably other information which is tracked (or could be tracked) in that join table, maybe GPA or parent consent form or ??

 

Scripting would depend upon what you wish to do and how you are currently structured.  It sounds like you are currently a bit relationally-challenged so seeing your setup would be required before I could suggest further.  I'll try to take a look later or maybe others who have followed that thread can help as well.

[MikeKD]

Hi there,

Here's the relationship at the moment, is there enough info there, or do you want me to open up more? (I'm starting to struggle to keep it all on the screen!)

Cheers,

Mike

[LaRetta]

Hi Mike,

 

If I understand ( and that is a big IF ), you might already have the join table required - your Assessment table?  It consists of:

 

PupilID

ClassID

YearGroup ( school year? )

GPA ( or whatever else you track which pertains to a particular pupil's data within that single year )

 

Purpose being a single record for each unique combination of pupil, class and year.  Although you did a great job of presenting your need, I still lack clarity.  What is an Assessment?  Are there multiple Assessments for a single YearGroup?  And what is a YearGroup while we're here?  Is it school year?

 

If there are multiple Assessments for a single school year and if a YearGroup is a school year then you need to slide an additional join table thus:

 

Pupils --< Class Years --< Classes

and then  also

Class Years --< Assessments

 

This join table ( Class Years ) would hold specifics about a pupil's school year.  Once structured properly, graduating your students will consist of creating records in your join table.  The only question remains ... will the records be created in Assessments or in a new Class Years table ...  

 

I hope we're on same track but if not, just straighten me out.  

[MikeKD]

Hi Laretta,

the assessment bit took me ages, but with some help I think I've pretty much got it. A class can have many assessments, and an assessment can have many classes. The same is true therefore of pupils.  I have a basic table of Assessment which just gives the info, but most of the actual data is in join tables; pupil_class_asses_join has the pupil's mark, notes and date.

 

If I understand your graduating students concept, each year I'd create a new year 7, 8 etc and create the Year_Pupil_Join records for the that.  The advantage of that is that I wouldn't delete or alter the previous record and could still use it for data gathering. This seems quite sensible. I'd then do the same for classes. (I don't think I need a join table for classes, as they're only in one year.

 

Cheers!

Mike

[MikeKD]

Hi there,

I've added the appropriate tables and am getting near the stage where I add 500 pupils info to my database.

My stumbling block is how do I create the relevant records to join pupils to their appropriate year group?

Is there a way to automate it?

I do have the pupils Date of Birth.

Cheers,

Mike

[MikeKD]

EUREKA!

I can't see any way of allocating the pupils to the correct year other than manually. I can sort them by age which should help, it's just a case of getting a layout that makes it easier.

 

Once they're all joined though, I can just change the year group on the YearGroup field once a year, and they'll still be joined to it by the ID's. Any unlucky pupils who don't move up can just be joined to another yeargroup.

The assessments, if I can manage it, will still have the original year group on them so can be compared with other cohorts.