add random numbers to an existing table

[paulc]

Hi everyone,



I'm trying to import a large text file with hundreds of thousands of rows, add a column with unique random numbers, and re export the text file for use in another program. I've tried this in Excel and R and they both butcher the original format. I've figured out how to add random numbers to a new table, and then cut and past some of my rows into that new table, but its no good for big tables. Is there a way to add a column to an existing table and populate it with random numbers? I'm pretty new to Access so if there are other ways to achieve the same goal, I'd welcome any and all suggestions.

Cheers,
Paul

[orange]

You may get some ideas from this link.

[paulc]

Thanks Orange,

That looks like very helpful code. I have two follow up questions,

1) which part of the code is just to create and populate the random numbers ?
2) where does one enter the code into Access to get it to execute?

Cheers,
Paul

[orange]

The procedure called
Sub PayBandStuff()

populates the xtrafield with a Random number.

I would suggest you import the data into table in Access. Then, add a field to the table to hold the
random number, say RandField. Then adapt the procedure PayBandStuff to populate RandField.

Show us your table layout and we can help you.

You would run the procedure when ready.

[paulc]

Thanks for your patience with me Orange,

Here is what the table looks like



I'm comfortable modifying the code, but I'm just not sure where to enter the code and run it? Thanks for your help, I've not used Access at this level before

Cheers,
Paul

[orange]

It appears that your table fields
field1,2 and 7 are text datatype
3,4,5,6 are number datatype.

You could copy your table, add a field8 with number data type, then select FieldSize Double. Let's say your newTable is called NewtableXX, and the added field is RandField number datatype

this routine should insert a Random number in RandField. (random numbers between 0 and 500 with about 10 decimal places)

'---------------------------------------------------------------------------------------
' Procedure : P_PaulC
' Author    : Jack
' Date      : 20/02/2014
' Purpose   : To put a random number in a field (Randfield) in a table NewTableXX
'
'---------------------------------------------------------------------------------------
'
Sub P_PaulC()
      'populate Randfield with random number
          Dim sql As String
          Dim rs As DAO.Recordset
          Dim i As Integer
10       On Error GoTo P_PaulC_Error

20        Randomize 'must use this to getnew random numbers
30        Set rs = CurrentDb.OpenRecordset("NewTableXX")
40        Do While Not rs.EOF
50            rs.Edit
60            rs!Randfield = Rnd() * 500 'just a number
70            rs.Update
80            rs.MoveNext
90        Loop

100      On Error GoTo 0
110      Exit Sub

P_PaulC_Error:

120       MsgBox "Error  "  & Err.Number & " in line "  & Erl & "  (" & Err.Description & ") in procedure P_PaulC "
End Sub

[paulc]

Thank you Orange! That worked perfectly, you have saved me so much time, I sincerely appreciate it.

Now I need to figure out how to export it so the format matches my other software.

Thanks again.
Paul

[orange]

Glad you have it resolved.
Happy to help.