Exclude sime recirds from query result (1 Viewer)

[LjushaMisha]

exclude some records from query​

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.

 

[arnelgp]

Have you tried it yet:

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));

result:

ID	Name	Level	IDSuperior
4	Bad	0
17	Wardrobe	0
15	Shelf	3	10
20	Bottom	2	12

 

[Gasman]

Crossposted https://www.accessforums.net/showthread.php?t=90976