Pulling a random number using a query 1

[RandDUser]

I have a table with 1 Field. That field is made up of a bunch of 10 digit numbers. There are a ton of numbers that begin with the same 7 digits. I want pull back a random number for each unique first 7 digits. Example:

Field 1
--------
1234567000
1234567111
1234567222
1234567NNN
7654321000
7654321111
7654321222
7654321NNN
NNNNNNNXXX

I'm trying to write a query that will randomly give me one digit that begins with the 1234567, one that begins with the 7654321, one that begins with the NNNNNNN, etc.

I used =left([fiedl1], 7), and did a count on it to see how many unique first 7 numbers there are, and I know I should use a Rnd() funcition somewhere, but after that, I'm stuck.

Any suggestions?

TIA

 

[Golom]

Write a function like this

Public Function FRnd(X As Variant) As String
    Dim RF As Long
    RF = 999# * Rnd()
    FRnd = Format(RF,"000")
End Function

And then some SQL

SELECT [Field1] & FRnd([Field1]) AS NewField
FROM MyTable
WHERE [Field1] IS NOT NULL
Order By 1

but note that random numbers are just that ... random. There is no guarantee that you will not get duplicate values returned by this process.

I know. I know. X is not referenced in FRnd, but if you omit it then the function will return the same value for every call to it.

 

[ThomasLafferty]

Hi Golom!
To force reevaluation of a random, use the Randomize statement.

Tom

Born once die twice; born twice die once.

 

[Golom]

ThomasLafferty

Yeah ... I know. But this

Public Function FRnd() As String
    Dim RF As Long
    [red]Randomize[/red]
    RF = 999# * Rnd()
    FRnd = Format(RF,"000")
End Function

still produces identical values for every call to the function. Why including an unreferenced field makes a difference, I have no idea.

Just as a curiosity, running the above (even without "Randomize") from VBA code rather than SQL, gives you nice random values.

 

[PHV]

I guess JetSQL's optimizer call the function only once as the return value seems to not depend of any current row value ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Golom]

Great PHV!

And obvious ... now that you point it out.

Star from me.