select [...] (not) exists

[ovello]

I have 2 identic tables: T1 and T2 which contain only one field NR.

T1 contains the data:
1
2
3
4
5
6
7
8
9
10
11
12

T2 contains the data:
5
6
7
8
A
B
C
D

I want to select all the records found in T1, but not found in T2. So, I wrote the following SQL query:

SELECT T1.NR
FROM T1
WHERE NOT EXISTS
(select T2.NR
from T2);

Unfortunately, this query doesn`t return any record. And the strangest thing is that the query:

SELECT T1.NR
FROM T1
WHERE NOT EXISTS
(select T2.NR
from T2);

have the same effect like:

SELECT * FROM T1,

I mean it returns all the records of T1. I mention that the query was written in Access. What I have to do? Can anybody help me? What solutions do I have? I need a query, not a VBA code!

Thank you in advance!

 

[MStef]

Hello ovello!
Look at "DemoSelectQryA2002.mdb"

 

[ScottGem]

Did you try using the Unmatched query wizard?

 

[ovello]

Thank you! It works.

 

[ScottGem]

Glad to assist

 

[ovello]

I extended the query to 2 tables with 2 fields. Even if I have identical records, the query returns some of them. Why do you think that happens?

I choosed the query (it is the same query with the one that has been generated by MStef in DemoSelectQryA2002.mdb or generated by Access using Unmatched query method):

SELECT T1.NR
FROM T1
LEFT JOIN T2 ON T2.NR=T1.NR
WHERE T2.NR IS NULL

extended to 2 tables with 2 fields. For who are interested in helping me, you can see the project at the address (I tried to upload the project but I didn`t succeed - I suppose it is too huge - 2 MB):

www.fainweb.as.ro/forum/forum.zip

Run the query FAUN and enter the date in those 2 fields that appear: 1st of June 2005. Look at the codes 30020, 30061, 30079 and so on. They should not appear because these records are identicale in that 2 tables and the query should return only unmatched queries. What do you think, how can I solve this problem?

 

[ScottGem]

I don't think you can do this with more then 2 tables and one link field.

 

[ovello]

I have to do that. In fact I did, and for some records, it works, but sometimes it doesn`t and I don`t know why...

 

[BartekSollie]

SQL statement Table1.NR and table2.NR

Try this one,

SELECT Table1.NR
FROM TABLE1
WHERE (table1.nr NOT IN (SELECT Table2.NR FROM Table2));

The results are de different values of NR, except for the values (a,b,c and d)

 

[will1128]

Does anyone know how to do this using queries instead of tables in access?

 

[ScottGem]

Does anyone know how to do this using queries instead of tables in access?

Not sure what you mean, but you can use a query instead of a table within a SQL statement. So the SQL shown in this thread should work if you just substitute querynames.

Hope this helps,
Scott<>
ScottGem's Blog
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA