record number

[Jon Rowlan]

I wish to number records, in natural order from 1 to n

There is no numerical or date comparison or sort that will allow be to rank
using the techniques listed (that rely on there being count() records in my
query that are logically lower than the current record.

Is there any way to apply a simple record number (like the old
clipper/dbase/foxpro recno()) without relying on a comparison.

jON

 

[Tom Ellison]

Dear Jon:

If is is the case that there is no row, or combination of rows, that
uniquely identify the rows in a table, then the only way would be to use a
random number to give them values and sort by that. Even then, there is no
guarantee of uniqueness.

If the rows in your table do not have a unique order then a query won't
help.

You could open the table using VBA as a recordset and assign sequential
numbers to them.

You could add an autonumber column.

Just for the purpose of theoretical research, what would be accomplished for
you by assigning numbers to them? What would this allow you to do which you
cannot do without numbering?

Tom Ellison

 

[John Vinson]

I wish to number records, in natural order from 1 to n

A table HAS NO ORDER. That's like asking to number the potatoes in a
wheelbarrow in "natural order". Access will store new records in a
table in whatever order is convenient; sure, they'll usually be
sequential, but you CANNOT count on it.

If need be you can add a Long Integer field and open a Recordset:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim iRec As Long
Set db = CurrentDb
Set rs = db.OpenRecordset("yourtable", dbOpenDynaset)
iRec = 1
Do Until rs.EOF
rs!RecNo = iRec
iRec = iRec+1
rs.MoveNext
Loop


John W. Vinson[MVP]

 

[SteveS]

I'm working nights, so maybe I'm not too alert. But don't you need

rs.Edit and rs.Update in the loop???

Do Until rs.EOF
rs.Edit '<<<
rs!RecNo = iRec
rs.Update '<<<
iRec = iRec+1
rs.MoveNext
Loop

(Trying to stay awake by reading the NG.......)

 

[John Vinson]

I'm working nights, so maybe I'm not too alert. But don't you need

rs.Edit and rs.Update in the loop???

Absolutely. You're wide awake and alert it seems, it's me who wasn't!

John W. Vinson[MVP]