Find Records Not Scheduled/Complete

[DMerchen]

Hello all, it has been a while since I have worked with Access, and so I have not been here as well.

I have a database for training. I have a table of employees with one of the criteria being active which is enabled by a check box. I have a second table of all of the training data. When I add a new employee, or I want to schedule someone, I want to be able to have a combobox on a form which I can select the type of training and then with a push of a button find if there is an active employee which has not had this training through results of a query. I am stupified as to how I can do this and would appreciate your assistance.

Thanks,
Dave

 

[pbaldy]

Two queries; the first finds the employees who have had the training, the second uses the unmatched query wizard to compare that result against all employees (or a query of all active employees).

 

[DMerchen]

Can I run one query right after another? Basically I use the first query from the form to gather information on employees who match the training I selected, but then I need to run a second query to find those individuals that don't have the training that are active employees. I was able to run an unmatched query on all training and found an individual not trained on anything, but it wasn't to a specific training type. I am getting bogged down in this area.

 

[pbaldy]

You would only run the second query; it would run the first internally.

 

[DMerchen]

I can run the unmatched query to find employees that have had no training, but I have several training options. I have a form I want them to choose which training, then get the results of who has not had this training. Does the form pull the results for the second query, because I would need to know the individuals that had that specific training before I could move forward and compare the two, correct. Perhaps I have something incorrect in my database?

 

[pbaldy]

Your first query finds the people who have had the selected training, and you can use the form where they choose, then the unmatched query wizard to compare that against all active employees.

 

[DMerchen]

Is it easy enough to upload a database to have it reviewed? I have the query for the first part. It pulls the employees that have been trained for a particular training type based on a selection on a form, just not getting results on that second part. Or perhaps a small example for me to view.

 

[pbaldy]

Sure, post the db here:

http://www.access-programmers.co.uk/forums/showthread.php?t=140587

 

[DMerchen]

Very good!! I truly appreciate the assist. I am sure it is something I am missing. :banghead:Here we go!

 

[pbaldy]

So which is the working first query?

 

[DMerchen]

If I use the form group training sign-off it will pull up the results based on the type selected. Perhaps this is where I am going wrong?

 

[pbaldy]

Two queries, the first named "qryPaulBase":

SELECT [Training Data].EmployeeID
FROM [Training Data]
WHERE ((([Training Data].[Training Description])=[Forms]![Group Training Sign-Off]![Combo0]));

the second based on that:

SELECT Employees.EmployeeID, Employees.[Last Name]
FROM Employees LEFT JOIN qryPaulBase ON Employees.[EmployeeID] = qryPaulBase.[EmployeeID]
WHERE (((qryPaulBase.EmployeeID) Is Null));

 

[DMerchen]

Hey!! That works!! Still working on how it works, but it works! I am sure it will take a bit of time for me to figure out! Thanks for the assistance Paul!!

 

[pbaldy]

Happy to help Dave!

 

[DMerchen]

Hey Paul, is there an easy way to get the training description in the column next to the name of the individual lacking training, just as a reminder what training they were running the query on?

 

[pbaldy]

Sure, you can get it directly from the form. I'd be presenting the results in a report, so I'd just have it once at the top.

 

[DMerchen]

Hmm....seems that would work!! Thanks again!

 

[DMerchen]

Okay.....can't get it to pull over from the form for some reason. Any suggestions? Just getting nowhere today!!

 

[DMerchen]

Got it!! :banghead: Been difficult today, but I appreciate the assist.

 

[pbaldy]

Happy to help!