tricky unmatched query

[odun]

Hello all:

In table 1 I have some document #s
e.g.
320
321
322
170
171
172
151
152

In table 2 I have some document #s e.g.
170
171
151

I would like to create a query, that will look at the document #s starting with 17* and 15* in table 1 and look for it in table 2, if the document #s do not exist, I would like the query to spill out the following:

320
321
322
172
152

Is this possible?

Thanks so much in advance for helping!

 

[Smart]

Have you tried using the query wizard to create an unmatched query

 

[mresann]

I don't fully understand. From what I see of the resulting recordset, you are returning all records from table 1 that is not present in Table 2. That much I got. Here is my query for that:

SELECT Table1.DocID
FROM Table1
WHERE (((Table1.DocID) Not In (SELECT DocID FROM Table2)));

What I don't understand are your qualifiying requirements. Can yo be more specific?

 

[mresann]

I Got It!

OK, I think you wanted a query to return a recordset of all records FROM Table1 that are NOT IN Table2, IF there are no ducuments currently in Table2 which contain document numbers starting with "15" or "17". Sorry, I had to read a litte more into it.

Here is the query:
.

SELECT DISTINCT Table1.DocID
FROM Table1, Table2
WHERE (((Table1.DocID) Not In (SELECT Table2.DocID 
FROM Table2)) AND ((IIf(IIf((SELECT Count(*)
FROM Table2
WHERE left(DocID,2)= "15"),1,0)+IIf((SELECT Count(*)
FROM Table2
WHERE left(DocID,2)= "17"),1,0)=2,"False","True"))="False"));

In both tables, the name of the field containing your document name is "DocID". They are TEXT fields, incidentally. If your fields are NUMBER fields, then you have to change

left(DocID,2) to CStr(left(DocID,2))

in both places in the two final WHERE clauses.