Help with query

[Drew]

I have to work on a report in a database that I did not build. I am needing
some help with the query for the report. The query is for showing employees
who haven't had a certain training. Here is the info that you may need,

ClassesTaught Table
Title - Title of Class
ClassNo - Class Number
StartDate - Date class started
EndDate - Date class ended
Time - Amount of time for credit
Comments - comments about class

Training Table
EmpNo - EmployeeNumber
Score - Score in training
ClassNo - ClassNo from ClassesTaught

StaffCore Table
EmpNo - Employee Number
EmpFName - Emp First Name
EmpLName - Emp Last Name
More info but not important to current problem

I need to make a query showing all employees who DID NOT take a certain
class. I'm not sure how to go about this, and would like to illicit some
help from others.

Thanks,
Drew Laing

 

[Brian]

I have to work on a report in a database that I did not build. I am needing
some help with the query for the report. The query is for showing employees
who haven't had a certain training. Here is the info that you may need,

ClassesTaught Table
Title - Title of Class
ClassNo - Class Number
StartDate - Date class started
EndDate - Date class ended
Time - Amount of time for credit
Comments - comments about class

Training Table
EmpNo - EmployeeNumber
Score - Score in training
ClassNo - ClassNo from ClassesTaught

StaffCore Table
EmpNo - Employee Number
EmpFName - Emp First Name
EmpLName - Emp Last Name
More info but not important to current problem

I need to make a query showing all employees who DID NOT take a certain
class. I'm not sure how to go about this, and would like to illicit some
help from others.

Thanks,
Drew Laing

SELECT S.EmpNo FROM StaffCore S LEFT JOIN Training T ON (S.EmpNo=T.EmpNo AND
T.ClassNo=[ACertainClassNo]) WHERE T.ClassNo IS NULL

 

[Drew]

Thanks!

Drew

I have to work on a report in a database that I did not build. I am needing
some help with the query for the report. The query is for showing employees
who haven't had a certain training. Here is the info that you may need,

ClassesTaught Table
Title - Title of Class
ClassNo - Class Number
StartDate - Date class started
EndDate - Date class ended
Time - Amount of time for credit
Comments - comments about class

Training Table
EmpNo - EmployeeNumber
Score - Score in training
ClassNo - ClassNo from ClassesTaught

StaffCore Table
EmpNo - Employee Number
EmpFName - Emp First Name
EmpLName - Emp Last Name
More info but not important to current problem

I need to make a query showing all employees who DID NOT take a certain
class. I'm not sure how to go about this, and would like to illicit some
help from others.

Thanks,
Drew Laing

SELECT S.EmpNo FROM StaffCore S LEFT JOIN Training T ON (S.EmpNo=T.EmpNo
AND
T.ClassNo=[ACertainClassNo]) WHERE T.ClassNo IS NULL

 

[Drew]

Why does Access not like this query... it runs fine, gives me the correct
data but when I go back to "Design View", Access changes the query for me.
Any ideas?

Thanks,
Drew

I have to work on a report in a database that I did not build. I am needing
some help with the query for the report. The query is for showing employees
who haven't had a certain training. Here is the info that you may need,

ClassesTaught Table
Title - Title of Class
ClassNo - Class Number
StartDate - Date class started
EndDate - Date class ended
Time - Amount of time for credit
Comments - comments about class

Training Table
EmpNo - EmployeeNumber
Score - Score in training
ClassNo - ClassNo from ClassesTaught

StaffCore Table
EmpNo - Employee Number
EmpFName - Emp First Name
EmpLName - Emp Last Name
More info but not important to current problem

I need to make a query showing all employees who DID NOT take a certain
class. I'm not sure how to go about this, and would like to illicit some
help from others.

Thanks,
Drew Laing

SELECT S.EmpNo FROM StaffCore S LEFT JOIN Training T ON (S.EmpNo=T.EmpNo
AND
T.ClassNo=[ACertainClassNo]) WHERE T.ClassNo IS NULL

 

[Brian]

Why does Access not like this query... it runs fine, gives me the correct
data but when I go back to "Design View", Access changes the query for me.
Any ideas?

Thanks,
Drew

Access does like the query: as you say, it runs and gives you the correct
results. The problem is the query designer, not the query: there are
various things that can be done in SQL but which the query designer is
incapable of displaying, such as the relatively sophisticated join used in
this query. In order to maintain this query, you'll need to use the SQL
view.

 

[Drew]

Another question,

Why can't I add another join onto this query? I need to get the class
titles out so that we know what class to choose. I have tried this,

SELECT StaffCoreTable.EmployeeNo, StaffCoreTable.StaffFName,
StaffCoreTable.StaffLName, StaffCoreTable.StaffDept,
ClassesTaughtTable.Title
FROM StaffCoreTable LEFT JOIN TrainingTable ON
StaffCoreTable.EmployeeNo=TrainingTable.EmployeeNo AND
TrainingTable.ClassNo=[ClassNumber] INNER JOIN TrainingTable.ClassNo =
ClassesTaughtTable.ClassNo
WHERE TrainingTable.ClassNo IS NULL AND StaffCoreTable.Active = Yes;

When I try this query I get, Syntax Error.

Thanks,
Drew

 

[Brian]

Another question,

Why can't I add another join onto this query? I need to get the class
titles out so that we know what class to choose. I have tried this,

SELECT StaffCoreTable.EmployeeNo, StaffCoreTable.StaffFName,
StaffCoreTable.StaffLName, StaffCoreTable.StaffDept,
ClassesTaughtTable.Title
FROM StaffCoreTable LEFT JOIN TrainingTable ON
StaffCoreTable.EmployeeNo=TrainingTable.EmployeeNo AND
TrainingTable.ClassNo=[ClassNumber] INNER JOIN TrainingTable.ClassNo =
ClassesTaughtTable.ClassNo
WHERE TrainingTable.ClassNo IS NULL AND StaffCoreTable.Active = Yes;

When I try this query I get, Syntax Error.

Thanks,
Drew

1. The parentheses around the join expression were not there for
decoration: if you remove them, the query will not work.

2. You can't join tables on TrainingTable.ClassNo, because it's always
going to be null.

3. The following might do it:

SELECT StaffCoreTable.EmployeeNo, StaffCoreTable.StaffFName,
StaffCoreTable.StaffLName, StaffCoreTable.StaffDept,
C.Title
FROM StaffCoreTable LEFT JOIN TrainingTable ON
(StaffCoreTable.EmployeeNo=TrainingTable.EmployeeNo AND
TrainingTable.ClassNo=[ClassNumber]), ClassesTaughtTable C WHERE
TrainingTable.ClassNo IS NULL AND StaffCoreTable.Active = Yes AND C.ClassNo
= [ClassNumber];