Nested query bug?

[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

 

[Baz]

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?

 

[John]

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?

 

[Allen Browne]

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.

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?

 

[Baz]

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

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

 

[giorgio rancati]

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]

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?

 

[Jess]

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

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]

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?

 

[giorgio rancati]

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

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]

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?

 

[Jess]

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!

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

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]

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?

 

[giorgio rancati]

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]

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!

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

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]

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?

 

[Gary Walter]

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...

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]

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!

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

("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

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

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?