Query to find things that don't exist

[sharpnova]

This seems like it should be very simple but I'm drawing a blank. I've tried a few different things, but none of them worked, nor did I expect them to.

I have two tables:

Files: FileNumber
Documents: FileNumber, DocType

There are a bunch of files and each file has a bunch of documents.

There are a bunch of different document types, for example (but not limited to):
P3, P4, A1, A2, A3, GX90, GX110, etc.

I want to make a select query that will return all files that do NOT have a document with doctype A2.

I realize this is more complicated than it seems. Might require multiple queries.

But I'm clueless as to how to proceed.

 

[jdraw]

I don't think you have shown us all the related info.

I see Tables

File
Document
DocumentType
FileDocumentLocation


Do you have a picture of your Tables and relationships?
Have you Normalized your table structures?

 

[boblarson]

The SQL for what you appear to want is:

SELECT Files.FileNumber, Documents.FileTyp, Documents.FileNumber
FROM Documents RIGHT JOIN Files ON Documents.FileNumber = Files.FileNumber
WHERE (((Documents.FileTyp)<>"A2")) OR (((Documents.FileNumber) Is Null));

That is, if FileNumber is truly a number and not text.

 

[sharpnova]

Using inline SQL was how I got this to work.

Basically something like this:

First query:

select all documents where doctype = A2

second query:

select everything with this criteria on the doctype:

Not In (select doctype from [first table])


I didn't know you could use SQL in a criteria that way. But when I looked at the SQL view it made sense.