Sequential numbers in a query

[Harv Lake]

Thanks for the answer Michael but, it isn't quite what I
need.

I'd like to have the results returned in a query because
if I put them into a table, the database could get very
large very fast, even if I delete the table each time.

If you are new person to this question, see my
explanation of the problem below.

Thanks
Harv

Hi,

For a large number of records, the fastest way is to
append the ordered
result into a new table, with an autonumber. Just do no
specify any value
for the autonumber field (in the append query), but
remember to keep the
ORDER BY clause:


INSERT INTO newTableWithAutonum( LastName, FirstName)
SELECT last_name, first_name FROM oldTable ORDER BY
last_name, first_name


Get (read) the result from the (temporary) table you
created (with an
autonumber).



Hoping it may help,
Vanderghast, Access MVP

 

[Harv Lake]

-----Original Message-----
What you do not explain is how you trigger the sorting by LastName or
FirstName. If this is from a button that creates the QSL try something like

MyField: DCount("*", "underlyingTableName", "[LastName] <' " & [LastName] & "
' ")

or

write small piece of code in the OnCurrent of the form or report you use to
display the data. For example

Dim rs As Recordset

Private Sub Form_Current()
rs.Bookmark = Me.Bookmark
Me.txtPosition = rs.AbsolutePosition + 1
End Sub

Private Sub Form_Load()
Set rs = Me.RecordsetClone
End Sub


Regards

Thanks for the answer Michael but, it isn't quite what I
need.

I'd like to have the results returned in a query because
if I put them into a table, the database could get very
large very fast, even if I delete the table each time.

If you are new person to this question, see my
explanation of the problem below.

Thanks
Harv

Hi,

For a large number of records, the fastest way is to
append the ordered
result into a new table, with an autonumber. Just do no
specify any value
for the autonumber field (in the append query), but
remember to keep the
ORDER BY clause:


INSERT INTO newTableWithAutonum( LastName, FirstName)
SELECT last_name, first_name FROM oldTable ORDER BY
last_name, first_name


Get (read) the result from the (temporary) table you
created (with an
autonumber).



Hoping it may help,
Vanderghast, Access MVP

.