jet correlated subquery fails

[david epsom dot com dot au]

This is not new, it's the kind of thing that has caused problems for years,
but here it is again (Access 2000 Jet SR7: Access 97 Jet SR3). I have a
query based on a stored query that uses correlated subqueries. The top
level query returns many invalid records:

create table1:
create table table1 (idx autoincrement constraint ID primary key, comment
text)

create query1:
SELECT a.idx, (select max(idx) FROM table1 where (table1.idx < a.idx)) AS
OldIndex, (select min(idx) FROM table1 where (table1.idx > a.idx)) AS
NewIndex
FROM table1 AS a;

create query2:
SELECT Query1.OldIndex, Query1.NewIndex, Table1.idx, Table1_1.idx
FROM (Query1 LEFT JOIN Table1 ON Query1.OldIndex = Table1.idx) LEFT JOIN
Table1 AS Table1_1 ON Query1.NewIndex = Table1_1.idx;

query1 returns records like this:

NULL 1 2
1 2 3
2 3 4
3 4 NULL

query2 should return records like this:

1 3 1 3
2 4 2 4

but in fact it returns many records with null values in query1:

NULL NULL 1 4
etc

Since there are no records returned by Query1 with NULL NULL, this result is
incorrect.

In my application, I have replaced the correlated subqueries with 'joined'
querydefs.

(david)

 

[Gary Walter]

Thanks for heads-up David...

one more thing to look out for.

You didn't mention this but...
I did save Query1 to a table and
it worked just like one would have
*expected* the correlated query to work.

Thanks again,

Gary Walter