Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Query to show employees without training records

Posted on 2014-07-30
5
Medium Priority
?
422 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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

772 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