ms access - Update Calculated [Field] Base on the status of a Separate Table -

I have to change the value of a calculated field based on the result from a different table.

I have made a small fake DB (attached) to highlight my problem .. When the database is open I want to show on the first screen if all training is done for each employee "calculated area ".

1. I have 5 employees.
2. Each employee must have 5 (or more) training.
3. All training should not end time
4. If any training is finished, change the calculated field value to "good"
5. if If all training has not expired, then change the calculated field value to "all good"

I have no idea about the perspective of this scenario, do I have to store this value To create a separate "floating table" Need Help?

The database found here:

I can not link pictures or more than 2 links so please see:

This is my query.

  SELECT Training.ID, training. EmployeeID, employee Name, training. Training. Training, training Training, training Include employees due to being less than employees (Training on training inner training training. Training ID = Training List. Training ID) Staff, Add Training, Deadline, Date ("M", [Frequency], [Training Date] EmpireID = Training. EmpireID;  

I have to see that all the training is going on for every employee. If this happens, then I need to show it by changing the value from my first form. The record source of the first form is like this:

  Choose DISTINCTROW Staff. ID, employee. Name, employee "Assistance with this area" as an "training area" from staff, employee;  

Its purpose is to simplify my life and look at it at a glance, which requires recurring training to employees and what has been done till date .. I still can not imagine How can this be done .. I am (Google My Teacher is the kind of access user :()

You have a query that calculates DueDate for all training records. Use as a data source in which you restrict only those records whose DueDate has not already been passed.

  SELECT tq. From [training query] AS teak where (((tq.DueDate)> date =) ());  

If that question Gives correct records - only those training have not expired, its WHERE reuse section in a group BY query In which you count the number of unemployed training per employee.

  SELECT tq.EmployeeID, Count (tq.TrainingID) AS CountOfTrainingID FROM [Training Quote] AS TQ WHERE ((tq.DueDate) & gt; = Date ())) by group tq.EmployeeID;  

If that query also produces sensible results, you can use IIf expression "all good" when CountOfTrainingID = 5 and "Good" when it & lt; 5.

  SELECT sub.EmployeeID, sub.CountOfTrainingID, IIf (subfaccountoffraining id = 5, 'all good', 'not good' ) From the training position (Select tq.EmployeeID, Calculation examination as TC training) Edi [Examination of training] AS vaccine where (((TC. Duitet)> Date = ()) Teak .ampeleid) as the sub;  

This should give you the most route to your goal. To get their names in the Employee table, you still have to be involved.

There may be other problems that you still need to address:

1. If an employee has not completed any training yet, or all of his training ends If it is done, will its name appear in the query (and based on that query)?
2. Do separate training based on your criteria 5 or more? For example, if an employee has completed training only 5 times in the previous month, should he / she should not be good or not?