Is Null Criteria - not working!

[mattpearcey]

I want to bring up records in a query using the Is Null criteria. In other words, i want to see which records i have that i need to enter data into. Now, the query is spanning across two tables, which is where i think the problem sits. The main table contains the name, then i have a software details table that has a one to many relationship with the main table. I want the query to show me names where i have no information in the other table.

When i runthe query, using the IS Null criteria on the second table field, the query shows me no records. But i know for a fact that there are over 150 records in there where this is the case?

Can someone tell me where i am going wrong? Thank you for your help already.

Matt Pearcey

 

[purpleflash]

Q1 if you wish to find main table items where there are no detail table records then you need to make the query a LEFT Join Query (Show all data from main and only those from detail where the fields meet the criteria) so if
MAIN has
JUNK1
JUNK2
JUNK3

and there are records in DETAIL associated with
JUNK1
JUNK2
but NOT JUNK3

Then the above will return JUNK3

SQL looks like this
SELECT MAIN.Junk, DETAIL.DETAIL
FROM MAIN LEFT JOIN DETAIL ON MAIN.Junk = DETAIL.JUNK
WHERE (((DETAIL.DETAIL) Is Null));

but its easy to build in the QBE grid just double click on the relationship and choose the appropriate one.

PURPLEFLASH

 

[Aivars]

Hi, Matt!
Use LEFT JOIN or RIGHT JOIN type in the query (Double-click on relation line and select join type)

Example:
SELECT MyTable.*
FROM MyTable LEFT JOIN MyTable AS MyTable_1 ON MyTable.FieldID= MyTable_1.FieldID
WHERE MyTable_1.CriteriaField Is Null;

Aivars

 

[rajeevnandanmishra]

Hi Matt,

I did not understand the requirement but one of following query will solve your problem:
1)SELECT * FROM mainTable WHERE name not in
(SELECT name FROM childTable)

2)SELECT * FROM mainTable WHERE name in
(SELECT name FROM childTable WHERE ISNULL(detail))

Please let me know, which query had worked for you.