Update Query Randomization

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

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.


"Ian" wrote:

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

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

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.


"Ian" wrote:

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

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

"Jerry Whittle" wrote:

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.


"Ian" wrote:

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

still one number

"Jerry Whittle" wrote:

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.


"Ian" wrote:

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

On Sun, 27 Dec 2009 18:11:01 -0800, Ian wrote:

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 W. Vinson [MVP]

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

Ian wrote:
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