Using Not In, or wahtever will work

[Stephen Simon]

Hello,

I track training for my office. I’m trying build a single query that will show me who still needs training in a certain category, (Category 1).

The Legacy expression is to exclude people who no longer work here.

Category 1 is a yes/no which tell whether the training is in category 1 or not.
I want to end up with a list of people in need of Category 1 training and which Category 1 trainings they need.

Ideally, I would inner join a Staff table and draw the Staff_Name from there. When I try to do that, I get everyone in the Staff table.

I’ve been banging at this for a week. I won’t tell you all of the things I’ve tried, because I could have easily picked up the right tool and just used it the wrong way. It’s been over a decade since I’ve tried to do anything this complicated.

I look forward to your comments.

Here’s what I have so far:

SELECT DISTINCT Technical_Training.Staff_Name, Technical_Training.Training_Name, Technical_Training.Training_Number

FROM Technical_Training

WHERE (((Technical_Training.Staff_Name)

Not In (SELECT Staff_Name FROM Technical_Training

WHERE Training_Number IN ([Enter Training Number])))

AND ((Technical_Training.Legacy)<>-1)

AND ((Technical_Training.[Category 1])=True));

 

[Minty]

You want to exclude the staff not the training - so this
((Technical_Training.Legacy)<>-1)

Should be
((Stafff.Legacy)<>-1)

So you need the join to the Staff with that criteria.
If that doesn't work please post up a picture of the relationship window with all the fields names for the two tables.

 

[isladogs]

Have you tried a simple unmatched query using the query wizard?

 

[The_Doc_Man]

You have a sub-query AND it shares the same table as the main query. You need to qualify which query - main or sub - holds each field that you want. This comment is STRICTLY about syntax. Minty's request regarding relationships is what we need so that we can discuss the semantics of your request.

SELECT DISTINCT TT1.Staff_Name, TT1.Training_Name, TT1.Training_Number
FROM Technical_Training AS TT1
WHERE (  TT1.Staff_Name  Not In 
    ( SELECT Staff_Name FROM Technical_Training AS TT2
    WHERE TT2.Training_Number IN ([Enter Training Number] )  )
AND ( TT1.Legacy)<>-1)
AND ( TT1.[Category 1]=True);

Each FROM clause opens an implied recordset BUT that means that both the main and sub recordsets have the same field names. They are two different views of the same table so you have to help Access by showing it which one you meant to use in which place... because the query as a whole sees BOTH views.

 

[Stephen Simon]

You want to exclude the staff not the training - so this
((Technical_Training.Legacy)<>-1)

Should be
((Stafff.Legacy)<>-1)

So you need the join to the Staff with that criteria.
If that doesn't work please post up a picture of the relationship window with all the fields names for the two tables.

You want to exclude the staff not the training - so this
((Technical_Training.Legacy)<>-1)

Should be
((Stafff.Legacy)<>-1)

So you need the join to the Staff with that criteria.
If that doesn't work please post up a picture of the relationship window with all the fields names for the two tab

You want to exclude the staff not the training - so this
((Technical_Training.Legacy)<>-1)

Should be
((Stafff.Legacy)<>-1)

So you need the join to the Staff with that criteria.
If that doesn't work please post up a picture of the relationship window with all the fields names for the two tables.

First, thanks for the reply.
I took the fields Legacy and Staff_Name out of the Technical_Training table, and added Legacy to the Staff table. The result gave me all of the fields, but no data.
Here's the screen shot:

 

[Minty]

Remove the subquery from the criteria in the Staff name.
You don't need it anymore.

 

[Stephen Simon]

I think you've got me going in the right direction.
I now get a list of everyone that has taken any Category 1 training and the Category 1 training they took.
I'm looking for the inverse.
I want a list of staff that still need Category 1 training and the specific Category 1 training they need.
I keep trying things and I'm not getting closer. I wouldn't want you think I just want you to answer it for me.

 

[Stephen Simon]

Have you tried a simple unmatched query using the query wizard?

I'm not sure how I would do this. One table is Staff and the other is Training, with a staff id number as the primary key. I need to find which staff members have not taken specific trainings in Category 1.

 

[Stephen Simon]

You have a sub-query AND it shares the same table as the main query. You need to qualify which query - main or sub - holds each field that you want. This comment is STRICTLY about syntax. Minty's request regarding relationships is what we need so that we can discuss the semantics of your request.

SELECT DISTINCT TT1.Staff_Name, TT1.Training_Name, TT1.Training_Number
FROM Technical_Training AS TT1
WHERE (  TT1.Staff_Name  Not In
    ( SELECT Staff_Name FROM Technical_Training AS TT2
    WHERE TT2.Training_Number IN ([Enter Training Number] )  )
AND ( TT1.Legacy)<>-1)
AND ( TT1.[Category 1]=True);

Each FROM clause opens an implied recordset BUT that means that both the main and sub recordsets have the same field names. They are two different views of the same table so you have to help Access by showing it which one you meant to use in which place... because the query as a whole sees BOTH views.

 

[Stephen Simon]

I think I understand broadly what you mean. I've been using Technical Training as more of a one sheet. So, I've backed up and taken duplicate fields out of the tables keeping Staff_ID as the primary key.
Thanks for the input.
We had Tornado warning last night, and I can't see me wrapping my head around this with this little sleep. I'm afraid it's on the edge what I can digest when I'm well rested.

 

[Minty]

I think you've got me going in the right direction.
I now get a list of everyone that has taken any Category 1 training and the Category 1 training they took.
I'm looking for the inverse.
I want a list of staff that still need Category 1 training and the specific Category 1 training they need.
I keep trying things and I'm not getting closer. I wouldn't want you think I just want you to answer it for me.

Okay - I'm not sure your data is stored in a sufficiently normalised structure to do this.
Currently you only know what courses someone has taken.
If you had a new course you wouldn't know it existed as it's an unknown quantity.

You really need at least three tables :
tblStaff - with the staff details
tblCourses - with the details of the training courses
tblTrainingTaken - This is a Junction table that has the CourseID and the StaffID and lists who and when they took the course.

With this structure it becomes simple to see who has taken what or not.
You could add records to the junction table with a Date to be taken by, date completed etc...