Filtering unique values

[DDONNI]

Hi Guys i'm to run a query to sort unique data between 2 tables. The SQL command i'm using is

SELECT [EBAN].[BANFN]
FROM EBAN2 INNER JOIN EBAN ON [EBAN2].[ID]=[EBAN].[ID]
WHERE ((([EBAN2]![BANFN])<>[EBAN]![BANFN]));

This works fine as it filters out the unique values but, it filters out all unique values from both tables when, i only want the query to filter out the unique values that only exist in EBAN2 table and not in EBAN as well. e.g.

EBAN
1
2
3
4
5
6
7

EBAN2

6
7
8
9
10

So the result would be:

8
9
10

I've tried changing the SQL query i've tried using the wizard but i can't seem to get it to do this. Can anyone help me. I'm sure this is an easy problem, its just i'm not thinking along the right lines.

 

[Len Boorman]

Why not use a UNION ALL query to basically add the values and then a non duplicates query on the UNIOn


Len B


No this would not work since it would just give you a list of non duplicated

Didn't read your question properly

L

 

[DDONNI]

I tried using the right join in the statement but it doesn't seem to work either. I looked it up and in theory it should work but it doesn't. sure whats wrong whether i've got formatting wrong or something but i'm sure its something really simple.

Can anyone else help on this matter please.

 

[Len Boorman]

More thoughts

Query 1

Select EBAN data

UNION ALL
Select EBAN data

UNION ALL

Select EBAN2 data


Now run a find non duplicates query over Query 1

Works providing you want the data exactly as you describe in your original question.

HTH

Len B

 

[Jon K]

The attached database contains a query. When the query is run, it will produce:-

ID	BANFN
 1	    8
 1	    9
 1	   10
 2	    9
 2	   10

Not sure if it's what you wanted though (as your query contained an ID field but your sample data didn't.)
.