Sequential numbers in a query

  • Thread starter Thread starter Harv Lake
  • Start date Start date
H

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
 
-----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



Harv Lake said:
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
Click to expand...


.
Click to expand...
 
Back
Top