Solved

Query to show employees without training records

Posted on 2014-07-30
5
382 Views
Last Modified: 2014-07-30
I have a training DB that I use to track employee skills and what they were trained on, etc. I have a table called "tblEmployees" where I store the employees. I have a table called "tblTrainingRecords", and then a table called "tblTraining Participants". The tblTrainingRecords is the master table where the training classes are stored. And then the tblTrainingParticipants is where the employees are stored for the classes. What I need to do is find out which employees did not have training for a specified training class. If I store all the employees that had classes in the tblTrainingParticipants, how can I find out which employees did not have training? I hope this makes sense.

Larry
0
Comment
Question by:Lawrence Salvucci
  • 2
  • 2
5 Comments
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 40230598
try this query, will return all employees without training records

select *
from tblEmployees As E Left Join tblTrainingParticipants As P
On E.EmployeeID=P.EmployeeID
where P.EmployeeID is null
0
 
LVL 1

Author Closing Comment

by:Lawrence Salvucci
ID: 40230607
Thank you very much for your quick response! Much appreciated!!
0
 
LVL 21

Expert Comment

by:Randy Poole
ID: 40230610
select * from tblEmployees where not EmployeeID in
(Select EmployeeID from tblTrainingRecords TR left join
tblTrainingParticipants TP on TR.id=TP.trainingid where
TR.classname='some class') 

Open in new window

This will allow you to specify a class name/description then return all employees that are not registered for that class
0
 
LVL 21

Expert Comment

by:Randy Poole
ID: 40230614
Sorry, thought you wanted a list based on who did not attend a class, not employees that never attended any class..
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 40230621
I do want a list based on who did not attend a class. I was gonna create a query with a criteria selection and use that as table "P" from example Rey gave me. Then I would just make the selection and fire the query example he gave me. Would your example do the same thing but only require one query?
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now