forms/subforms

[jzwp22]

For a cross-trained employee, you might be able to add another job title and then modify the query to assign the qualifications for that job title to the employee. If this happens often in your organization, the it would be best to automate the process. If you don't want to assign a second job title to the employee then you could always assign the qualifications manually.

 

[sunshine076]

Ok I will try it. I would click the qryAppendApplicable Quals right. I am just making sure that I am hitting the correct one. I will try this and see what happens.

 

[jzwp22]

No, you would not use the qryAppendApplicableQualsToAllExistingEmps. That will reappend all records for all employees. You will have to create a new append query.

It would look something like this:

INSERT INTO tblEmpJobQual ( EmpJobsID, JobsQualsID )
SELECT tblEmpJobs.EmpJobsID, tblJobQuals.JobQualID
FROM tblJobQuals INNER JOIN tblEmpJobs ON tblJobQuals.GrpJobTitleID=tblEmpJobs.GrpJobTitleID
WHERE tblEmpJobs.EmpID=[enter employee ID] and tblEmpJobs.GrpJobTitleID = [Enter Job title ID];

You can use a combo boxes on a form to populate the employee ID and the job title ID & run the query via a button on that form.

INSERT INTO tblEmpJobQual ( EmpJobsID, JobsQualsID )
SELECT tblEmpJobs.EmpJobsID, tblJobQuals.JobQualID
FROM tblJobQuals INNER JOIN tblEmpJobs ON tblJobQuals.GrpJobTitleID=tblEmpJobs.GrpJobTitleID
WHERE tblEmpJobs.EmpID= forms!yourformname!employeecombobox and tblEmpJobs.GrpJobTitleID = oforms!yourformname!jobtitlecombobox

 

[sunshine076]

I have done what you have suggested by going with your first option. I created the query and have now ran the query it doesn't show any new data. It shows zero rows copied

 

[jzwp22]

Did you add the new job to the employee (to be cross trained) before running the append query? Are you putting in the correct ID values?

 

[sunshine076]

Yes I am (or at least I think I am) I have clicked on the two tables so in the query I have the two tables and clicked on the field name ID's which are automatically entered into the fields I click the append under query and select EmplJobQuals for the append and then I go into the code and change that. It should work but I am getting a zero return
I have even cut and pasted your statement and compared the data to the new employee data and it is identical. I cannot attach what I have so here is what I have in the code
INSERT INTO tblEmpJobQual ( EmpJobsID, JobsQualsID )
SELECT [tblEmpJobs].[EmpJobsID], [tblJobQuals].[JobQualID]
FROM tblJobQuals INNER JOIN tblEmpJobs ON [tblJobQuals].[GrpJobTitleID] = [tblEmpJobs].[GrpJobTitleID]
WHERE [tblEmpJobs].[EmpID]=[enter employee ID] AND [tblEmpJobs].[GrpJobTitleID]=[Enter Job title ID];

 

[jzwp22]

If no records are appended, then it is because no records are being selected by the SELECT clause portion of the query which means there is probably nothing wrong with the query, so the problem lies in the data itself. Since I don't know exactly what data you have in your database, I cannot troubleshoot that part for you.

 

[sunshine076]

I am going to see about trying to resolve this myself. Since I cannot upload my database now becasue it is too big I will try and figure it out. If I have any more questions I will let you know. Thank you.

 

[jzwp22]

You're welcome. Let us know if you need additional help.

 

[sunshine076]

Would it be wise to create another form to go with the append query similar to what you did for the new employees? Reason why I am mentioning this is because eveyrthing is setup and since I need a place to add the data I figured a form would be the most likely way to go.

 

[sunshine076]

I think that I am going to try my suggestion and see if I cannot get the query to run how it should.

 

[sunshine076]

I still cannot get that query to run. I have tried to create a simple form and have it linked to it but it doesn't work. Any other suggestions?

 

[sunshine076]

No, you would not use the qryAppendApplicableQualsToAllExistingEmps. That will reappend all records for all employees. You will have to create a new append query.

It would look something like this:

INSERT INTO tblEmpJobQual ( EmpJobsID, JobsQualsID )
SELECT tblEmpJobs.EmpJobsID, tblJobQuals.JobQualID
FROM tblJobQuals INNER JOIN tblEmpJobs ON tblJobQuals.GrpJobTitleID=tblEmpJobs.GrpJobTitleID
WHERE tblEmpJobs.EmpID=[enter employee ID] and tblEmpJobs.GrpJobTitleID = [Enter Job title ID];

You can use a combo boxes on a form to populate the employee ID and the job title ID & run the query via a button on that form.

INSERT INTO tblEmpJobQual ( EmpJobsID, JobsQualsID )
SELECT tblEmpJobs.EmpJobsID, tblJobQuals.JobQualID
FROM tblJobQuals INNER JOIN tblEmpJobs ON tblJobQuals.GrpJobTitleID=tblEmpJobs.GrpJobTitleID
WHERE tblEmpJobs.EmpID= forms!yourformname!employeecombobox and tblEmpJobs.GrpJobTitleID = oforms!yourformname!jobtitlecombobox

I would be using the tblJobQual and tblEmpJobs when creating this query. Where is this being appended to? Is it to the tblJobQuals?

 

[jzwp22]

The INSERT clause tells you what table and fields to which the data will be appended, so in this case the data will be appended to the EmpJobsID and JobsQualsID fields in the table tblEmpJobQual

INSERT INTO tblEmpJobQual ( EmpJobsID, JobsQualsID ) ...

 

[sunshine076]

That is how I have it setup. Should there be a new form for the appending query I would say no. I have seen the query that has 2 fields with no blank fields for entry. It only has the headers but yet I copy and pasted everything that was given in a previous message. This is the only part that is holding me up. I data from the tables is not being transferred from this query. hmmm

 

[jzwp22]

Create a new query, switch to SQL view and paste this SQL text into the window & run this query. Even if you don't have the form specified, the query should still prompt you for the employee ID and the group job ID. After inputting those two values, if you get no records returned, then you don't have any records in tblEmpJobs that meet the criteria & thus there is nothing to append.

SELECT tblEmpJobs.EmpJobsID, tblJobQuals.JobQualID
FROM tblJobQuals INNER JOIN tblEmpJobs ON tblJobQuals.GrpJobTitleID=tblEmpJobs.GrpJobTitleID
WHERE tblEmpJobs.EmpID= forms!yourformname!employeecombobox and tblEmpJobs.GrpJobTitleID = forms!yourformname!jobtitlecombobox

You can always manually enter a record in tblEmpJobs (for test purposes) with a specific EmpID and a specific GrpJobTitleID so that you know you have a record that meets the criteria you intend to specify in the query. This will tell you if the query is working properly. If you are worried about messing up the data in your primary database, make a duplicate copy of your database and do your testing in that database.

 

[sunshine076]

I have been working at this all day I have this database in several areas so that I don't mess up and lose it.

 

[sunshine076]

You can always manually enter a record in tblEmpJobs (for test purposes) with a specific EmpID and a specific GrpJobTitleID so that you know you have a record that meets the criteria you intend to specify in the query. This will tell you if the query is working properly. If you are worried about messing up the data in your primary database, make a duplicate copy of your database and do your testing in that database.

This above worked with me manually entering in the data into the tblEmpJobs. Since I know that this works this wayI should then create either a new form or button to use it this way. I have created a new form simlilar to the tbleEmpJobs I would create the new button to have it append the qry right? I guess my question is where would I put the SQL statement inside the command box?

 

[jzwp22]

For the most part, that is what you have to do. If you are going to create a form based on tblEmpJobs, I would open the form in data entry mode only and you will also have to save the record before you run the append query. See the form frmEmpJobs2 in the attached DB.

 

[sunshine076]

I was on the right track last night. There is light at the end of the tunnel yeah. Thank you so much for all your help.