Windows 7 64bit 
Access 2016
Store a count as a field and then calculate a value?
Hello,
Thank you for taking the time to look at my question. This is a somewhat involved situation.
I work in a department that manages practicum placements, and I was asked to keep track of the students who have been deferring placement for a variety of reasons. This has been a complicated process and I taught myself Access and, incrementally, using trial and error and YouTube and Google: I’ve made a behemoth of a database that is functional and robust and I keep adding new features such as determining when their 5-year expiry will be up and file closures and tracking statistics and reports, and contact with the students.
Basically, I have a table to keep track of our students, and a table to keep track of deferrals. Each time a student submits a deferral form, I record their reason to defer (i.e. can’t find a placement, personal reasons, health, employment, etc) and date information.
The issue is that sometimes, students do not submit deferral forms. So, we have to calculate how long they have been deferred based on the number of terms that have elapsed since they became eligible for placement.
Therefore, say a student has been deferring every term since Winter of 2016. There are 3 practicum starts per year therefore, as of the current term, Spring 2017, they have deferred 5 times. Maybe we only have 3 forms for this person. That means there are 2 “Additional Deferrals” and 3 forms in the system for this person, and their Total Deferrals would be 5.
SO – when I go to the form where I look up student information, I have a sub-form/query that counts the number of records in the database with the student number of the student currently displaying. Then there is a spot to enter “Additional Deferrals” and then under OnUpdate, when I type in a number, it calculates the total deferral and saves it under “TotalDef” in the Students database.
My issue arises when I want to see how long students have deferred and this info doesn’t update on its own. I have to enter a number into Total Deferrals for each student, even if it’s 0 (i.e. maybe they’ve submitted their first form and there are no additional ones) for it to properly calculate.
What I would like to do is:
Do a count of how many deferrals are in the Deferral Table for each student number, and store this as a NumDef field in the Students table, so that I can calculate total deferrals. I have already managed to run this query, I set up the table/field relationships and did this query:
SELECT Students.StudentID, Count(Deferrals.StudentID) AS NumDef
FROM Students INNER JOIN Deferrals ON Students.StudentID = Deferrals.StudentID
GROUP BY Students.StudentID;
I get the number in the system from each student number in StudentID (and there are no duplicates), but I want to store the NumDef in the student table for the record that matches that StudentID. Append Query doesn’t work.
I also tried to do a Lookup, but this just makes a drop-down box for a field in a table.
Basically, I want to COUNT how many times each student number in the Students table matches the StudentID on a deferral in the deferral database, store that number in a field and then add it to the number of additional deferrals. And, now, since we are adding every deferral form as it comes in, there wouldn’t be that many additional deferrals, just once each term we’d add one to each person who hadn’t submitted a deferral yet.
Does this make sense? Is there an easy way to do this?
Thanks for your help!
Ian
Reply With Quote
Originally Posted by aytee111
