I have a table in my database in Access 2013.
Table : city
ID_city city
1 Tetuan
5 Rabat
9 Marrakech
10 Agadir
15 Laayoun
I wish to add the Rowid number beside them:
Rowid ID_city city
1 1 Tetuan
2 5 Rabat
3 9 Marrakech
4 10 Agadir
5 15 Laayoun
One way to do this is to use the count function in a subquery. Not sure it scales well though and there are probably better ways...
select
(select count(*) from city where ID_city <= t1.ID_city) as row_number,
*
from city t1
The best way to do it is by using a self-join...
SELECT
COUNT(*) AS Rowid,
C.ID_City,
C.city
FROM City C
INNER JOIN City C1 ON C.ID_City >= C1.ID_City
GROUP By C.ID_City, C.city
A little VBA goes a long way...
'Module level variables; values will persist between function calls
'To reset the row number, you have to explicitly call Reset
Dim lastValue As Integer
Public Function RowNumber(x) As Integer
'We need this parameter so Access will call the function on each row, instead of only once
lastValue = lastValue +1
RowNumber = lastValue
End Function
Public Sub Reset()
lastValue = 0
End Sub
SQL statement:
SELECT RowNumber(city) AS RowID, *
FROM City
another option: (http://www.openwinforms.com/row_number_to_sql_select.html)
SELECT ROW_NUMBER()
OVER (ORDER BY ID_city) AS Rowid,
ID_City, city
FROM city
ROW_NUMBER() and OVER are not supported in Access SQL.