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

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

i need alter value of calculated field depending on results separate table.

i made little false db(attached) highlight problem.. when database open want able show on first screen if training date each employee, changing value of "calculated field".

i have 5 employees. each employees must 5(or more) training's. all training's must not expired. if single training expired alter calculated field value "no good" if training not expired alter calculated field value "all good"

i have no thought on how approach scenario. need create separate "temp table" store value?

database found here: http://1drv.ms/1tx7l9m

i can't link pictures or more 2 links yet please @ these: http://1drv.ms/1txhr45

here query.

select training.id, training.employeeid, employees.name, training.trainingid, training.trainingdate, traininglist.frequency, dateadd("m",[frequency],[trainingdate]) duedate employees inner bring together (training inner bring together traininglist on training.trainingid = traininglist.trainingid) on employees.employeeid = training.employeeid;

i need check training each employee current. if need show changing value first form.. record source of first form this:

select distinctrow employees.id, employees.name, employees.employeeid, "help field" trainingstatus employees;

the purpose of create life easier , able see @ glance employees need recurrent training , date.. still can't visualized how can done.. (google teacher kind of access user :( )

you have query computes duedate training records. utilize info source query in restrict results records duedate has not passed.

class="lang-sql prettyprint-override">select tq.* [training query] tq (((tq.duedate)>=date()));

if query returns right records --- trainings have not expired --- reuse where clause in group by query count number of unexpired trainings per each employee.

class="lang-sql prettyprint-override">select tq.employeeid, count(tq.trainingid) countoftrainingid [training query] tq (((tq.duedate)>=date())) grouping tq.employeeid;

if query produces sane results, can utilize iif look homecoming "all good" when countoftrainingid >= 5 , "no good" when it's < 5.

class="lang-sql prettyprint-override">select sub.employeeid, sub.countoftrainingid, iif( sub.countoftrainingid >= 5, 'all good', 'no good' ) trainingstatus ( select tq.employeeid, count(tq.trainingid) countoftrainingid [training query] tq (((tq.duedate)>=date())) grouping tq.employeeid ) sub;

that should of way goal. still need bring together in employees table names.

there may other issues still need address:

if employee has not yet completed trainings, or trainings have expired, should name appear in query (and form based on query)? is criteria based on 5 or more different trainings? example, if employee completed same training 5 times in past month, should trainingstatus or no good?

ms-access ms-access-2010