Adding a field containing the row number

[Badman]

How can I write a query that will return a table with one extra field
indicating the row number.
I know I can use "Auto number" to put a number in a field, but I need a way
that will show the correct row numbers even when the table is sorted by
another field.
I thought about creating another table with just the row numbers, and the
querying with something like:
---
SELECT RowNumbers.RowNum, AnyOtherTable.*
FROM RowNumbers, AnyOtherTable;
---
where RowNumbers is a table containing just 0, 1, 2, 3, 4, .... and so on
(in each row).
That however returns all the possible combinations of each of the tables
rows...

I was also wondering if it possible to get a certain range of rows of a
table in a query.
Something like retreiving the data in rows 26 through 50 of a table
containing more than 100 rows.

Anyone know how I can do this?

Any help or suggestions are appreciated.

-Steve

[Danny J. Lesandrini]

I forget who has code for this (Lyle, Larry, ???) but it requires a function
to create a recordset and determine the absolute position of a record
dynamically. You won't (shouldn't) make this a field in the table, since it
will change based on the filter and sort. It can be done dynamically with
a query and the above mentioned function.

If anyone remembers ... or finds it in Google, you'll be in luck.
--
Danny J. Lesandrini

"Badman" <Badm...@hotmail.com> wrote

[Danny J. Lesandrini]

Ooops, it was Stephen Lebans code.

I tested it with a query of mine by adding this column to the query:

RowNum: serialize("qryAccountsPayable","RefNum",[RefNum])

--
Danny J. Lesandrini

"Danny J. Lesandrini" wrote

> I forget who has code for this (Lyle, Larry, ???) but it requires a function
> to create a recordset and determine the absolute position of a record
> dynamically.

Function Serialize(qryname As String, keyname As String, keyvalue) As Long
Dim dbs As Database
Dim rs As Recordset

Set dbs = CurrentDb
On Error GoTo Err_Serialize
Set rs = dbs.OpenRecordset(qryname, dbOpenDynaset, dbReadOnly)

On Error GoTo Err_Serialize

'Find the current record.
Select Case rs.Fields(keyname).Type
' Find using numeric data type key value?
Case DB_INTEGER, DB_LONG, DB_CURRENCY, DB_SINGLE, DB_DOUBLE, DB_BYTE
rs.FindFirst "[" & keyname & "] = " & keyvalue
' Find using date data type key value?
Case DB_DATE
rs.FindFirst "[" & keyname & "] = #" & keyvalue & "#"
' Find using text data type key value?
Case DB_TEXT
rs.FindFirst "[" & keyname & "] = '" & keyvalue & "'"
Case Else
MsgBox "ERROR: Invalid key field data type!"
'Add your error handler here
End Select

'return the record number
Serialize = Nz(rs.AbsolutePosition, 0) + 1

Err_Serialize:

rs.Close
dbs.Close
Set rs = Nothing
Set dbs = Nothing

End Function

[John Winterbottom]

Think of the records in a table as being an unordered heap - the concept of
Row Number has no meaning here. To give it a meaning in your application,
you must use an ORDER
BY clause in the query. The general solution then, using pure SQL, is to
use a corelated subquery to count the number of records in the table less
than or equal a particular ORDER BY criteria, such as start_date. Example:

orders
=======
order_number (text PK)
order_date (date / time)

The following will show you row numbers for each order:

SELECT o1.order_number, o1.order_date,
(SELECT COUNT(*) FROM orders AS o2
WHERE o2.order_date <= o1.order_date) AS RowNum
FROM
orders AS o1
ORDER BY o1.order_date

There are some problems with this method:

1. It can be slow, since the inner query is run once for each iteration of
the outer query.
2. The inner query has to be changed if you need a different sort order.
3. It doesn't handle ties, (eg orders on the same date).

Having said that, it is still reasonably fast if your tables are indexed
correctly. Certainly I would expect it to be faster than a DAO solution, (I
must test that one of these days).

You can use a sub-query like this in the WHERE clause to restrict the
resultset
to certain rows; but it gets ugly.

SELECT o1.order_number, o1.order_date,
(SELECT COUNT(*) FROM orders AS o2
WHERE o2.order_date <= o1.order_date) AS RowNum
FROM
orders AS o1
WHERE
(SELECT COUNT(*) FROM orders AS o2
WHERE o2.order_date <= o1.order_date)
BETWEEN 2 AND 4
ORDER BY o1.order_date

A more attractive alternative might be to use an ADO recordset that supports
paging. Paging is often used in ASP applications to return records to the
client in batches, (1 to 10, 11 to 20 etc.) Look up AbsolutePage, PageSize
and PageCount on Visual Basic / ADO help for some examples.

"Badman" <Badm...@hotmail.com> wrote in message
news:LleJ6.27$Kb.1...@newsread2.prod.itd.earthlink.net...

[Clive Bolton]

Steve hasn't said, but if he plans to use the query results in a form or
report, there are simple ways to serially number the rows as presented.

As for the latter part of his posting, "Row Number" has no meaning so cannot
be used to find groups of records. This must be done using the primary key.
This could of course be a roll-your-own serial ID field.

Steve might find help from the Microsoft Knowledgebase:

Understanding Relational Database Design Available on MSL
Article ID: Q164172

Database Normalization Basics
Article ID: Q100139

Clive

"John Winterbottom" <jo...@assaynet.com> wrote in message
news:zmiJ6.30756$_f3.5...@news20.bellglobal.com...