Solved

Query to show employees without training records

Posted on 2014-07-30
5
401 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 120

Accepted Solution

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Balance after Repayment - 2 6 62
usage of ms/excell 3d maps in ms/access 2016 6 43
Combo box question 6 55
Access 2010: Combo Box Query 5 22
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

710 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