create sequential number column index into table with data

Question

I wanted to do something like this post, so I tried:

SELECT 
   ROW_NUMBER() OVER(ORDER BY t.[Data Saida] ) AS id, 
   t.codigo, t.[Data Saida], t.Entidade, t.DataEnt, 
   t.[GTEntT Nº], t.Estado, t.[GTSaida Nº], t.[Observações1], 
   t.Eequisitante, t.Certificado, T.Resultado, T.Seleccionar, t.[Tipo de Intervenção]
FROM 
   [Movimento ferramentas] t;

However I ended up getting something like

Syntax error (missing operator) in query expression ROW_NUMBER() OVER(ORDER BY t.[Data Saida] )

Is it because ROW_NUMBER() OVER() is SQL Server only or am I doing something wrong?

I'm working with MS Access 2010.

Here's a row from that table:

Answer 1

To add an AutoNumber field to an existing table, simply open the table in Design View, type in the Field Name and select "AutoNumber" from the drop-down list for the Data Type:

Access will populate the new field with AutoNumber values for any existing records in the table.

Edit re: influencing the order in which AutoNumber values are applied to existing records

As with many other database operations, there is essentially no guarantee that Access will use any particular order when assigning the AutoNumber values to existing records. However, if we look at a couple of examples we can see how Access will likely do it.

Consider the following sample table named [Events]. The rows were entered in random order and there is no primary key:

EventDate   Event         
----------  --------------
2012-06-01  it's June     
2012-10-01  it's October  
2012-09-01  it's September
2012-12-01  it's December 
2012-11-01  it's November 
2012-07-01  it's July     
2012-04-01  it's April    
2012-08-01  it's August   
2012-02-01  it's February 
2012-01-01  it's January  
2012-03-01  it's March    
2012-05-01  it's May      

Now we'll simply add an AutoNumber field named [ID] using the procedure above. After that has been done

SELECT * FROM Events ORDER BY ID

returns

EventDate   Event           ID
----------  --------------  --
2012-06-01  it's June        1
2012-10-01  it's October     2
2012-09-01  it's September   3
2012-12-01  it's December    4
2012-11-01  it's November    5
2012-07-01  it's July        6
2012-04-01  it's April       7
2012-08-01  it's August      8
2012-02-01  it's February    9
2012-01-01  it's January    10
2012-03-01  it's March      11
2012-05-01  it's May        12

Now let's revert back to the old copy of the table and see if the existence of a primary key makes a difference. We'll make [Event Date] the primary key, save the changes to the table, and then add the [ID] AutoNumber field. After that is done, the select statement above gives us

EventDate   Event           ID
----------  --------------  --
2012-06-01  it's June        1
2012-10-01  it's October     2
2012-09-01  it's September   3
2012-12-01  it's December    4
2012-11-01  it's November    5
2012-07-01  it's July        6
2012-04-01  it's April       7
2012-08-01  it's August      8
2012-02-01  it's February    9
2012-01-01  it's January    10
2012-03-01  it's March      11
2012-05-01  it's May        12

Hmmm, same thing. So it looks like the AutoNumber values get assigned to the table in natural order (the order in which the records were added to the table) even if there is a primary key.

Okay, if that's the case then let's use a make-table query to create a new copy of the table in a different order

SELECT Events.EventDate, Events.Event 
INTO Events2
FROM Events
ORDER BY Events.EventDate;

Now let's add the [ID] AutoNumber field to that new table and see what we get:

SELECT * FROM Events2 ORDER BY ID

returns

EventDate   Event           ID
----------  --------------  --
2012-01-01  it's January     1
2012-02-01  it's February    2
2012-03-01  it's March       3
2012-04-01  it's April       4
2012-05-01  it's May         5
2012-06-01  it's June        6
2012-07-01  it's July        7
2012-08-01  it's August      8
2012-09-01  it's September   9
2012-10-01  it's October    10
2012-11-01  it's November   11
2012-12-01  it's December   12

If that is the order we want then we can just delete the [Events] table and rename [Events2] to [Events].