Not Equal

[kbreiss]

I'm wanting to write this query that looks at both tables and only returns records that don't match...not for sure how to do this..


SELECT tblCombined.DLID, tblCombined.DUP, tblCombined.QRYRAN
FROM Susp_Master INNER JOIN tblCombined ON (Susp_Master.DUP = tblCombined.DUP) AND (Susp_Master.DLID = tblCombined.DLID);
________
GLASS VAPORIZER BOWL

 

[Murli]

Try this:

Syntax:

Select Table1.Field1, Table1.Field2, Table1.Field3
from Table1 LEFT JOIN Table2 ON (Table1.Field1 = Table2.Field1) And
(Table1.Field2 = Table2.Field2) WHERE (((Table2.Field1) is Null))

Union

Select Table2.Field1, Table2.Field2, Table2.Field3
from Table1 RIGHT JOIN Table2 ON (Table1.Field1 = Table2.Field1) And
(Table1.Field2 = Table2.Field2) WHERE (((Table1.Field1) is Null));

 

[kbreiss]

Thanks

Got it working.....
________
Ipad cases

 

[kuniyo]

Not In

i wanted to write a sql that select field1 from table1 where field1 not in select field1,field2,field3 from table2. anyone can help ?

SELECT RoomNo
FROM table1 AS r
WHERE RoomType ="Suite" and ViewPreference="pref" and RoomStatus="status" and SmokingRoom="option" and RoomNo Not IN (Select RoomNo,RoomNo2, RoomNo3, RoomNo4, RoomNo5 from table2 where expchkin between 8/25/2008 and 8/27/2008 and expchkout between 8/25/2008 and 8/27/2008 and RoomType ="Suite" )

 

[boblarson]

i wanted to write a sql that select field1 from table1 where field1 not in select field1,field2,field3 from table2. anyone can help ?

SELECT RoomNo
FROM table1 AS r
WHERE RoomType ="Suite" and ViewPreference="pref" and RoomStatus="status" and SmokingRoom="option" and RoomNo Not IN (Select RoomNo,RoomNo2, RoomNo3, RoomNo4, RoomNo5 from table2 where expchkin between 8/25/2008 and 8/27/2008 and expchkout between 8/25/2008 and 8/27/2008 and RoomType ="Suite" )

Two things:

1. You should start your own thread for this.

2. You need to normalize your database before moving further. Repeating fields (RoomNo, RoomNo2, RoomNo3, etc.) are a sign that you do NOT have it normalized and therefore you are going to run into major hurdles with trying to query this.

See here for more about normalization:
http://support.microsoft.com/kb/283878

and Access basics:
http://www.accessmvp.com/strive4peace/