Query does not return First or Last record from subquery

[David Witteried]

I think this is a bug. I remember in my old Access V1 book (yes I read it)
that the author continually stressed that data in tables is unordered. I also
believe this query worked up through Access 95 and stopped working in Access
2000. Here is the setup:

Use the following SQL to create the table:

CREATE TABLE [Example Data] (ReturnCode TEXT(1), QueryRank BYTE, TableOrder
COUNTER);

Load the table with the following data in the order shown below:

ReturnCode, QueryRank, TableOrder(Autonumber)
B, 2, 1
E, 5, 2
A, 1, 3
C, 3, 4
D, 4, 5

Create the following two queries:

Query 1: Name it [Sort by QueryRank ASC]

SELECT [Example Data].QueryRank, [Example Data].ReturnCode, [Example
Data].TableOrder
FROM [Example Data]
ORDER BY [Example Data].QueryRank;

Query 2: Name it [Select First and Last Code from sorted query]

SELECT First([Sort by QueryRank ASC].ReturnCode) AS FirstCode, First([Sort
by QueryRank ASC].TableOrder) AS FirstTableOrder, Last([Sort by QueryRank
ASC].ReturnCode) AS LastCode, Last([Sort by QueryRank ASC].TableOrder) AS
LastTableOrder
FROM [Sort by QueryRank ASC];

The first query sorts the data using the QueryRank field which results in
the ReturnCode being in alphabetic order. The second query gets the first and
last rows from the the [Sort by QueryRank ASC] query (or it should!). If the
query worked as expected the result should be:

FirstCode: A, FirstTableOrder: 3, LastCode: E, LastTableOrder 2

Instead you will get:

FirstCode: B, FirstTableOrder: 1, LastCode: D, LastTableOrder 5

The returned result is the order the data was entered into the table, not
the order imposed by the query! I believe this violates the ANSI SQL standard
(or Cobb's rules) that there be no order in the table.

Does anybody know how to make an Access query work "correctly" in this
scenario? My workarround is to create a temporary table, append the data from
a sorted append query, then pull the first or last values within my groupings
from the temp table.

Thanks,

David Witteried

 

[Jerry Whittle]

Add the following to the last query:

ORDER BY [Sort by QueryRank ASC].QueryRank ;

First and Last have no meaning unless you have a sort order in the query.