I've had this problem before, and no one seemed to know what was up, but let's try this again.

I have a database in which two queries suddenly ceased to function. Nothing seems to cause it to stop working, everything is normal. The queries will suddenly return empty recordsets (only column headings will display, nothing else). Here is the first query:

SELECT [not yet called].CNYU_ID, [not yet called].StudentFirstName, [not yet called].StudentLastName
FROM [not yet called] LEFT JOIN [People with No Numbers] ON [not yet called].CNYU_ID = [People with No Numbers].CNYU_ID
WHERE ((([People with No Numbers].CNYU_ID) Is Null));

Here is the second that does not work:

SELECT [tracking file 2].CNYU_ID, [tracking file 2].StudentFirstName, [tracking file 2].StudentLastName, [tracking file 2].[Search needed]
FROM [tracking file 2]
WHERE ((([tracking file 2].[Search needed])=Yes))
UNION SELECT [not yet called].CNYU_ID, [not yet called].StudentFirstName, [not yet called].StudentLastName, [not yet called].[Search needed]
FROM [not yet called] LEFT JOIN [phone numbers] ON [not yet called].CNYU_ID = [phone numbers].CNYU_ID
WHERE ((([phone numbers].CNYU_ID) Is Null));


In the case of the second, it does not return an empty recordset but returns fewer results than it should. When working properly, it should return about 500; currently, it's only returning about 100.

For the first query, which returns empty, I have the problem pinpointed to this query, titled "not yet called" (which is involved in the JOIN):

SELECT [tracking file 2].CNYU_ID, [tracking file 2].StudentFirstName, [tracking file 2].StudentLastName, [tracking file 2].[Search needed]
FROM [tracking file 2]
WHERE ((([tracking file 2].CNYU_ID) Not In (SELECT [tracking file 2].CNYU_ID FROM [tracking file 2] WHERE [tracking file 2].ROUND = 99) And ([tracking file 2].CNYU_ID) Not In (SELECT [tracking file 2].CNYU_ID FROM [tracking file 2] WHERE [tracking file 2].[ROUND] = 5)))
GROUP BY [tracking file 2].CNYU_ID, [tracking file 2].StudentFirstName, [tracking file 2].StudentLastName, [tracking file 2].[Search needed];

I suspect that the problem is in the "Not In" statement. I'm sure that my db is corrupted, since this usually works fine but will spontaneously stop working (and then start again, as was the case before). The problem is that we lose days of work when these queries suddenly stop working.



I did try to import all of these into a new db, but the problem persists.