Providing a count on the number of records
I have a student database which is collecting information from staff regarding student homework which has not been done and student work variations (which are items such as forgetting a text book etc).
I have managed to create one table with the student records. I have another table which has the term dates. These tables are linked now so I am able to collect the term number by comparing the date the record was made, with the date range that the term goes for.
What I now need to do is to record a running count against each student each term per homework or work variation recorded.
I think the best way to achieve this is to add another field to student homework table which is a calculation (?). What I need to do is this:
For each record in the table:
1. Look at the STUDENT ID, YEAR and the TERM and check if they have been seen before.
2. If these items have not yet been seen, record the value "1" in the field TERMCOUNT
3. If the item has been seen before, collect the value of TERMCOUNT and increment it by 1 and add it to the TERMCOUNT field.
4. Go to the next record and start again at step 1.
The reason I think a calculation would be best is it allows the system to be flexible enough to rework out all the values on the fly, and will not require someone to sit down and change everything (which would become confusing ;->)
The other reason is that I want to use these numbers in a layout is so it shows the items in order they were added per student, and it should allow me to set off a nightly script which checks the TERMCOUNT field and emails someone when the count is over 5, then again at 10 then again at 15. Each time it does this it would change an emailed field to YES.
So any ideas on how I might achieve this?