Query not returning results that should be there

[storl2]

Hey all,

I have a query that is trying to pull data from two tables, tblTemp and tblClasses, but for some reason it isn't pulling the correct data.

SELECT tblClasses.fldClassName, tblClasses.fldRequired, tblTemp.Group, tblClasses.fldClassType
FROM tblClasses LEFT JOIN tblTemp ON tblClasses.fldClassName = tblTemp.[Class Name]
WHERE (((tblClasses.fldClassName) Not In ([tblTemp]![Class Name])) AND ((tblClasses.fldRequired)="Yes"));


I'm guessing it has to do with the left join, but the query returns all nulls in the tblTemp.Group field, and I can't figure out why. There are data in tblTemp.Group that should be pulled back when the query is run. Any suggestions?

 

[Crowley16]

try switching your tables around...

...FROM tblTemp LEFT JOIN tblClasses ON tblClasses

 

[storl2]

Yeah, that doesn't return any reasults for any of the fields...

 

[PHV]

Have you tried this ?
SELECT tblClasses.fldClassName, tblClasses.fldRequired, tblTemp.Group, tblClasses.fldClassType
FROM tblClasses LEFT JOIN tblTemp ON tblClasses.fldClassName = tblTemp.[Class Name]
WHERE tblTemp.[Class Name] Is Null AND tblClasses.fldRequired="Yes";

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[storl2]

Yeah, that gives the same results. Nothing in the group field.

 

[PHV]

How are the tblTemp.Group values joined to the tblClasses.fldClassName NOT in tblTemp ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[storl2]

Here is basically what the tables look like, minus some fields that aren't included in the queries, but aren't part of keys or anything:

tblTemp:

Class Name Menu Name Attendance Group
Class1 White, Andy Yes A
Class2 White, Andy Yes A
Class3 White, Andy Yes A
Class4 White, Andy Yes A
Class5 White, Andy Yes A

tblClasses

fldClassName fldClassType fldRequired
Class1 A Yes
Class4 A Yes
Class6 B Yes
Class8 A Yes

 

[PHV]

And the expected result ?
And the answer for my question ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[storl2]

The end desired result is to find classes that the person has not taken, that are required for their group and in the master list of classes, but I'm taking it one step at a time since I couldn't make that query work. Right now it's just showing the class name, whether or not it is required, and the group/type from the two tables.

I'm not sure what you meant by your question, but here are the results that are given for the above example:

fldclassname fldrequired fldclasstype group
class6 yes b
class8 yes a

 

[PHV]

Yeah, that gives the same results. Nothing in the group field.
You not showed what you expect in the group column !

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[storl2]

Oh geez, I see what you mean now. I've been looking at the tables backwards. Thanks for giving me a kick in the head, now I know what to do.