exclude some records from query

[MishaLjusha]

Hi.
I have a table called "tblParts". Some records from this table are as follows.
Id Name Level IdSuperior
04 Bad 0 Null
17 Wardrobe 0 Null
15 Shelf 3 10
19 Side Board 1 22
20 Bottom 2 12
19 Side Board 0 Null

Point: Id 19 is correctly entered twice.
Question: How to "make" query where ALL id's where level is 0 and id superior is Null are NOT in the result of query? It means all entries of product Id 19 should be excluded from query result if one of entries has level 0 AND idSuperior Null.

[Welshgasman]

Open the query editor in design mode and add the field and criteria required.
You will use a Select query.

https://www.youtube.com/results?sear...s+select+query

[MishaLjusha]

Sorry,
if write criteria LEVEL = 0 and IdSuperior = Null I wil stil get product with Id = 19 and LEVEL = 1 and IdSuperior = 22.
That would be easy.
But I want ALL id =19 to be excluded if one of them has LEVEL = 0 and IdSuperior = Null.
See the difference?

[MishaLjusha]

Sorry,
if write criteria LEVEL = 0 and IdSuperior = Null I wil stil get product with Id = 19 and LEVEL = 1 and IdSuperior = 22.
That would be easy.
But I want ALL id =19 to be excluded if one of them has LEVEL = 0 and IdSuperior = Null.
See the difference?

[jojowhite]

maybe something like:

SELECT tblParts.* FROM tblParts WHERE ID NOT IN (SELECT tblParts.ID
FROM tblParts WHERE 
(DCOUNT("1","TBLPARTS","ID=" & [ID] & " AND ISNULL(IDSUPERIOR)=TRUE")>0 AND  
DCOUNT("1","TBLPARTS","ID=" & [ID] & " AND ISNULL(IDSUPERIOR)=FALSE")>0))

[MishaLjusha]

thx.
I will try first thing I come home😉

[Welshgasman]

Crossposted https://www.access-programmers.co.uk...result.334252/