Record Count Field

[chrisaroundtown]

Hi,

I'd like a column in a query to show a record count next to each row. i.e. if I have got a query returing 10 records I'd like each row to have a number, which would be numbers 1 through to 10.

Any ideas on how I can do this?

Chris

 

[cmmrfrds]

Provided the table has an unique ID, you can create row numbers through a self join. Here is an example.

SELECT Count(*) AS row, A.ID
FROM TestOrder AS A, TestOrder AS B
WHERE ((B.ID)<=[A].[id])
GROUP BY A.ID
ORDER BY A.ID;