Update Query Randomization

[Ian]

I'm now trying to update some of the data in a table with

UPDATE rawdata SET rawdata.[User] = Int((1200-1+1)*Rnd()+1);

but it updates all the values to a single random number. Is there a way to
make it insert a different random number for each value? The key is called ID

 

[Jerry Whittle]

Test if this will work:

UPDATE rawdata SET rawdata.[User] = Int((1200-1+1)*Rnd()+[USER]);

 

[Ian]

I'll post it in but I was hoping for the numbers to return between 1 and
1200 - let's see......

Test if this will work:

UPDATE rawdata SET rawdata.[User] = Int((1200-1+1)*Rnd()+[USER]);
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I'm now trying to update some of the data in a table with

UPDATE rawdata SET rawdata.[User] = Int((1200-1+1)*Rnd()+1);

but it updates all the values to a single random number. Is there a way to
make it insert a different random number for each value? The key is called ID

 

[Ian]

updated all [user] values to 1 then ran the query -- 33million records so
back in 5min.....

Test if this will work:

UPDATE rawdata SET rawdata.[User] = Int((1200-1+1)*Rnd()+[USER]);
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I'm now trying to update some of the data in a table with

UPDATE rawdata SET rawdata.[User] = Int((1200-1+1)*Rnd()+1);

but it updates all the values to a single random number. Is there a way to
make it insert a different random number for each value? The key is called ID

 

[Ian]

still one number

Test if this will work:

UPDATE rawdata SET rawdata.[User] = Int((1200-1+1)*Rnd()+[USER]);
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I'm now trying to update some of the data in a table with

UPDATE rawdata SET rawdata.[User] = Int((1200-1+1)*Rnd()+1);

but it updates all the values to a single random number. Is there a way to
make it insert a different random number for each value? The key is called ID

 

[John W. Vinson]

I'm now trying to update some of the data in a table with

UPDATE rawdata SET rawdata.[User] = Int((1200-1+1)*Rnd()+1);

but it updates all the values to a single random number. Is there a way to
make it insert a different random number for each value? The key is called ID

If there is a function call which does not reference some value in the query
as an argument, Access "saves time" by calling the function only once. This
makes sense in some cases (e.g. Date() ), but unfortunately has this effect
when calling Rnd().

Here's my boilerplate suggestion:


Put this little function into a Module:

Public Function RndNum(vIgnore As Variant) As Double
Static bRnd As Boolean
If Not bRnd Then
'Initialize the random number generator once only
bRnd = True
Randomize
End If
RndNum = Rnd()
End Function

Then add a calculated field to your Query by typing

Shuffle: RndNum([fieldname])

In your case, use RndNum([some field]) in your expression.

 

[John Spencer]

Insert a number field into the call to Rnd. If ID is a number field you can use.

UPDATE rawdata
SET rawdata.[User] = Int((1200-1+1)*Rnd([ID])+1);

Or use the Len function if ID is text to force a number
UPDATE rawdata
SET rawdata.[User] = Int((1200-1+1)*Rnd(Len([ID]))+1);

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[raskew via AccessMonster.com]

Hi - You might try playing with this function:

Public Function RandLotto2(Bottom As Integer, Top As Integer, _
Amount As Integer) As String

'*******************************************
'Purpose: Produce x random/unique/sorted numbers
' between bottom and top.
'Coded by: raskew
'Sources: http://www.ozgrid.com/VBA/RandomNumbers.htm
' http://www.tek-tips.com/viewthread.cfm?qid=756905 (sort routine -
Roy Vidar's post)
'Inputs: ? RandLotto2(1, 55, 5)
'Output: 1 5 11 18 44 (5 unique, random, sorted
''*******************************************

Dim iArr As Variant
Dim I As Integer
Dim j As Integer
Dim r As Integer
Dim Temp As Integer

ReDim iArr(Bottom To Top)
For I = Bottom To Top
iArr(I) = I
Next I

For I = Top To Bottom + 1 Step -1
Randomize
r = Int(Rnd() * (I - Bottom + 1)) + Bottom
Temp = iArr(r)
iArr(r) = iArr(I)
iArr(I) = Temp
Next I

For I = Bottom To Amount
For j = I + 1 To Amount
If iArr(I) > iArr(j) Then
Temp = iArr(I)
iArr(I) = iArr(j)
iArr(j) = Temp
End If
Next j
Next I

For I = Bottom To Bottom + Amount - 1
RandLotto2 = RandLotto2 & " " & iArr(I)
Next I

RandLotto2 = Trim(RandLotto2)

End Function

HTH - Bob

Insert a number field into the call to Rnd. If ID is a number field you can use.

UPDATE rawdata
SET rawdata.[User] = Int((1200-1+1)*Rnd([ID])+1);

Or use the Len function if ID is text to force a number
UPDATE rawdata
SET rawdata.[User] = Int((1200-1+1)*Rnd(Len([ID]))+1);

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I'm now trying to update some of the data in a table with

UPDATE rawdata SET rawdata.[User] = Int((1200-1+1)*Rnd()+1);

but it updates all the values to a single random number. Is there a way to
make it insert a different random number for each value? The key is called ID