Show what not is there

[aldeb]

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?

 

[neileg]

And the event numbers are held where? As fields in a single record? As records in a related table?

 

[aldeb]

Well the event numbers would be in a separate table or query. Below is a better example of what I am trying to do.

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.

 

[gemma-the-husky]

prepare a query to find the parts which dont have event 10
then prepare a query to find the parts which dont have event 20

etc

make sure all have the same columns

then do another query and in SQL put

select * from query1
union
select * from query2
union
select * from query3
union
select * from query4

that will do it

 

[aldeb]

Thanks I will give this a try.

 

[odin1701]

Couldn't you just use OR clauses? I guess I would need to see how the data is stored, etc.

 

[neileg]

Well the event numbers would be in a separate table or query. Below is a better example of what I am trying to do.

Still doesn't give us the structure!

Gemma's approach works if you have 4 fields in a record.