Generate Record Number Sequence in Query?

[gromit]

Hi -

Suppose I have a generic query that returns a group of records.

Is there some way to generate a field containing the number sequence of each record? E.g. if there are 10 records, generate the numbers 1 through 10?

Must be a simple way to do this, I'm just a bit dense today...

Thanks,

-g

 

[gromit]

Now I remember...

This is possible by generating a rank for each record.

E.g.

Table1
-------
ItemID, AutoNumber, PK
DateField, Date

Then the query is
SELECT tbl1.ItemID, tbl1.DateField, (Select Count(*) from Table1 Where [Date] < [tbl1].[DateField]+1; ) AS Rank
FROM Table1 AS tbl1
ORDER BY tbl1.DateField;

Note the use of the alias of Table1 to do the ranking.

See also -
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q120608