Display info about data not in the database

Topic

New Reply

I need to create a report that shows me a list of completed and not completed skills for each employee. I created a table of the skills that are required
(tblReqdSkills) to be completed by each employee on a yearly basis (skillID, SkillName). I then created another table (tblSkillsInfo) to enter data about each employee’s skills when they have completed them and a completion date (EmployeeID, SkillName, CompletedDate, etc). If each required skill is not entered into the skills info table for each employee, I won’t know if every skill for each employee was completed. Is there a way to compare the 2 tables for each employee and determine if any of the necessary skills have not been done?

Reply | Quote

Replies

You may not have the structure of your db quite right.

Have one table with EmployeeID, Name, etc. You may have to have a table for each skill that would contain the EmployeeID, Skill, completion date, etc.

Then a report based on a query that looks at all the tables could display the skills completed and not completed.

This is probably what I would do, but I am fairly new at Access. I have had to set up a similar db and this is how it worked for me. HTH.

Reply | Quote

Actually, you need three tables. One for the Employee information (tblEmployee), one for the list of required skills (tblSkill), and one to hold a single record for every completed required skill for each employee (tblEmployeeSkill). The latter is called a join table and need only consist of an EmployeeID and a SkillID, with the two fields making up the primary key of tblEmployeeSkill. Then all you have to do is compare the skills in tblEmployeeSkill with the list in tblSkill to see if any are missing.

Reply | Quote

My brain has gone to lunch! How do I set up the comparison for this?

Reply | Quote

tblSkills contains a single record for each required skill.
tblEmployee contains a single record for each employee.
tblEmployeeSkill contains a single record for each employee with a completed skill of a particular kind.
So any skills that don’t show up in tblEmployeeSkill are missing for that employee.

You would query the unjoined tables like this:

SELECT tblEmployees.EmployeeID, tblSkill.SkillID
FROM tblEmployees, tblSkill
WHERE (((tblSkill.SkillID) Not In 
(select [skillID] from tblEmployeeSkill as E
where E.EmployeeID = tblEmployees.EmployeeID)));

That will return a list of all employeeIDs and the skills each employee hasn’t completed.

Reply | Quote

Charlotte,

Thanks for the help. I can see where I need to follow up my Access class with a Visual Basic class.

Jim

Reply | Quote

Actually, try a SQL class.

Reply | Quote