Update a table with random values

[Katy44]

I have an Access table where all the non-key fields are null. I need to fill them all in with an integer between 0 and 10 for testing. I can do this column by column, but not row by row as there are about 3000 of them! I have run:

update Table set Field = Round(Rnd()*10)

but this fills the field in with the same random number for each record (I assume it does the random number calculation first and then the update.

Can I do this writing a module and looping through the no of records in the table? If so, how do I get the number of records? It doesn't like 'select count(*)...'

 

[Katy44]

I have figured this out. The only way I could find to do it was to create a new form with a button on it to run this:

Private Sub Command0_Click()
Dim int1 As Integer
For int1 = 1 To 3725
DoCmd.SetWarnings (False)
DoCmd.RunSQL ("update Table set [0 - 1 months]=Round(Rnd()*10) where ID = " & int1)
Next int1
End Sub

ID is an autonumber col I had to put in to make this work - I plan to remove it when all the updates are done.