Data Structure for Student Tracking?

[RGBrighton]

Hi,
Can you suggest a way of setting up a data structure to meet my situation?

I have a table of students for whom I want to record a list of grades for
particular assessment points as they progress through the year. A key
feature is that every student has the same set of assessment points. I
envisage the list of assessments may need amendment/addition and they require
longish titles (eg "First review point following induction session" etc). So
I have created a table of 'Assessments" in which there is one record for each
assessment point - this should allow me to edit the descriptions of each
assessment point and add extra ones should we wish.

I want to have a form to edit/enter every assessment grade for each student.
I envisage having a junction table between 'Students' and 'Assessments'
where the appropriate grade for each Student/Assessment is stored.

My problem is that at the start of a year there are no grades recorded but I
still want the form to have a space for every possible assessment grade
against each student.

Do I need to write some code to populate the junction table with all the
students and every assessmentID? (In which case how should the system
respond to an added assessment point?)
Or is there some neat form of query that would provide for this? (Some sort
of crosstab or similar that puts each student ID against each assessmentID
even when there are no grades yet?)
Or am I completely wrong in seeing this as a many to many between students
and assessments? Is there a more appropriate data structure?

I am sorry this is long-winded, Thanks for reading this far!
I feel this must be a fairly standard requirement and I am probably missing
something obvious!

Richard

 

[Allen Browne]

Assuming that:
a) you have all the assessments in place before you start adding students,
b) every student does every assessment,
you could execute an Append query statement in the AfterInsert event
procedure of the form where you add students. That way, every time you add a
student, all their assessments are automatically added to the junction
table.

To get the exactly query statement, you could mock up an Append query
(Append menu in query design) that selects AssessmentID from the Assessment
table. In the Field row, enter:
StudentID: 99
as a sample value. Then switch the query to SQL View (View menu) to see the
kind of string you have to create. In the Form_AfterInsert code, you will
break the string and concatenate the new StudentID value into the string in
place of the 99, e.g.:
"... Assessment.AssessmentID, " & Me.StudentID & " AS StudentID FROM ...

For help with executing the query statement, see:
http://allenbrowne.com/ser-60.html

The assumption that every student will do every assessment really bothers me
though. Normally, one subject is offerered many times over the years, so
there's a one-to-many relation between subjects and the actual instances
when they are run. Each instance has many assessments associated with it,
but every student does not do every instance of every subject every time
it's offered.

 

[RGBrighton]

Thanks Allen,
I think an Append query may be the solution (I generally only use
straightforward SELECT types).

I understand your concern about the nature of the student/subject
relationship but this is a single training course where, to complete the year
successfully, the students all start together and have to complete a
predetermined set of assignments over the year. What I want is a tracking
form where I can enter their progress through the year against the various
assignments and from there generate reports showing who is doing well or
causing concern etc.

At present I have a table of student details and a table of assignments. I
want a form to show a blank space to enter results for every assignment
against each student.

Entering new students is unlikey as they all have to start at the
commencement of the year. Entering new assignments is likely as colleagues
may want extra review points included. (Some are major written assignments
but some are just whether a form has been completed or not).

I will follow up the APPEND query idea but I'm sorry I won't have time to
check this out properly til the weekend!

Thanks for your help
Richard

 

[Allen Browne]

If you are more likely to add new assignments than to add new students, you
probably want to execute this append query statement in the AfterInsert
event procedure of the Assignment form, rather than the Student form.

The query appends to the junction table, of course, appending Null to the
Result field so you can fill the value in later.