If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Need query to find employees who have not had required training
I am creating a database to track employee training. I have created 3 tables:
my first table is for general employee information, my second is for employee training history which is linked to the first table by the employee #. This table list all of the classes that the employee has taken to include course name, course#, class #, date completed and date re-current training is due. My third table is linked to the second table by the course name and contains all of the courses available for the employee to take. Some of these classes are mandatory classes and I have used a yes no field to identify them. I need to build a query to identify all of the employees who have not had any or all of the mandatory classes. This is starting to get the best of me. Is there a way to do this or am I beating my head against the wall? Your help with this would be greatly appreciated insp36rls |
#2
|
|||
|
|||
Need query to find employees who have not had required training
You can build a query that lists all course that are mandatory, right?
You can build a query of all courses taken an employee, right? Create an unmatched query (use the query wizard to help) ... what courses are in the first query that are not in the second? If you need to do this for ALL employees at once, first create a list of all required courses for all employees -- use a cartesian product query (tables are NOT joined) between mandatory courses and employees. Then run your unmatched query from mandatory courses against this cartesian product query. -- Regards Jeff Boyce Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ Microsoft Registered Partner https://partner.microsoft.com/ "insp36rls" wrote in message ... I am creating a database to track employee training. I have created 3 tables: my first table is for general employee information, my second is for employee training history which is linked to the first table by the employee #. This table list all of the classes that the employee has taken to include course name, course#, class #, date completed and date re-current training is due. My third table is linked to the second table by the course name and contains all of the courses available for the employee to take. Some of these classes are mandatory classes and I have used a yes no field to identify them. I need to build a query to identify all of the employees who have not had any or all of the mandatory classes. This is starting to get the best of me. Is there a way to do this or am I beating my head against the wall? Your help with this would be greatly appreciated insp36rls |
#3
|
|||
|
|||
Need query to find employees who have not had required trainin
Jeff: Thanks for the suggestion, I will try it out and let you know how it
goes. Again thanks, I really appreciate the help. insp36rls "Jeff Boyce" wrote: You can build a query that lists all course that are mandatory, right? You can build a query of all courses taken an employee, right? Create an unmatched query (use the query wizard to help) ... what courses are in the first query that are not in the second? If you need to do this for ALL employees at once, first create a list of all required courses for all employees -- use a cartesian product query (tables are NOT joined) between mandatory courses and employees. Then run your unmatched query from mandatory courses against this cartesian product query. -- Regards Jeff Boyce Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ Microsoft Registered Partner https://partner.microsoft.com/ "insp36rls" wrote in message ... I am creating a database to track employee training. I have created 3 tables: my first table is for general employee information, my second is for employee training history which is linked to the first table by the employee #. This table list all of the classes that the employee has taken to include course name, course#, class #, date completed and date re-current training is due. My third table is linked to the second table by the course name and contains all of the courses available for the employee to take. Some of these classes are mandatory classes and I have used a yes no field to identify them. I need to build a query to identify all of the employees who have not had any or all of the mandatory classes. This is starting to get the best of me. Is there a way to do this or am I beating my head against the wall? Your help with this would be greatly appreciated insp36rls |
#4
|
|||
|
|||
Need query to find employees who have not had required trainin
Jeff: The first unmatched query works great. It identifies the course that
has not been accomplished by a single employee. Now the second query on the other hand does not give me the same result. could you please break this down into steps. I think that I might not be building the ALL employees at once, first create a list of all required courses for all employees --list properly and do I need to specify criteria in the unmatched query I created for a single employee and is this used to check for items not listed in my all employee list. I have tried several combinations with no help. "Jeff Boyce" wrote: You can build a query that lists all course that are mandatory, right? You can build a query of all courses taken an employee, right? Create an unmatched query (use the query wizard to help) ... what courses are in the first query that are not in the second? If you need to do this for ALL employees at once, first create a list of all required courses for all employees -- use a cartesian product query (tables are NOT joined) between mandatory courses and employees. Then run your unmatched query from mandatory courses against this cartesian product query. -- Regards Jeff Boyce Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ Microsoft Registered Partner https://partner.microsoft.com/ "insp36rls" wrote in message ... I am creating a database to track employee training. I have created 3 tables: my first table is for general employee information, my second is for employee training history which is linked to the first table by the employee #. This table list all of the classes that the employee has taken to include course name, course#, class #, date completed and date re-current training is due. My third table is linked to the second table by the course name and contains all of the courses available for the employee to take. Some of these classes are mandatory classes and I have used a yes no field to identify them. I need to build a query to identify all of the employees who have not had any or all of the mandatory classes. This is starting to get the best of me. Is there a way to do this or am I beating my head against the wall? Your help with this would be greatly appreciated insp36rls |
Thread Tools | |
Display Modes | |
|
|