Training DB

[Carina]

How would I handle a situation like this? - Using Access
2000

I have a DB that is used to track employee training.
When an auditor comes in, he/she wants to see what
procedures an employee has been trained on. That's simple
enough - Create a query to generate that info. The
problem I'm having is, the auditor also wants to see what
procedures and employee has not been trained on. (The
procedures and employee should be trained on varies from
employee to employee.) How can I make this happen? I
would have to have blank fields in the DB and I'm sure
that is a no-no. Is this possible?
If so, how?

Thank you

 

[Chris]

It depends on how your data about the training is stored,
but I think that you could write a VB function which inputs
the fields the employee has been trained in and returns the
ones he hasn't been trained in. Again, the format of this
function depends on how you store the training data, but it
sounds feasable.
If you post exactly how you store your data, I'm sure
someone here can help.

HTH (it's a start)

Chris

 

[Carina]

-----Original Message-----
It depends on how your data about the training is stored,
but I think that you could write a VB function which inputs
the fields the employee has been trained in and returns the
ones he hasn't been trained in. Again, the format of this
function depends on how you store the training data, but it
sounds feasable.
If you post exactly how you store your data, I'm sure
someone here can help.

HTH (it's a start)

Chris

fields within those tables I have?

Thank you

 

[John Vinson]

I have a DB that is used to track employee training.
When an auditor comes in, he/she wants to see what
procedures an employee has been trained on. That's simple
enough - Create a query to generate that info. The
problem I'm having is, the auditor also wants to see what
procedures and employee has not been trained on. (The
procedures and employee should be trained on varies from
employee to employee.) How can I make this happen? I
would have to have blank fields in the DB and I'm sure
that is a no-no. Is this possible?

I'm not sure what your table structure is, but typically a one would
have four tables:

Employees
EmployeeID
LastName
FirstName
<other bio information>

Procedures
ProcedureID
Description <of the procedure or course>

Training
EmployeeID <link to Employees>
ProcedureID <what they were trained on>
TrainingDate
<other fields, e.g. evaluation of how well they learned>

TrainingRequired
EmployeeID
ProcedureID
<any other info about this requirement>

A "frustrated outer join" query could be devised to identify those
courses which were required but not taken. Let's see:

SELECT Employees.LastName, Employees.FirstName, Procedures.Description
FROM ((Employees INNER JOIN TrainingRequired ON Employees.EmployeeID =
TrainingRequired.EmployeeID) INNER JOIN Procedures ON
Procedures.ProcedureID = TrainingRequired.ProcedureID) LEFT JOIN
Training ON Training.ProcedureID = TrainingRequired.ProcedureID
AND Training.EmployeeID = TrainingRequired.EmployeeID
WHERE Training.EmployeeID IS NULL;

Air code, untested - but basically you want to create a Query joining
Employees to Procedures via TrainingRequired, and do an "unmatched
query" between this construct and the Training table.

 

[Chris]

Thank you Chris - Do you need to know what tables &
fields within those tables I have?

Well, not exactly. It's actually really more of a
question of how te data is stored (shuold have been more
clear on that): i.e. are there a series of checkboxes for
which kind of training employees have received; is it one
continuous string containing all the forms of training
they have received, separated by some delimiter; are
there several string for each training the employee has
received etc.?

That was the question. I hope there is a quick and easy
solution

Chris