Excluded Results from a join

[Guest]

Two table exist, there are some records in both tables that are the same, but
both tables hold other characteristics relating to those records.

e.g both tables hold a Transaction Code Field, I need to exclude the
Overlapping records from Table A and return the remaining records from this
table......
How do I do this?

 

[Baz]

SELECT TableA.* FROM TableA LEFT JOIN TableB ON TableA.[Transaction Code] =
TableB.[Transaction Code] WHERE TableB.[Transaction Code] IS NULL

 

[Guest]

Thank you very much Baz, that worked perfectly, just to be a bit more
specific, lect say you wanted the join key to be more that one field i.e.
Trans_Date also how should the syntax be then

SELECT TableA.* FROM TableA LEFT JOIN TableB ON TableA.[Transaction Code] =
TableB.[Transaction Code] WHERE TableB.[Transaction Code] IS NULL

Two table exist, there are some records in both tables that are the same, but
both tables hold other characteristics relating to those records.

e.g both tables hold a Transaction Code Field, I need to exclude the
Overlapping records from Table A and return the remaining records from this
table......
How do I do this?

 

[Guest]

It's OK Baz I used was able to alter the syntax you gave very kindly and I
now have the following which gives the correct results:

SELECT TBL_TRANS_LOAD_RECORDS.*
FROM TBL_TRANS_LOAD_RECORDS LEFT JOIN TBL_CARD_LOAD_RECORDS ON
(TBL_TRANS_LOAD_RECORDS.[CARD NUM] = TBL_CARD_LOAD_RECORDS.[CARD NUM]) AND
(TBL_TRANS_LOAD_RECORDS.TRANSACTION = TBL_CARD_LOAD_RECORDS.TRANS) AND
(TBL_TRANS_LOAD_RECORDS.[TRANS DATE] = TBL_CARD_LOAD_RECORDS.[TRANS DATE])
WHERE (((TBL_CARD_LOAD_RECORDS.[TRANS DATE]) Is Null) AND
((TBL_CARD_LOAD_RECORDS.[CARD NUM]) Is Null) AND
((TBL_CARD_LOAD_RECORDS.TRANS) Is Null));


Thank you Again.....

Thank you very much Baz, that worked perfectly, just to be a bit more
specific, lect say you wanted the join key to be more that one field i.e.
Trans_Date also how should the syntax be then

SELECT TableA.* FROM TableA LEFT JOIN TableB ON TableA.[Transaction Code] =
TableB.[Transaction Code] WHERE TableB.[Transaction Code] IS NULL

Two table exist, there are some records in both tables that are the same, but
both tables hold other characteristics relating to those records.

e.g both tables hold a Transaction Code Field, I need to exclude the
Overlapping records from Table A and return the remaining records from this
table......
How do I do this?

 

[Baz]

Well done! Glad to have been of assistance.

It's OK Baz I used was able to alter the syntax you gave very kindly and I
now have the following which gives the correct results:

SELECT TBL_TRANS_LOAD_RECORDS.*
FROM TBL_TRANS_LOAD_RECORDS LEFT JOIN TBL_CARD_LOAD_RECORDS ON
(TBL_TRANS_LOAD_RECORDS.[CARD NUM] = TBL_CARD_LOAD_RECORDS.[CARD NUM]) AND
(TBL_TRANS_LOAD_RECORDS.TRANSACTION = TBL_CARD_LOAD_RECORDS.TRANS) AND
(TBL_TRANS_LOAD_RECORDS.[TRANS DATE] = TBL_CARD_LOAD_RECORDS.[TRANS DATE])
WHERE (((TBL_CARD_LOAD_RECORDS.[TRANS DATE]) Is Null) AND
((TBL_CARD_LOAD_RECORDS.[CARD NUM]) Is Null) AND
((TBL_CARD_LOAD_RECORDS.TRANS) Is Null));


Thank you Again.....

Thank you very much Baz, that worked perfectly, just to be a bit more
specific, lect say you wanted the join key to be more that one field i.e.
Trans_Date also how should the syntax be then

SELECT TableA.* FROM TableA LEFT JOIN TableB ON TableA.[Transaction Code] =
TableB.[Transaction Code] WHERE TableB.[Transaction Code] IS NULL

Two table exist, there are some records in both tables that are the same,
but
both tables hold other characteristics relating to those records.

e.g both tables hold a Transaction Code Field, I need to exclude the
Overlapping records from Table A and return the remaining records from
this
table......
How do I do this?