Update Query Randomization

  • Thread starter Thread starter Ian
  • Start date Start date
I

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
 
Test if this will work:

UPDATE rawdata SET rawdata.[User] = Int((1200-1+1)*Rnd()+[USER]);
 
I'll post it in but I was hoping for the numbers to return between 1 and
1200 - let's see......
Jerry Whittle said:
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 said:
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
Click to expand...
Click to expand...
 
updated all [user] values to 1 then ran the query -- 33million records so
back in 5min.....

Jerry Whittle said:
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 said:
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
Click to expand...
Click to expand...
 
still one number

Jerry Whittle said:
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 said:
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
Click to expand...
Click to expand...
 
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
Click to expand...

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.
 
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
 
Back
Top