Need help with SQL query find tasks of different numbers 1

[DougP]

I have a table which has tasks. Each job can have one or more tasks.
One task number 33 has a complimenting task 34.
I want to find all jobs that have a task 33 but not an accompanying task 34.

Job task
1234 33
1234 34
4567 33
7837 33
7837 34
7829 33

I want to see results like so which do not have a 34.
4567
7829

here is my sql which returns the same number of records with the 34 part or without it.

SELECT ProjectTrackingItems.[Project Number], ProjectTrackingItems.[Tracking CodeNumber]
FROM ProjectTrackingItems
WHERE (((ProjectTrackingItems.[Tracking CodeNumber])=33 And (ProjectTrackingItems.[Tracking CodeNumber])<>34));

TIA

DougP
< I love mine

http://www.robots2000.com

 

[PHV]

SELECT [Project Number], [Tracking CodeNumber]
FROM ProjectTrackingItems
WHERE [Tracking CodeNumber]=33
AND [Project Number] NOT IN (SELECT [Project Number] FROM ProjectTrackingItems WHERE [Tracking CodeNumber]=34)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DougP]

Unbelievable PHV,

here are the real numbers, it found two
Project Number Tracking CodeNumber
070160IA 33
070872ASN 33

Many thanks once again!!!

Also I want to know how to do this. This is the one stumbling blocks I have in programming Access, complicated SQL.
I understand it after you created it but I have no idea where to begin.
I did get it done in a round about way while I was waiting for an answer. But I created two make table queries one had the 33’s the other had the 34’s , then I created an unmatched query between the two tables looking at Project number.


DougP
< I love mine

http://www.robots2000.com