Query Row Number

[HansBades]

How can I get a query to return a row number for each record returned in numerical order? For instance if I query Select SKUID, SKUNAME from SKU WHERE SKU.DEPTIDFK =4 how can I get a third field with the row count? Thanks.

SKUID	SKUNAME	Row
5	Hammer	1
7	Pliers	2
12	Aluminum Shield	3

[orange]

Do you have a sample database to post?

[Micron]

It's an oft asked question and usually involves DCount function in the counting field. A google search will likely produce some 100 million plus results.

[orange]

See this for Row Number with Access by Richard Rost.

[June7]

Another http://allenbrowne.com/ranking.html

[isladogs]

Here's another approach not involving subqueries
Rank Order In Queries (isladogs.co.uk)

[NoellaG]

Just a thought: without order by, the rownumber makes no sense?

[CJ_London]

this method does not rely on an order by and as a function will be much faster than dcount or a subquery, particularly noticeable for large datasets

Put this function in a module

Function RowNumber(Optional r As Variant = -1) As Variant
Static X As Long


    If r = -1 Then
        X = 0
    Else
        X = X + 1
    End If
    
    RowNumber = X
    
End Function

and call it with your query - something like

SELECT *, RowNumber([PK]) AS Row
FROM mytable
WHERE (((RowNumber())=False))

Note the Where clause - this is required to to reset the count. Better to use the PK, but any field that is unique will do. If the field is not unique, the count will not increment correctly

Other points to note:
this works fine when viewed in a form or report, but view the query or form datasheet and the rownumbers will change if a row is selected
One of the potential benefits is if you reorder or filter the query, the rownumbers remain static - i.e. the first row is always 1, the second 2 etc
and as stated above, an order by is not required

[NoellaG]

If you want a row number viewed in a report, you don't need a function: just create an unbound control, set the control source to = 1 and set the running sum property to 'over all' or 'over group'

[isladogs]

@NoellaG
Agreed but the OP clearly stated query row number