Nested query bug?

  • Thread starter Thread starter John
  • Start date Start date
J

John

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 said:
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
Click to expand...

What do you mean it doesn't work? Do you get an error, or does it simply
not give you the results you expect?
 
It does not give the result i.e. results 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?

Thanks

Regards


Baz said:
John said:
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
Click to expand...

What do you mean it doesn't work? Do you get an error, or does it simply
not give you the results you expect?
Click to expand...
 
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.

Reply to group, rather than allenbrowne at mvps dot org.

John said:
It does not give the result i.e. results 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?

Thanks

Regards


Baz said:
John said:
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
Click to expand...

What do you mean it doesn't work? Do you get an error, or does it simply
not give you the results you expect?
Click to expand...
Click to expand...
 
John said:
It does not give the result i.e. results 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?

Thanks

Regards
Click to expand...

I've tried a query just like this and it works fine (in Access 2003) so it
doesn't look like a bug.

When you run the query without "Not", does Simon Pleydell appear in the
results?

Incidentally, a join would be a better way to do this query:

SELECT Clients1.ID, Clients1.Company, Clients1.[Company 2]
FROM Clients Clients1 LEFT JOIN Clients Clients2 ON Clients1.[Company 2] =
Clients2.[Company] WHERE Clients2.[Company] IS NULL
 
Hi Jon,

if you have some rows with [Company] = NULL the NOT IN operator doesn't
return rows

this works
----
SELECT Clients.ID, Clients.Company, Clients.[Company 2]
FROM Clients
WHERE Clients.[Company 2] Not In
(SELECT [Company] FROM [Clients] WHERE [Company] Is Not Null)
----

however the query with self join is a better way to do this query:

bye
--
Giorgio Rancati
[Office Access MVP]

John said:
It does not give the result i.e. results 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?

Thanks

Regards


Baz said:
John said:
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
Click to expand...

What do you mean it doesn't work? Do you get an error, or does it simply
not give you the results you expect?
Click to expand...
Click to expand...
 
Hi All.
I recently posted a 'cry for help' but didn't get any replies... not
sure why...maybe it wasn't clear but I think it is relevent and easy
for experienced Access developers, so I thought I'd post it here since
it has to do with Nested queries and this guy got a lot of replies:I sucessfully created a query with a nested select and it seems that
Access gave the subquery an alias name: AS [%$##@_Alias]
....ok by me since it worked.
but when I had to go back to add another condition on the iif/in set
:("A","O","S"), I got a
syntax error. I tried rebuilding from scratch, the subquery works on
its own but I can't wrap the select * from it.

The query that works:
SELECT *
FROM [SELECT IIf([Q].[accType] in
("A","O","S"),"OP",IIf([Q].[accType]="E","ED",IIf([Q].[accType]="I","IP",[Q­].[accType])))

AS patType, Q.OrdName, Q.CountOfOrdSeq
FROM LABORDER AS Q]. AS [%$##@_Alias]
ORDER BY PatType

For now I will do this in 2 queries but I'm trying to get better at
this!!
thanks,
Jess



giorgio said:
Hi Jon,

if you have some rows with [Company] = NULL the NOT IN operator doesn't
return rows

this works
----
SELECT Clients.ID, Clients.Company, Clients.[Company 2]
FROM Clients
WHERE Clients.[Company 2] Not In
(SELECT [Company] FROM [Clients] WHERE [Company] Is Not Null)
----

however the query with self join is a better way to do this query:

bye
--
Giorgio Rancati
[Office Access MVP]

John said:
It does not give the result i.e. results 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?

Thanks

Regards


Baz said:
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



What do you mean it doesn't work? Do you get an error, or does it simply
not give you the results you expect?
Click to expand...
Click to expand...
Click to expand...
 
Hi Jess,

Try to remove the check *Track Name Autocorrect Info* from
Tools-->Options-->General-->Name Autocorrect Options
Rewrite the query and give the derived table name
----
SELECT *
FROM [SELECT IIf([Q].[accType] in
("A","O","S"),"OP",IIf([Q].[accType]="E","ED",IIf([Q].[accType]="I","IP",[Q­
].[accType])))

AS patType, Q.OrdName, Q.CountOfOrdSeq
FROM LABORDER AS Q]. AS DRV_TBL
ORDER BY PatType
----

bye
--
Giorgio Rancati
[Office Access MVP]

"Jess" <[email protected]> ha scritto nel messaggio
Hi All.
I recently posted a 'cry for help' but didn't get any replies... not
sure why...maybe it wasn't clear but I think it is relevent and easy
for experienced Access developers, so I thought I'd post it here since
it has to do with Nested queries and this guy got a lot of replies:I sucessfully created a query with a nested select and it seems that
Access gave the subquery an alias name: AS [%$##@_Alias]
....ok by me since it worked.
but when I had to go back to add another condition on the iif/in set
:("A","O","S"), I got a
syntax error. I tried rebuilding from scratch, the subquery works on
its own but I can't wrap the select * from it.

The query that works:
SELECT *
FROM [SELECT IIf([Q].[accType] in
("A","O","S"),"OP",IIf([Q].[accType]="E","ED",IIf([Q].[accType]="I","IP",[Q­
].[accType])))

AS patType, Q.OrdName, Q.CountOfOrdSeq
FROM LABORDER AS Q]. AS [%$##@_Alias]
ORDER BY PatType

For now I will do this in 2 queries but I'm trying to get better at
this!!
thanks,
Jess



giorgio said:
Hi Jon,

if you have some rows with [Company] = NULL the NOT IN operator doesn't
return rows

this works
----
SELECT Clients.ID, Clients.Company, Clients.[Company 2]
FROM Clients
WHERE Clients.[Company 2] Not In
(SELECT [Company] FROM [Clients] WHERE [Company] Is Not Null)
----

however the query with self join is a better way to do this query:

bye
--
Giorgio Rancati
[Office Access MVP]

John said:
It does not give the result i.e. results 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?

Thanks

Regards


Baz said:
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



What do you mean it doesn't work? Do you get an error, or does it simply
not give you the results you expect?
Click to expand...
Click to expand...
Click to expand...
 
Thanks for the reply, giorgio...I removed the check for the track name
option but still did not fix the problem...
even changing the name from [%$##@_Alias] to [drv_tbl] causes the
syntax error..

Its a mystery!

giorgio said:
Hi Jess,

Try to remove the check *Track Name Autocorrect Info* from
Tools-->Options-->General-->Name Autocorrect Options
Rewrite the query and give the derived table name
----
SELECT *
FROM [SELECT IIf([Q].[accType] in
("A","O","S"),"OP",IIf([Q].[accType]="E","ED",IIf([Q].[accType]="I","IP",[Q­
].[accType])))

AS patType, Q.OrdName, Q.CountOfOrdSeq
FROM LABORDER AS Q]. AS DRV_TBL
ORDER BY PatType
----

bye
--
Giorgio Rancati
[Office Access MVP]

"Jess" <[email protected]> ha scritto nel messaggio
Hi All.
I recently posted a 'cry for help' but didn't get any replies... not
sure why...maybe it wasn't clear but I think it is relevent and easy
for experienced Access developers, so I thought I'd post it here since
it has to do with Nested queries and this guy got a lot of replies:I sucessfully created a query with a nested select and it seems that
Access gave the subquery an alias name: AS [%$##@_Alias]
...ok by me since it worked.
but when I had to go back to add another condition on the iif/in set
:("A","O","S"), I got a
syntax error. I tried rebuilding from scratch, the subquery works on
its own but I can't wrap the select * from it.

The query that works:
SELECT *
FROM [SELECT IIf([Q].[accType] in
("A","O","S"),"OP",IIf([Q].[accType]="E","ED",IIf([Q].[accType]="I","IP",[Q­
].[accType])))

AS patType, Q.OrdName, Q.CountOfOrdSeq
FROM LABORDER AS Q]. AS [%$##@_Alias]
ORDER BY PatType

For now I will do this in 2 queries but I'm trying to get better at
this!!
thanks,
Jess



giorgio said:
Hi Jon,

if you have some rows with [Company] = NULL the NOT IN operator doesn't
return rows

this works
----
SELECT Clients.ID, Clients.Company, Clients.[Company 2]
FROM Clients
WHERE Clients.[Company 2] Not In
(SELECT [Company] FROM [Clients] WHERE [Company] Is Not Null)
----

however the query with self join is a better way to do this query:

bye
--
Giorgio Rancati
[Office Access MVP]

John said:
It does not give the result i.e. results 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?

Thanks

Regards



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



What do you mean it doesn't work? Do you get an error, or does it simply
not give you the results you expect?
Click to expand...
Click to expand...
Click to expand...
 
Hi Jess,

every time you need to change the query you remove the square brackets and
replace they with round brackets
ex.
----
SELECT *
FROM (SELECT IIf([Q].[accType] in
("A","O","S"),"OP",IIf([Q].[accType]="E","ED",IIf([Q].[accType]="I","IP",[Q]
..[accType])))
AS patType, Q.OrdName, Q.CountOfOrdSeq

FROM LABORDER AS Q) AS DRV_TBL
ORDER BY DRV_TBL.patType;
----

Bye
--
Giorgio Rancati
[Office Access MVP]

Jess said:
Thanks for the reply, giorgio...I removed the check for the track name
option but still did not fix the problem...
even changing the name from [%$##@_Alias] to [drv_tbl] causes the
syntax error..
Click to expand...
Its a mystery!
Click to expand...

giorgio said:
Hi Jess,

Try to remove the check *Track Name Autocorrect Info* from
Tools-->Options-->General-->Name Autocorrect Options
Rewrite the query and give the derived table name
----
SELECT *
FROM [SELECT IIf([Q].[accType] in
("A","O","S"),"OP",IIf([Q].[accType]="E","ED",IIf([Q].[accType]="I","IP",[Q­
].[accType])))

AS patType, Q.OrdName, Q.CountOfOrdSeq
FROM LABORDER AS Q]. AS DRV_TBL
ORDER BY PatType
----

bye
--
Giorgio Rancati
[Office Access MVP]

"Jess" <[email protected]> ha scritto nel messaggio
Hi All.
I recently posted a 'cry for help' but didn't get any replies... not
sure why...maybe it wasn't clear but I think it is relevent and easy
for experienced Access developers, so I thought I'd post it here since
it has to do with Nested queries and this guy got a lot of replies:I sucessfully created a query with a nested select and it seems that
Access gave the subquery an alias name: AS [%$##@_Alias]
...ok by me since it worked.
but when I had to go back to add another condition on the iif/in set
:("A","O","S"), I got a
syntax error. I tried rebuilding from scratch, the subquery works on
its own but I can't wrap the select * from it.

The query that works:
SELECT *
FROM [SELECT IIf([Q].[accType] in
("A","O","S"),"OP",IIf([Q].[accType]="E","ED",IIf([Q].[accType]="I","IP",[Q­
].[accType])))

AS patType, Q.OrdName, Q.CountOfOrdSeq
FROM LABORDER AS Q]. AS [%$##@_Alias]
ORDER BY PatType

For now I will do this in 2 queries but I'm trying to get better at
this!!
thanks,
Jess



giorgio said:
Hi Jon,

if you have some rows with [Company] = NULL the NOT IN operator doesn't
return rows

this works
----
SELECT Clients.ID, Clients.Company, Clients.[Company 2]
FROM Clients
WHERE Clients.[Company 2] Not In
(SELECT [Company] FROM [Clients] WHERE [Company] Is Not Null)
----

however the query with self join is a better way to do this query:

bye
--
Giorgio Rancati
[Office Access MVP]

John said:
It does not give the result i.e. results 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?

Thanks

Regards



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



What do you mean it doesn't work? Do you get an error, or does it simply
not give you the results you expect?
Click to expand...
Click to expand...
Click to expand...
 
PMFBI

Implicit in what Giorgio is saying......

when you use a subquery in the FROM clause,
somewhere down the line Access will surround
that subquery in brackets and an ending period.

but, the parser will then choke on any *further brackets*
within that subquery....

you have no table/field names that I see
that required the brackets within the subquery,
so remove the brackets around "Q" and "accType"
in the subquery's IIF stmt
and go in peace...

giorgio rancati said:
Hi Jess,

every time you need to change the query you remove the square brackets and
replace they with round brackets
ex.
----
SELECT *
FROM (SELECT IIf([Q].[accType] in
("A","O","S"),"OP",IIf([Q].[accType]="E","ED",IIf([Q].[accType]="I","IP",[Q]
.[accType])))
AS patType, Q.OrdName, Q.CountOfOrdSeq

FROM LABORDER AS Q) AS DRV_TBL
ORDER BY DRV_TBL.patType;
----

Bye
--
Giorgio Rancati
[Office Access MVP]

Jess said:
Thanks for the reply, giorgio...I removed the check for the track name
option but still did not fix the problem...
even changing the name from [%$##@_Alias] to [drv_tbl] causes the
syntax error..
Click to expand...
Its a mystery!
Click to expand...

giorgio said:
Hi Jess,

Try to remove the check *Track Name Autocorrect Info* from
Tools-->Options-->General-->Name Autocorrect Options
Rewrite the query and give the derived table name
Click to expand...
("A","O","S"),"OP",IIf([Q].[accType]="E","ED",IIf([Q].[accType]="I","IP",[Q­
].[accType])))

AS patType, Q.OrdName, Q.CountOfOrdSeq
FROM LABORDER AS Q]. AS DRV_TBL
ORDER BY PatType
----

bye
--
Giorgio Rancati
[Office Access MVP]

"Jess" <[email protected]> ha scritto nel messaggio
Hi All.
I recently posted a 'cry for help' but didn't get any replies... not
sure why...maybe it wasn't clear but I think it is relevent and easy
for experienced Access developers, so I thought I'd post it here since
it has to do with Nested queries and this guy got a lot of replies:I sucessfully created a query with a nested select and it seems that
Access gave the subquery an alias name: AS [%$##@_Alias]
...ok by me since it worked.
but when I had to go back to add another condition on the iif/in set
:("A","O","S"), I got a
syntax error. I tried rebuilding from scratch, the subquery works on
its own but I can't wrap the select * from it.

The query that works:
SELECT *
FROM [SELECT IIf([Q].[accType] in
Click to expand...
("A","O","S"),"OP",IIf([Q].[accType]="E","ED",IIf([Q].[accType]="I","IP",[Q­
].[accType])))

AS patType, Q.OrdName, Q.CountOfOrdSeq
FROM LABORDER AS Q]. AS [%$##@_Alias]
ORDER BY PatType

For now I will do this in 2 queries but I'm trying to get better at
this!!
thanks,
Jess



giorgio said:
Hi Jon,

if you have some rows with [Company] = NULL the NOT IN operator doesn't
return rows

this works
----
SELECT Clients.ID, Clients.Company, Clients.[Company 2]
FROM Clients
WHERE Clients.[Company 2] Not In
(SELECT [Company] FROM [Clients] WHERE [Company] Is Not Null)
----

however the query with self join is a better way to do this query:

bye
--
Giorgio Rancati
[Office Access MVP]

"John" <[email protected]> ha scritto nel messaggio
It does not give the result i.e. results 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?

Thanks

Regards



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
Click to expand...
Not.
Is
this
a bug? I am using access 2000 sp3.

Thanks

Regards



What do you mean it doesn't work? Do you get an error, or does it simply
not give you the results you expect?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top