How do i use a "Not In" statement in a query?

[Appollo14]

I have an update query which should look at the values table A and then the values B and then append any entries that appear in table A but not in table B to table B. I'm trying evaluate which records are not in table B by using a "not in" clause.

The SQL for the query is as follows;
SELECT dbo_SalesAccounts.dwAccountId, dbo_SalesAccounts.szAccountCode, dbo_SalesAccounts.szName, dbo_SalesAccounts.szAddress1, dbo_SalesAccounts.szAddress2, dbo_SalesAccounts.szAddress3, dbo_SalesAccounts.szAddress4, dbo_SalesAccounts.szAddress5, dbo_SalesAccounts.szPostcode, dbo_SalesAccounts.szTelephone, dbo_SalesAccounts.szMobile, dbo_SalesAccounts.szFax, dbo_SalesAccounts.szEmail
FROM dbo_SalesAccounts
WHERE (((dbo_SalesAccounts.dwAccountId)Not In (select [Contacts]![DwAccountIdFk]from[Contacts] )));
However, no records are ever found even though i know they are there.
I'm using Access 2000.

Any ideas where i'm going wrong?

Regards,
Noel.

 

[PHV]

You don't have syntax error in your where clause ?
Try this:
WHERE dbo_SalesAccounts.dwAccountId Not In (select DwAccountIdFk from Contacts);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Appollo14]

Hi PH,
Thanks for the reply, no syntax errors. Actually used the standard design view for the query and just had "Not In (select [Contacts]![DwAccountIdFk]from[Contacts] )" in the criteria section.

Tried altering the sql statment to the bit that you suggested and unfortunately that didn't work either. To try to narrow things down i'm obviously running a select query. If i alter it to be "In (select [Contacts]![DwAccountIdFk]from[Contacts] )" that works. It seems to be the inclusion of the "Not" that is giving me the problem.
Any more ideas?

Regards,
Noel

 

[PHV]

And this ?
WHERE Not dbo_SalesAccounts.dwAccountId In (select DwAccountIdFk from Contacts);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Appollo14]

Hi PH,

thanks for persevering with this. The line you suggested gives a parameter request. Sorry.

Regards,
Noel.

 

[Appollo14]

Hi,

I cant work this out - my original query is now working fine. I've not made any changes to the query, i've just been changing copies of the query. The original query still reads:
INSERT INTO Contacts ( DwAccountIdFk, AccountCode, CompanyName, AddressLine1, AddressLine2, AddressLine3, [Town/City], County, PostalCode, CompTelephone, CompMobile, CompFax, CompEmail )
SELECT dbo_SalesAccounts.dwAccountId, dbo_SalesAccounts.szAccountCode, dbo_SalesAccounts.szName, dbo_SalesAccounts.szAddress1, dbo_SalesAccounts.szAddress2, dbo_SalesAccounts.szAddress3, dbo_SalesAccounts.szAddress4, dbo_SalesAccounts.szAddress5, dbo_SalesAccounts.szPostcode, dbo_SalesAccounts.szTelephone, dbo_SalesAccounts.szMobile, dbo_SalesAccounts.szFax, dbo_SalesAccounts.szEmail
FROM dbo_SalesAccounts
WHERE (((dbo_SalesAccounts.dwAccountId) Not In (select dwaccountidfk from contacts)));

Why would it not work here for a start and, more importantly, why is it not working on site??

Regards,
Noel

 

[Appollo14]

Hi,

I cant work this out - my original query is now working fine. I've not made any changes to the query, i've just been changing copies of the query. The original query still reads:
INSERT INTO Contacts ( DwAccountIdFk, AccountCode, CompanyName, AddressLine1, AddressLine2, AddressLine3, [Town/City], County, PostalCode, CompTelephone, CompMobile, CompFax, CompEmail )
SELECT dbo_SalesAccounts.dwAccountId, dbo_SalesAccounts.szAccountCode, dbo_SalesAccounts.szName, dbo_SalesAccounts.szAddress1, dbo_SalesAccounts.szAddress2, dbo_SalesAccounts.szAddress3, dbo_SalesAccounts.szAddress4, dbo_SalesAccounts.szAddress5, dbo_SalesAccounts.szPostcode, dbo_SalesAccounts.szTelephone, dbo_SalesAccounts.szMobile, dbo_SalesAccounts.szFax, dbo_SalesAccounts.szEmail
FROM dbo_SalesAccounts
WHERE (((dbo_SalesAccounts.dwAccountId) Not In (select dwaccountidfk from contacts)));

Why would it not work here for a start and, more importantly, why is it not working on site??

Regards,
Noel

 

[Appollo14]

Ooops sorry about the duplicated post, had error page on submit.

I think i now know why my query suddenly started to work again. I'd cleared out the data in table b and it was populated from table a, however, if i clear all data from table b, make an entry into b that is not in a then try my query i get no updates.
This looks like a relationship problem to me. Any advice appreciated.

Regards,
Noel.

 

[Appollo14]

Solved it,

FYI the problem was that the field that i was querying (dwAccountIDfk) had null values in the b table. To solve the problem i have given the field a default value of (-1) and updated all existing records that have a null value. The query now works fine and everyone is happy.
Thanks for your input.

Regards,
Noel

 

[cmmrfrds]

You can probably put a where clause in to check for null to avoid needing to update the field.

WHERE (((dbo_SalesAccounts.dwAccountId) Not In (select dwaccountidfk from contacts)))
This is the same as asking for a value list i.e.
WHERE (dbo_SalesAccounts.dwAccountId) Not In (1, 2, 3))
Null is not a value.
Something like this should work.
WHERE (((dbo_SalesAccounts.dwAccountId) Not In (select dwaccountidfk from contacts where 1 = 1 or dwaccountidfk IS NULL)))
The 1=1 should get everything with a value and the is null should pick up the records with null.