"NOT IN" statement not working in MS Access?

[kandiko]

Hi all,
Here is my dilemma.

I have two tables, one called HS DATA and one called
ImportedHighSchools.

Both tables contain an ID code for each record.

The table ImportedHighSchools contains 554 records.

I have tried to make a query that would find all of the IDs in
ImportedHighSchools that do not appear in HS DATA. Here it is.

SELECT * FROM ImportedHighSchools WHERE ImportedHighSchools.ID NOT IN
(SELECT [HS DATA].ID FROM HS DATA)

The problem is, it doesn't return any records. And I have put in fake
IDs in table ImportedHighSchools.

But when I run the same query, just without the NOT statement, it
returns 501 records. Would it not make sense then, that if I had the
NOT IN statement in the query, it would return the remaining 53
records? I can't seem to figure this one out.
Any insight would be very helpful.

Thanks
Josh

[Keith Wilby]

ka*****@appliedtech.us wrote:

I have two tables, one called HS DATA and one called
ImportedHighSchools.

Both tables contain an ID code for each record.

The table ImportedHighSchools contains 554 records.

I have tried to make a query that would find all of the IDs in
ImportedHighSchools that do not appear in HS DATA. Here it is.

SELECT * FROM ImportedHighSchools WHERE ImportedHighSchools.ID NOT IN
(SELECT [HS DATA].ID FROM HS DATA)

You need an outer join between the tables on ID such that all records from
ImportedHighSchools are returned and those in HS DATA will show as null.

HTH - Keith.
www.keithwilby.com

[Bas Cost Budde]

ka*****@appliedtech.us wrote:

I have tried to make a query that would find all of the IDs in
ImportedHighSchools that do not appear in HS DATA. Here it is.

You can use the Unmatched Query wizard for the same function. It uses a
LEFT JOIN for the purpose.

That doesn't answer the original question, I know. Are both the ID
fields of the same datatype? What result do you get with the LEFT JOIN
strategy?
--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

[Salad]

ka*****@appliedtech.us wrote:

Hi all,
Here is my dilemma.

I have two tables, one called HS DATA and one called
ImportedHighSchools.

Both tables contain an ID code for each record.

The table ImportedHighSchools contains 554 records.

I have tried to make a query that would find all of the IDs in
ImportedHighSchools that do not appear in HS DATA. Here it is.

SELECT * FROM ImportedHighSchools WHERE ImportedHighSchools.ID NOT IN
(SELECT [HS DATA].ID FROM HS DATA)

The problem is, it doesn't return any records. And I have put in fake
IDs in table ImportedHighSchools.

But when I run the same query, just without the NOT statement, it
returns 501 records. Would it not make sense then, that if I had the
NOT IN statement in the query, it would return the remaining 53
records? I can't seem to figure this one out.
Any insight would be very helpful.

Thanks
Josh

Go to Queries/New and try the wizard for Find Unmatched. Or...

Queries/New/Design. Select HSData and ImportedHighSchools. Press Close.

Now drag a relationship line between the two ID fields. Now dlb-Click
on the line and set the relation to All ImportedHighSchools and only
those in HSData that match.

Now drag the ID fields from both tables. In the criteria row for the ID
in HSData enter
Is Null

That should do it. Run it and see.

BTW, you had a code line
(SELECT [HS DATA].ID FROM HS DATA)
and it probably should be
(SELECT [HS DATA].ID FROM [HS DATA])

I will say this. Sub-selects like yours are notoriously slow when you
have lots of records.

[kandiko]

Both the IDs are of the same datatype. Is there a reason why the IN
statement works, but the NOT IN statement doesn't?

Thanks!

[kandiko]

Hey guys! The wizard in Access did end up working! I never thought I
would ever use an Access wizard. Turns out, it worked out great,
although I would have never come up with that SQL. Can you guys give
me some insight into why I needed the LEFT JOIN and the the IS NULL?
Thanks
Josh

[kandiko]

Hey guys! The wizard in Access did end up working! I never thought I
would ever use an Access wizard. Turns out, it worked out great,
although I would have never come up with that SQL. Can you guys give
me some insight into why I needed the LEFT JOIN and the the IS NULL?
Thanks
Josh

[Bas Cost Budde]

ka*****@appliedtech.us wrote:

Both the IDs are of the same datatype. Is there a reason why the IN
statement works, but the NOT IN statement doesn't?

Do you have Nulls in either table?

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

[kandiko]

No, just some blank records.

[Dave M]

A "regular" inner join selects rows from the two tables where each table has
row(s) than have matching data in the join field. Say you have a list of
clients, where one client can have many invoices, but not all clients have
been issued invoices. A query like this

SELECT Clients.[Company Name], Invoices.[Invoice Number]
FROM Clients INNER JOIN Invoices ON Clients.[Client ID] = Invoices.Client;

selects clients and their matching invoices, but only those clients that
have had invoices issued.

A left join, like this

SELECT Clients.[Company Name], Invoices.[Invoice Number]
FROM Clients LEFT JOIN Invoices ON Clients.[Client ID] = Invoices.Client;

selects all clients, whether they have invoices associated with them or not,
and of course, the invoice number if they do.

A right join, like this

SELECT Clients.[Company Name], Invoices.[Invoice Number]
FROM Clients RIGHT JOIN Invoices ON Clients.[Client ID] = Invoices.Client;

does the converse: selects all invoices, even if they don't have clients
associated. These are usually orphaned records that need to be either need
to be deleted or correctly matched with existing clients.

However, don't overlook Salad's point regarding your original subquery:
subquerys do work, and sometimes (especially in deletion or update
situations) they do things that unmatched queries can't. I think the problem
with your subquery was that your table name consisted of two words ("HS
Data") and for the query to identify the table correctly, the two-word name
needs to be enclosed in brackets: [HS Data]
<ka*****@appliedtech.us> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...

Hey guys! The wizard in Access did end up working! I never thought I
would ever use an Access wizard. Turns out, it worked out great,
although I would have never come up with that SQL. Can you guys give
me some insight into why I needed the LEFT JOIN and the the IS NULL?
Thanks
Josh