Sequential Record Numbers in Query

  • Thread starter Thread starter Jim Pockmire
  • Start date Start date
J

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.
 
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
 
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.
 
Have you considered building a table with an autonumber and then just
inserting rows into it?
 
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.
 
Well,
you can code the construction of the table,
delete it between runs and re-fill it.
 
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
 
Back
Top