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 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
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
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.
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.
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
"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/ This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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...
|
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...
|
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
...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
| |