Query to create records if necessary

[Keith]

I'm not sure this is possible without programming, but I thought I would post
and see what others think.

I would like to create a system that allows me to record what skills a
particular employee is certified in. I have an employee table, with the
primary key being the employee ID. I have a skills table, with the primary
key being the name of the skill. I am able to combine these two tables in a
query which has a line for each employee/skill combination. It looks like
the following:

Employee ID SkillName
06 0-1 Micrometer
06 1-2 Micrometer
07 0-1 Micrometer
07 1-2 Micrometer

I have created a third table with the fields EmployeeID, SkillName, and
approved(yes/no).

Is there a way to create a query combining the Employee/SkillName query
shown above with the third table described that creates the record in the
third table when the approved field is checked?

Following is the SQL for a query I have attempted to create:

SELECT qryEmployeesAndSkillsList.EmployeeID,
qryEmployeesAndSkillsList.SkillName, tblEmployeeSkillsApproved.Approved,
tblEmployeeSkillsApproved.EmployeeID, tblEmployeeSkillsApproved.SkillName
FROM qryEmployeesAndSkillsList LEFT JOIN tblEmployeeSkillsApproved ON
(qryEmployeesAndSkillsList.EmployeeID = tblEmployeeSkillsApproved.EmployeeID)
AND (qryEmployeesAndSkillsList.SkillName =
tblEmployeeSkillsApproved.SkillName);


The problem is, of course, that I cannot check the approved checkbox since
the query is not updateable. Does anyone have a suggestion for how I can
accomplish this, or will I have to write code, as I've done in the past?

Thanks,

Keith

 

[KARL DEWEY]

I thnk your third table should have fields EmployeeID, SkillName, and
approved(DateTime).
Use an Employee form/Skills subform have employee table and EmpSkill table
with EmployeeID as Master/Child links. Use a combo box to select the skill
in the subform.
Then a second form/subform to view skils-employees.

 

[Keith]

Karl,

Thanks for the response. I think that's what I'll do. The form will be
kind of clunky with 2 subforms, but it's better than writing a bunch of code
that may or may not be portable.

Thanks again,

Keith

 

[KARL DEWEY]

No I did not mean one form with two subforms but two different forms with one
subform each.