Im sure this is easy....

[Pommy_g]

Hi, can anyone help me with this prb

ive got 2 tables, A and B, which are related, table B contains records from table A.


Now i need a query to display the records from table A that are NOT in table B.



Thanks in Advance

 

[Rick Brandt]

Hi, can anyone help me with this prb

ive got 2 tables, A and B, which are related, table B contains records from table A.

Now i need a query to display the records from table A that are NOT in table B.

SELECT * FROM TableA
LEFT JOIN TableB ON TableA.KeyField = TableB.KeyField
WHERE TableB.KeyField Is Null

 

[Rick Brandt]

"Rick Brandt" <[email protected]> wrote in message

from table
A. table B.

thanks for reply

not too sure if i understand that fully though, am i correct in thinking:
/ SELECT * FROM TableA
relationship type-[
\LEFT JOIN TableB ON TableA.KeyField = TableB.KeyField

query criteria - WHERE TableB.KeyField Is Null

Open a new query, select no tables and then switch to SQL view. Paste my
example SQL exactly and then replace the KeyField and TableNames with the
appropriate entries for your tables. Switch to design view to see what this
looks like in the query design grid.

 

[Pommy_g]

whenever i try to save it it comes up with this:
"Syntax error (missing operator) in query expression table A.keyfield
= Table B.Key field'

? do both fields have to be primray key or something?

appreciate the help

"Rick Brandt" <[email protected]> wrote in message

from table
A.

Now i need a query to display the records from table A that are NOT in table B.

thanks for reply

not too sure if i understand that fully though, am i correct in thinking:
/ SELECT * FROM TableA
relationship type-[
\LEFT JOIN TableB ON TableA.KeyField = TableB.KeyField

query criteria - WHERE TableB.KeyField Is Null

Open a new query, select no tables and then switch to SQL view. Paste my
example SQL exactly and then replace the KeyField and TableNames with the
appropriate entries for your tables. Switch to design view to see what this
looks like in the query design grid.

 

[Rick Brandt]

whenever i try to save it it comes up with this:
"Syntax error (missing operator) in query expression table A.keyfield
= Table B.Key field'

? do both fields have to be primray key or something?

If your table names have spaces in them (a poor practice) then they need to be
enclosed in brackets.

EX: [table A].keyfield