Return Row Number in Query

[mferrisi]

In Microsoft Query, is it possible to query for row numbers, and if so, what woldthat look like?

i.e. I run a query, and use it as a subquery to return only rows 1, 3, and 5?


cmd.CommandText = "SELECT Date, type, pct " & _
"FROM (SELECT * OVER(partition by trunc(returns_date) & _ "ORDER BY returns_type) " & _
"RowNumber, date, type, pct FROM records) " & _
"WHERE ROWNUM=1 OR ROWNUM = 3 or ROWNUM =5"



Thanks!

[geekgirlau]

Create a sub query, sorted by your key field, and add a calculated "RowNum" field set to:

RowNum: DCount("[KeyField]","MyTable","[KeyField]<=" & [KeyField])

You can then set filter criteria for this RowNum field.