Generating Random Numbers

[nancier]

This problem involves generating random numbers with a user supplied seed number. In this
example, I have 3 different dollar ranges and a sample size for each dollar range.

Dollar Range...................Sample Size.................Seed#
.01 - 10000............................ 417.....................777
10000.01-75000..................... 200.....................555
75000.01 - 9999999999...........100.....................999

I am trying to seed them individually but the first seed seems to control all results. For
example, If I change the seed in the .01-10000 dollar range from 777 to 888, the numbers chosen
in the other two ranges are changing even though they shouldn't because their seeds remain the
same. This is the query and the module I'm using.

(SELECT DISTINCTROW TOP 417 tblAmount.Amt FROM tblAmount WHERE Amt between 00.01 and 10000.00 GROUP BY tblAmount.Amt, tblAmount.ID, GetRan(777,[id]) ORDER BY GetRan(777,[id]))
UNION ALL (SELECT DISTINCTROW TOP 200 tblAmount.Amt FROM tblAmount WHERE Amt between 10000.01 and 75000.00 GROUP BY tblAmount.Amt, tblAmount.ID, GetRan(555,[id]) ORDER BY GetRan(555,[id]))
UNION ALL (SELECT DISTINCTROW TOP 100 tblAmount.Amt FROM tblAmount WHERE Amt between 75000.01 and 99999999.99 GROUP BY tblAmount.Amt, tblAmount.ID, GetRan(999,[id]) ORDER BY GetRan(999,[id]));

Option Compare Database
Public gblnInitialized As Boolean
Public Function GetRan(Seed As Long, nextnum) As Double
If gblnInitialized = False Then
InitializeRandom (Seed)
End If
GetRan = Rnd(nextnum)
End Function
Public Sub InitializeRandom(Seed As Long)
Rnd -1
Randomize Seed
gblnInitialized = True
End Sub

Any suggestions how to accomplish what I'm trying to do?

Thanks

 

[PHV]

Have you tried a semi-colon ( before each UNION ?

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

 

[VBslammer]

You're setting a flag to bypass the InitializeRandom() procedure:

Public gblnInitialized As Boolean

Public Function GetRan(Seed As Long, nextnum) As Double
  If gblnInitialized = False Then  [red]'<-- see?[/red]
    InitializeRandom (Seed)
  End If
  GetRan = Rnd(nextnum)
End Function

Public Sub InitializeRandom(Seed As Long)
  Rnd -1
  Randomize Seed
  gblnInitialized = True     [red]'<-- Flag set[/red]
End Sub

Simplify the function:

Public Function GetRan(Seed As Long, nextnum) As Double
  Randomize Seed
  GetRan = Rnd(nextnum)
End Function

VBSlammer

Unemployed in Houston, Texas

 

[nancier]

PVH,

When I try the ; before each Union, it tells me I have a syntax error.

Thanks

 

[nancier]

VBSlammer,

I tried your suggestion but it's giving me problems. Instead of the correct number of results, 717, it gives me an incorrect number of results each time like 738 or 739, etc. Also, I'm quite sure that the
Rnd -1
line that you removed is quite important. I'd love to hear any other ideas you might have. I know it's difficult without you having the database.

Thanks

 

[VBslammer]

You're right about that, using a negative number forces Rnd() to use the same sequence each time:

To repeat sequences of random numbers, call Rnd() with a negative argument immediately before using Randomize with a numeric argument.

Public Function GetRan(Seed As Long, nextnum) As Double
  Rnd -1
  Randomize Seed
  GetRan = Rnd(nextnum)
End Function

VBSlammer

Unemployed in Houston, Texas

 

[PHV]

Maybe playing with static variable ?
Public Function GetRan(Seed As Long, nextnum) As Double
Static usedSeed As Long
If usedSeed <> Seed Then
Rnd -1
Randomize Seed
usedSeed = Seed
End If
GetRan = Rnd(nextnum)
End Function

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

 

[nancier]

VBSlammer, That doesn't seem to work for some reason, it returns 13,000 plus records though I'm only asking for 717.

Thanks

 

[nancier]

PVH, you are really on to something here. The first two seeds seem to work flawlessly and always return the same results. There seems to be a problem sometimes though with the third seed. I tested your code out by using various seeds and taking note of the first result in each range. The results were consistent until the last two results. Can you see anything in the code that would be causing this? Nice work!!!

777 12
555 10006.34
999 75295.52


444 5.92
555 10006.34
999 75295.52


777 12
222 10107.01
999 75295.52

444 5.92
222 10107.01
999 75295.52

777 12
333 10000.08
999 75295.52

444 5.92
333 10000.08
999 75295.52

444 5.92
333 10000.08
666 72893.77

777 12
555 10006.34
666 75356.9

 

[nancier]

Hi, Sorry, it is working fine. I'm a little bleary eyed here and saw the numbers wrong. Looks perfect.

Thanks a million!

 

[nancier]

Hi, I did some more analysis and have two more questions.

1.)I tested it trying to do just a single query and it comes up with an error saying this is a union query. I then remove the ( before Select and one of the ) after [id]. It then runs but comes up with a different 417 results than when it was a union query. Any idea why the results change?

New Select Query
SELECT DISTINCTROW TOP 417 tblAmount.Amt FROM tblAmount WHERE Amt between 00.01 and 10000.00 GROUP BY tblAmount.Amt, tblAmount.ID, GetRan(1234567,[id]) ORDER BY GetRan(1234567,[id]);

Old Union Query
(SELECT DISTINCTROW TOP 417 tblAmount.Amt FROM tblAmount WHERE Amt between 00.01 and 10000.00 GROUP BY tblAmount.Amt, tblAmount.ID, GetRan(444,[id]) ORDER BY GetRan(444,[id]))
UNION ALL (SELECT DISTINCTROW TOP 200 tblAmount.Amt FROM tblAmount WHERE Amt between 10000.01 and 75000.00 GROUP BY tblAmount.Amt, tblAmount.ID, GetRan(222,[id]) ORDER BY GetRan(222,[id]))
UNION ALL (SELECT DISTINCTROW TOP 100 tblAmount.Amt FROM tblAmount WHERE Amt between 75000.01 and 99999999.99 GROUP BY tblAmount.Amt, tblAmount.ID, GetRan(666,[id]) ORDER BY GetRan(666,[id]));

2.)You'll notice I have GetRan(444,[id])in the Group By section and also in the Order By Section. Do you know if I need to put GetRan(444,[id]) in both places or can I just put GetRan([id]) in one the spots. I notice I get different results by changing the 444 number in either place and I get a syntax error if I change GetRan(444,[id]) to GetRan([id]) in either place.

Thanks

 

[nancier]

Correction

Hi, I did some more analysis and have two more questions.

1.)I tested it trying to do just a single query and it comes up with an error saying this is a union query. I then remove the ( before Select and one of the ) after [id]. It then runs but comes up with a different 417 results than when it was a union query. Any idea why the results change?

New Select Query
SELECT DISTINCTROW TOP 417 tblAmount.Amt FROM tblAmount WHERE Amt between 00.01 and 10000.00 GROUP BY tblAmount.Amt, tblAmount.ID, GetRan(444,[id]) ORDER BY GetRan(444,[id]);

Old Union Query
(SELECT DISTINCTROW TOP 417 tblAmount.Amt FROM tblAmount WHERE Amt between 00.01 and 10000.00 GROUP BY tblAmount.Amt, tblAmount.ID, GetRan(444,[id]) ORDER BY GetRan(444,[id]))
UNION ALL (SELECT DISTINCTROW TOP 200 tblAmount.Amt FROM tblAmount WHERE Amt between 10000.01 and 75000.00 GROUP BY tblAmount.Amt, tblAmount.ID, GetRan(222,[id]) ORDER BY GetRan(222,[id]))
UNION ALL (SELECT DISTINCTROW TOP 100 tblAmount.Amt FROM tblAmount WHERE Amt between 75000.01 and 99999999.99 GROUP BY tblAmount.Amt, tblAmount.ID, GetRan(666,[id]) ORDER BY GetRan(666,[id]));

2.)You'll notice I have GetRan(444,[id])in the Group By section and also in the Order By Section. Do you know if I need to put GetRan(444,[id]) in both places or can I just put GetRan([id]) in one the spots. I notice I get different results by changing the 444 number in either place and I get a syntax error if I change GetRan(444,[id]) to GetRan([id]) in either place.

Thanks

 

[PHV]

Anyway it's a nonsense to use DISTINCTROW and GROUP BY in the the same SELECT instruction.
I get a syntax error if I change GetRan(444,[id]) to GetRan([id])
Your GetRan function expects 2 not optional parameters.

Have you tried this ?
SELECT DISTINCT TOP 417 Amt FROM tblAmount WHERE Amt Between 00.01 And 10000.00 ORDER BY GetRan(1234567,[id]);
And this ?
SELECT DISTINCT TOP 417 Amt FROM tblAmount WHERE Amt Between 00.01 And 10000.00 ORDER BY GetRan(444,[id])
UNION ALL SELECT DISTINCT TOP 200 Amt FROM tblAmount WHERE Amt Between 10000.01 And 75000.00 ORDER BY GetRan(222,[id]))
UNION ALL SELECT DISTINCT TOP 100 Amt FROM tblAmount WHERE Amt Between 75000.01 And 99999999.99 ORDER BY GetRan(666,[id]));

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

 

[nancier]

Hi,

Both of those queries generate this error message.

ORDER BY clause (GetRan(444,[id])) conflicts with DISTINCT

Any idea how to fix it?

Thanks Again.

 

[PHV]

And what about simply remove the DISTINCT keyword ?

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

 

[nancier]

That seems to work great except the numbers are longer sorted by dollar amount. With the select query, when I sort them after the query is run, all the numbers change and I get 417 different numbers though this doesn't happen with the union query which is baffling. Is there any way to sort them by dollar amount in the query? Thanks

Select Query
SELECT TOP 417 tblAmount.Amt
FROM tblAmount
WHERE (((tblAmount.Amt) Between 0.01 And 10000))
ORDER BY GetRan(444,[id]);

Union Query
(SELECT TOP 417 Amt FROM tblAmount WHERE Amt Between 00.01 And 10000.00 ORDER BY GetRan(444,[id]))
UNION ALL (SELECT TOP 200 Amt FROM tblAmount WHERE Amt Between 10000.01 And 75000.00 ORDER BY GetRan(222,[id]))
UNION ALL (SELECT TOP 100 Amt FROM tblAmount WHERE Amt Between 75000.01 And 99999999.99 ORDER BY GetRan(666,[id]));

 

[PHV]

SELECT TOP 417 Amt
FROM tblAmount
WHERE Amt Between 0.01 And 10000
ORDER BY Amt, GetRan(444,[id]);


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

 

[nancier]

Hi and thanks for your help and patience.
I tried ORDER BY Amt, GetRan(444,[id]);
For some reason, it totally messes up the randomness of the sample. It gives the same numbers, about 20 of each which shouldn't be happening. Could it be that the group by clause was needed?

A couple other points.

I'm concerned that I get a one sample when I leave group by GetRan(444,[id]) in and a completely different one when I leave group by GetRan(444,[id]) out. Do you know why the samples would be different?

If I leave group by GetRan(444,[id]) out, then the sample isn't sorted by dollar amount. If I use your last suggestion and try to fix that by adding order by amt, the sample does not look random at all.