Sequential Record Numbers in Query

[Jim Pockmire]

How do I create a field that will sequentially number each record in a
query? The query may sort the records on different fields, but I still want
the calculated field to contain 1,2,3....n.

 

[Joe Fallon]

This SQL statement will give you both a record number and a running total of
freight in the Orders table. Modify it to meet your needs after testing it
on Northwind.

SELECT (SELECT COUNT(OrderID) FROM Orders AS temp WHERE temp.OrderID <=
Orders.OrderID) AS Recno, Orders.OrderID, Orders.Freight, (SELECT
Sum(Freight) FROM Orders AS temp WHERE temp.OrderID <= Orders.OrderID) AS
RunningTotal
FROM Orders
ORDER BY Orders.OrderID;


For another solution check out this MSKB Article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;210554

 

[Jim Pockmire]

In this instance I do not have a unique order ID that I can use. Basically,
I am trying to order golf scores sequentially and there can be multiple
entries for each score. I would still like a field that identifies the
records 1,2,3...n even though there may be multiple entries. I notice that
when the query is in datasheet view, Access is able to number the records at
the bottom of the datasheet. Any other suggestions?

Thank you for the response.

 

[Joe Fallon]

Have you considered building a table with an autonumber and then just
inserting rows into it?

 

[Jim Pockmire]

I have, but as I enter new scores, I always want the lowest score to be
number "1" and so on. I don't think the autonumber will re-sequence the
records as I enter new records.

 

[Joe Fallon]

Well,
you can code the construction of the table,
delete it between runs and re-fill it.

 

[Jim Pockmire]

Joe, Thank you for your help. I was hoping to get a more direct solution
though. I figured if Access can number the records in datasheet view, there
should be some code that could do the same.

Jim