compare then return

[AJOlbring]

I have an emplyee training dbase, where employees are assigned to departments (Emp can only be member of one Dep).

Emp<---Dep

Departments require Trainings (more than one per department)

Dep--->Trng

Training sessions are set up with multiple trainings and employees per session

Session--->Trng
--->Emp

I end up with sets of data from several tables and sub tables. Completed Trng per Emp and required Trng per Emp.
What is the easiest way to compare the Emp required Trng with the Emp completed Trng?
Thanks for your help!
AJO

 

[Guus2005]

In that case you need to know which training is required for an employee and you need to know when a training is completed. For instance when a certificate is handed out.

So you need a table in which you store the trainings per department or per employee in general.
An employee must follow both the individual trainings and the trainings for the department the employee works in.

Are you sure that a session has more than one training?
I'd say that a session is a training being given to employees.
Ofcourse there are more session of the same training.

HTH

 

[AJOlbring]

I should not have called it sessions. But what it allows us to do is record a 'Forklift' and a 'Tractor' training (two requirements) in one training for one group of employees. We usually combine trainings and the session feature makes input easier since I already entered 20 to 40 employees once, I drop in another training using a subform.
I can run queries which list trainings per employee or department either completed or required, but I need to run one that lists not completed but required trainings.
i played a little with the 'Find unmatched' query wizzard using the 'training done query' and the 'training required query'. It is working for one employee at a time but not for many employees in one run...
Maybe a report can do it easier, that would be fine.

AO

 

[Guus2005]

Again, when you want to list a set of trainings for an employee, you need to know which trainings an employee must follow and you must store which trainings an employee is following or completed.

That is a table you haven't mentioned yet.

Can you post a sample database?

 

[AJOlbring]

Let me work on it when I get back hm. I have tables containing both...
Thanks

AJO

 

[AJOlbring]

Please check it out.
I have a sample dbase attached.
The 'edit employee' form has the two subs.
Training completed and training requiered, like to see which requiered trainings are not yet completed.

Thanks for your help!

Andreas

 

[Guus2005]

I've added TrngId and EmpDept to the qryEmpsess query
A query to get employees who need some training according to their Department settings is below.

SELECT tblEmp.EmpID, tblEmp.FName, tblEmp.LName, tblEmp.EmpDept, tblDept.DeptName, qrydepttrng.TrngID, tblTrng.TrngName, tblTrng.TrngNote
FROM tblDept INNER JOIN (tblEmp INNER JOIN ((qrydepttrng INNER JOIN tblTrng ON qrydepttrng.TrngID = tblTrng.TrngID) LEFT JOIN qryEmpsess ON (qrydepttrng.TrngID = qryEmpsess.TrngID) AND (qrydepttrng.DeptID = qryEmpsess.EmpDept)) ON tblEmp.EmpDept = qrydepttrng.DeptID) ON tblDept.DeptID = tblEmp.EmpDept
WHERE (((qryEmpsess.TrngID) Is Null));

You need to strip some fields to put it into your subform.

HTH

 

[AJOlbring]

Thanks so much, i'll check it out....

Andreas