[ms office - 2016] Access Web App - Updating Records Automatically

[JSpaced]

Hey guys, I really need the Edugeek braintrust.

There's a bit of background to this, so bear with:

 

I've been asked to investigate updating our "Performance Other Than Academic" recording system. We have 7 categories of behaviour for our students and they're rated each lesson, on each category from 1-4.

Over the years I've built a massively complex Macro-enabled spreadsheet and housed it on a share. The kids carry around paper copies and at the end of the day a TA opens the spreadsheet for each tutor group and fills in the scores, the lessons etc.

Problem 1: We use DFS shares, which are replicating between three sites, so Shared Spreadsheets break down VERY quickly and stop working/ lock themselves etc.

Problem 2: The kids destroy their paper copies, so the poor TA has to reassemble the scores from fragments of torn paper. (Sometimes they destroy other kids POTAs.)

 

The Solution:

An Access Web App database - built on Sharepoint and so accessible by all staff at all times. Consisting of:

Student Information - including teaching group

A POTA table which creates a new record consisting of: Date, Teaching Group, Student, Lesson, Score 1, Score 2, Score 3 etc.

The teacher logs into Sharepoint, chooses the teaching group, enters the lesson, picks the student, enters scores - done, saved.

 

The new problem:

We move students between teaching groups because the relationships the students have fluctuate because of the extreme behaviours we encounter.

So you go into the Student Information and update their teaching group, no problem.

HOWEVER:

The POTA table does not update the Teaching Group for past records.

I've been trying to construct a Data Macro query where:

For every record where the student ID matches, but the teaching group does NOT match, update the teaching group field, based on the student information.

But it seems like Access Web App Data Macros CANNOT reference information in other tables, only within the one table.

 

So, does anyone have a clever solution to automatically go through the historical records and update the teaching group field, so that when I look back over a teaching group's data, I can collate it all with one query?

 

Many thanks,

J