How query for Records in table 1, but not in table 2 ?

I am a very much novice at Access. Please help.

Have two tables with data about servers from two sources.

Table1 has HOSTNAME, SERIAL, IP

Table2 has HOSTNAME, SERIAL, IP

I need
Hostnames that appear on Table1, but not on Table2.

I put the fields of Table1 in a query. In criteria in first column under HOSTNAME, I put
<>All (SELECT [HOSTNAME] FROM [Table2] WHERE [HOSTNAME] = HOSTNAME)

But get nothing. I think because I am joining on this field and/or because I am selecting for hostnames the same where I am looking for hostnames different, but when I take the where clause out, I get syntax error.

Try this

SELECT [Table 1].hostname, [Table 1].ip
FROM [Table 1] LEFT JOIN [Table 2] ON [Table 1].hostname = [Table 2].hostname
WHERE ((([Table 2].hostname) Is Null));

Thanks Corrupter for your reply. We are close. I removed the Where because I don't want only when NULL in table2.

What I end up with is all in table1 that ARE in table2. Guess I want the converse - those in table1 that do not appear in table2.

Would I just use NOT or !=? Will try some of that stuff.

I do not understand you.

If you want to select all those records are in table1 but not in table2 use the query I told you. That query is a simple unmatch query.

If you want to select thosre are in both tables simply modify the where clause like WHERE ((([Table 2].hostname) = [Table 1].hostname)))


The where clause you have to use otherwise the server won't be able to narrow your selection.


To learn more about queries try to use query wizard in Access (Select queries and clik on "New").

Sorry but I did not answer your question.

Since in the query we joined the two tables with identical columns when the record pointer will be moved in table1 to a record doesn't appear in table2 the record pointter will point toa NULL in table2 since the record (or any records matching the criteria we defined) does not exist in table2 therefore isn'T it logical to use Is Null statement in where clause?

Of course you can use <> or != but doesn't make any sense.

Corruptor

Was able to tweak it up to get needed result using NOT IN select from (table2) kind of thing. The IN seems to make the pointer advance through the select subquery comparing each result to the main query condition.

Thanks for your help. I probably confused you with my wording. But you have brought about the solution to the problem.

Thank you...