Show what is not there

[netrusher]

I have Part Numbers and Events. Each Part Number should have 4 different
Event Numbers.

10
20
40
80

I would like to have a query that would show me which of these part numbers
that do not have Event 10, 20, 40 or 80 with it and for it to show which
of these four numbers it does not have.

This hard to explain.
Can anyone tell how to do this?

 

[PHV]

A starting point:
SELECT [Part Number], Count(*) AS countOfEvents
FROM yourTable
WHERE Event In(10,20,40,80)
GROUP BY [Part Number]
HAVING Count(*) <> 4

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

 

[netrusher]

Thanks for the starting point. I am not sure the result gives me what I need.

PartNbr 111111 = Events 10,20,40,80
PartNbr 222222 = Events 10,40,80
PartNbr 333333 = Events 40,80

I want the results of the query to show:

PartNbr 222222 = 20
PartNbr 333333 = 10,20

This is showing me what Events the PartNbr's are not mapped
to any of the four Events they should be mapped to.

All the PartNbr's should be mapped to 10,20,40,80
Hope this makes sense.

 

[PHV]

You have one field named Events with a value like '10,40,80' ?

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

 

[netrusher]

Yes

 

[PHV]

SELECT *
FROM yourTable
WHERE Events <> '10,20,40,80'

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