display record number in query

[samuel]

I have a basic query on a table

SELECT Mnemonic, Name, Type
from tbl1

I want to be able to display the row number within the query where the
result would be

1 AB NAME1 TYPE1
2 BC NAME2 TYPE2


How do I accomplish this?

 

[Douglas J. Steele]

Assuming Mnemonic is the primary key of the table, you could use:

SELECT DCount("*", "tbl1", "Mnemonic <= '" & Mnemonic & "'") AS RowNumber,
Mnemonic, [Name], [Type]
FROM tbl1
ORDER BY Mnemonic

Note, though, that it'll be slow.

Also, just in case those are the real field names, Name and Type should be
changed to non reserve words. For a comprehensive list of names to avoid (as
well as a link to a free utility to check your application for compliance),
see what Allen Browne has at http://www.allenbrowne.com/AppIssueBadWord.html

 

[S.Clark]

http://www.eggheadcafe.com/software/aspnet/30963421/add-a-sequential-number-t.aspx