473,325 Members | 2,805 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

Nested query bug?

Hi

I have the following query;

SELECT Clients.ID, Clients.Company, Clients.[Company 2]
FROM Clients
WHERE Clients.[Company 2] Not In (SELECT [Company] FROM [Clients])

When I do not use the keyword Not it works but not with keyword Not. Is this
a bug? I am using access 2000 sp3.

Thanks

Regards
Aug 18 '06 #1
6 1861
Obvious question first, are you sure there are records which match the
criteria? I've just tried your SQL and it works fine.

I suspect, though, that you are expecting this to show data for Clients
where the [Company 2] field is Null, this won't happen.

The SQL you have will show Clients where the data in the [Company 2] field
does not exist in the Company field

I suspect you need something like this ...
SELECT
Clients.ID, Clients.Company, Clients.[Company 2]
FROM
Clients
WHERE (((
Clients.[Company 2]) Not In (SELECT [Company] FROM [Clients])))
OR
(((Clients.[Company 2]) Is Null));

.... which will show Clients where the data in the [Company 2] field does not
exist in the Company field and also the Clients where there is no data in
the [Company 2] field.

or possibly just ...
SELECT
Clients.ID, Clients.Company, Clients.[Company 2]
FROM
Clients
WHERE
(((Clients.[Company 2]) Is Null));

.... which will show Clients where there is no data in the [Company 2] field.

--

Terry Kreft
"John" <Jo**@nospam.infovis.co.ukwrote in message
news:tv******************************@pipex.net...
Hi

I have the following query;

SELECT Clients.ID, Clients.Company, Clients.[Company 2]
FROM Clients
WHERE Clients.[Company 2] Not In (SELECT [Company] FROM [Clients])

When I do not use the keyword Not it works but not with keyword Not. Is
this
a bug? I am using access 2000 sp3.

Thanks

Regards


Aug 18 '06 #2
It does not give any result i.e. no records while I know there should be
some. One example is below;

Company ID Company 2
Liffe 5483 Simon Pleydell

Simon Pleydell does not exist in the clients.company field any where in the
table and I have verified this by manually trying to find 'Simon Pleydell'
in the company field. Any ideas why it would not work? I am not looking for
nulls.

Thanks

Regards

"Terry Kreft" <te*********@mps.co.ukwrote in message
news:EN******************************@eclipse.net. uk...
Obvious question first, are you sure there are records which match the
criteria? I've just tried your SQL and it works fine.

I suspect, though, that you are expecting this to show data for Clients
where the [Company 2] field is Null, this won't happen.

The SQL you have will show Clients where the data in the [Company 2] field
does not exist in the Company field

I suspect you need something like this ...
SELECT
Clients.ID, Clients.Company, Clients.[Company 2]
FROM
Clients
WHERE (((
Clients.[Company 2]) Not In (SELECT [Company] FROM [Clients])))
OR
(((Clients.[Company 2]) Is Null));

... which will show Clients where the data in the [Company 2] field does
not
exist in the Company field and also the Clients where there is no data in
the [Company 2] field.

or possibly just ...
SELECT
Clients.ID, Clients.Company, Clients.[Company 2]
FROM
Clients
WHERE
(((Clients.[Company 2]) Is Null));

... which will show Clients where there is no data in the [Company 2]
field.

--

Terry Kreft
"John" <Jo**@nospam.infovis.co.ukwrote in message
news:tv******************************@pipex.net...
>Hi

I have the following query;

SELECT Clients.ID, Clients.Company, Clients.[Company 2]
FROM Clients
WHERE Clients.[Company 2] Not In (SELECT [Company] FROM [Clients])

When I do not use the keyword Not it works but not with keyword Not. Is
this
>a bug? I am using access 2000 sp3.

Thanks

Regards



Aug 18 '06 #3
Could you join the 2 tables on the names, change it to an outer join, and
then use Null as the criteria to select those that don't match?

Something like this:
SELECT Clients.ID, Clients.Company, Clients.[Company 2]
FROM Clients LEFT JOIN Clients AS Dupe
ON Clients.Company = Dupe.[Company 2]
WHERE Dupe.ClientID Is Null;

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"John" <Jo**@nospam.infovis.co.ukwrote in message
news:Ct******************************@pipex.net...
It does not give any result i.e. no records while I know there should be
some. One example is below;

Company ID Company 2
Liffe 5483 Simon Pleydell

Simon Pleydell does not exist in the clients.company field any where in
the
table and I have verified this by manually trying to find 'Simon Pleydell'
in the company field. Any ideas why it would not work? I am not looking
for nulls.

Thanks

Regards

"Terry Kreft" <te*********@mps.co.ukwrote in message
news:EN******************************@eclipse.net. uk...
>Obvious question first, are you sure there are records which match the
criteria? I've just tried your SQL and it works fine.

I suspect, though, that you are expecting this to show data for Clients
where the [Company 2] field is Null, this won't happen.

The SQL you have will show Clients where the data in the [Company 2]
field
does not exist in the Company field

I suspect you need something like this ...
SELECT
Clients.ID, Clients.Company, Clients.[Company 2]
FROM
Clients
WHERE (((
Clients.[Company 2]) Not In (SELECT [Company] FROM [Clients])))
OR
(((Clients.[Company 2]) Is Null));

... which will show Clients where the data in the [Company 2] field does
not
exist in the Company field and also the Clients where there is no data in
the [Company 2] field.

or possibly just ...
SELECT
Clients.ID, Clients.Company, Clients.[Company 2]
FROM
Clients
WHERE
(((Clients.[Company 2]) Is Null));

... which will show Clients where there is no data in the [Company 2]
field.

--

Terry Kreft
"John" <Jo**@nospam.infovis.co.ukwrote in message
news:tv******************************@pipex.net.. .
>>Hi

I have the following query;

SELECT Clients.ID, Clients.Company, Clients.[Company 2]
FROM Clients
WHERE Clients.[Company 2] Not In (SELECT [Company] FROM [Clients])

When I do not use the keyword Not it works but not with keyword Not. Is
this
>>a bug? I am using access 2000 sp3.

Aug 18 '06 #4
Senior moment Allen?

I think you meant

SELECT Clients.ID, Clients.Company, Clients.[Company 2]
FROM Clients LEFT JOIN Clients AS Dupe ON Clients.[Company 2] = Dupe.Company
WHERE (((Dupe.ID) Is Null));

--

Terry Kreft
"Allen Browne" <Al*********@SeeSig.Invalidwrote in message
news:44*********************@per-qv1-newsreader-01.iinet.net.au...
Could you join the 2 tables on the names, change it to an outer join, and
then use Null as the criteria to select those that don't match?

Something like this:
SELECT Clients.ID, Clients.Company, Clients.[Company 2]
FROM Clients LEFT JOIN Clients AS Dupe
ON Clients.Company = Dupe.[Company 2]
WHERE Dupe.ClientID Is Null;

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"John" <Jo**@nospam.infovis.co.ukwrote in message
news:Ct******************************@pipex.net...
It does not give any result i.e. no records while I know there should be
some. One example is below;

Company ID Company 2
Liffe 5483 Simon Pleydell

Simon Pleydell does not exist in the clients.company field any where in
the
table and I have verified this by manually trying to find 'Simon
Pleydell'
in the company field. Any ideas why it would not work? I am not looking
for nulls.

Thanks

Regards

"Terry Kreft" <te*********@mps.co.ukwrote in message
news:EN******************************@eclipse.net. uk...
Obvious question first, are you sure there are records which match the
criteria? I've just tried your SQL and it works fine.

I suspect, though, that you are expecting this to show data for Clients
where the [Company 2] field is Null, this won't happen.

The SQL you have will show Clients where the data in the [Company 2]
field
does not exist in the Company field

I suspect you need something like this ...
SELECT
Clients.ID, Clients.Company, Clients.[Company 2]
FROM
Clients
WHERE (((
Clients.[Company 2]) Not In (SELECT [Company] FROM [Clients])))
OR
(((Clients.[Company 2]) Is Null));

... which will show Clients where the data in the [Company 2] field
does
not
exist in the Company field and also the Clients where there is no data
in
the [Company 2] field.

or possibly just ...
SELECT
Clients.ID, Clients.Company, Clients.[Company 2]
FROM
Clients
WHERE
(((Clients.[Company 2]) Is Null));

... which will show Clients where there is no data in the [Company 2]
field.

--

Terry Kreft
"John" <Jo**@nospam.infovis.co.ukwrote in message
news:tv******************************@pipex.net...
Hi

I have the following query;

SELECT Clients.ID, Clients.Company, Clients.[Company 2]
FROM Clients
WHERE Clients.[Company 2] Not In (SELECT [Company] FROM [Clients])

When I do not use the keyword Not it works but not with keyword Not.
Is
this
a bug? I am using access 2000 sp3.


Aug 18 '06 #5

OK, that's strange, I actually tested the SQL against data and your original
SQl certainly should have worked the way you want it to. BTW I'muing Acess
2003.

Allan Browne has suggested using a Left Outer join, e.g.
SELECT
Clients.ID, Clients.Company, Clients.[Company 2]
FROM
Clients
LEFT JOIN
Clients AS Dupe
ON
Clients.[Company 2] = Dupe.Company
WHERE
(((Dupe.ID) Is Null));

the only problem with this is that it will bring through the companies where
the [Company 2] Field Is Null as well, which is a larger set than you want.

You could try ...
SELECT
Clients.ID, Clients.Company, Clients.[Company 2]
FROM
Clients
LEFT JOIN
Clients AS Dupe
ON
Clients.[Company 2] = Dupe.Company
WHERE
(((Dupe.ID) Is Null))
AND
Clients.[Company 2] Is Not Null;

.... which should work as you want.

--

Terry Kreft
"John" <Jo**@nospam.infovis.co.ukwrote in message
news:Ct******************************@pipex.net...
It does not give any result i.e. no records while I know there should be
some. One example is below;

Company ID Company 2
Liffe 5483 Simon Pleydell

Simon Pleydell does not exist in the clients.company field any where in
the
table and I have verified this by manually trying to find 'Simon Pleydell'
in the company field. Any ideas why it would not work? I am not looking
for
nulls.

Thanks

Regards

"Terry Kreft" <te*********@mps.co.ukwrote in message
news:EN******************************@eclipse.net. uk...
Obvious question first, are you sure there are records which match the
criteria? I've just tried your SQL and it works fine.

I suspect, though, that you are expecting this to show data for Clients
where the [Company 2] field is Null, this won't happen.

The SQL you have will show Clients where the data in the [Company 2]
field
does not exist in the Company field

I suspect you need something like this ...
SELECT
Clients.ID, Clients.Company, Clients.[Company 2]
FROM
Clients
WHERE (((
Clients.[Company 2]) Not In (SELECT [Company] FROM [Clients])))
OR
(((Clients.[Company 2]) Is Null));

... which will show Clients where the data in the [Company 2] field does
not
exist in the Company field and also the Clients where there is no data
in
the [Company 2] field.

or possibly just ...
SELECT
Clients.ID, Clients.Company, Clients.[Company 2]
FROM
Clients
WHERE
(((Clients.[Company 2]) Is Null));

... which will show Clients where there is no data in the [Company 2]
field.

--

Terry Kreft
"John" <Jo**@nospam.infovis.co.ukwrote in message
news:tv******************************@pipex.net...
Hi

I have the following query;

SELECT Clients.ID, Clients.Company, Clients.[Company 2]
FROM Clients
WHERE Clients.[Company 2] Not In (SELECT [Company] FROM [Clients])

When I do not use the keyword Not it works but not with keyword Not. Is
this
a bug? I am using access 2000 sp3.

Thanks

Regards



Aug 18 '06 #6
"John" <Jo**@nospam.infovis.co.ukwrote in
news:tv******************************@pipex.net:
I have the following query;

SELECT Clients.ID, Clients.Company, Clients.[Company 2]
FROM Clients
WHERE Clients.[Company 2] Not In (SELECT [Company] FROM [Clients])

When I do not use the keyword Not it works but not with keyword
Not. Is this a bug? I am using access 2000 sp3.
What's wrong with a join?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Aug 18 '06 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Forgone Conclusion | last post by:
Hi, I have a View that groups and sums up totals. This View is then nested within in another View and used (it needs to be done like this). What i need to do is to be able to vary the records...
3
by: WGW | last post by:
Though I am a novice to MS SQL server (2000 I believe), I can do almost! everything I need. Maybe not efficiently, but usefully. However, I have a problem -- a complex query problem... I can...
12
by: Jeff Lanfield | last post by:
First of all, I apologize if coalescing is not the right term to describe my problem. I have a tree where each node has the same set of attributes (is the same entity) but child nodes should...
2
by: d2r2 | last post by:
Hi, I'm trying to run a nested (UNION) query against a MSAccessXP database (version 2002; build 10.6501.6714; SP3) In Access the SQL-statement executes just fine. When I run it in a asp-page I...
5
by: ahokdac-sql | last post by:
Hi, I'm adapting access queries to sql server and I have difficulties with the following pattern : query1 : SELECT * FROM Query2 WHERE A=@param1 query 2: SELECT * FROM Table2 WHERE B=@param2 ...
3
by: Tcs | last post by:
My backend is DB2 on our AS/400. While I do HAVE DB2 PE for my PC, I haven't loaded it yet. I'm still using MS Access. And no, I don't believe this is an Access question. (But who knows? I...
20
by: Robert | last post by:
Need some help to stop me going around in circles on this one.... Have a nested subform (subform2) which simulates a continuous form for the record on the parent subform. Subform2 has rows of...
3
by: Frank Swarbrick | last post by:
I was just messing around trying to learn things and attempted the following: select brch_nbr , sum(case when post_flag != 'P' then amount else 0 end) as sum_amount from film.film_transactions...
9
by: P3Eddie | last post by:
Hello all! I don't know if this can even be done, but I'm sure you will either help or suggest another avenue to accomplish the same. My problem may be a simple find duplicates / do something...
0
petepell
by: petepell | last post by:
Hello all, I am developing an application in VB 2008 that works with a SQL2005 DB to store and manipulate employee data. In one section of the app I want to be able to show a treeview of the...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.