Random number query question

[Bob Waggoner]

Is there a way to have a query generate random numbers and then use that to
populate a table? I need to have 70 random 3 digit numbers each quarter and
I'd like to click a command button instead of write the numbers one-by-one.

The table to populate is 64EmpSurveyNumbers.

 

[Clifford Bass]

Hi Bob,

Yes, there is a way. Do they need to be unique? Overall or just per
quarter?

Clifford Bass

 

[Bob Waggoner]

I'd like them to be unique, but that's not as important as just different
each quarter.

 

[John W. Vinson]

Is there a way to have a query generate random numbers and then use that to
populate a table? I need to have 70 random 3 digit numbers each quarter and
I'd like to click a command button instead of write the numbers one-by-one.

The table to populate is 64EmpSurveyNumbers.

Sure, with help from a little VBA. Put this 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 a vacant Field cell, where [fieldname] is any field in
your table - this forces Access to give a different random
number for each record.

Change the query to an Append query and append it to the desired random field.

 

[Bob Waggoner]

You manged to find my achillies heel. I don't know how to call functions.

Is there a way to have a query generate random numbers and then use that to
populate a table? I need to have 70 random 3 digit numbers each quarter and
I'd like to click a command button instead of write the numbers one-by-one.

The table to populate is 64EmpSurveyNumbers.

Sure, with help from a little VBA. Put this 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 a vacant Field cell, where [fieldname] is any field in
your table - this forces Access to give a different random
number for each record.

Change the query to an Append query and append it to the desired random field.

 

[John W. Vinson]

You manged to find my achillies heel. I don't know how to call functions.

Ummm...

Just like I suggested:

Then add a calculated field to your Query by typing

Shuffle: RndNum([fieldname])

in a vacant Field cell, where [fieldname] is any field in
your table - this forces Access to give a different random
number for each record.

Did you try it?

 

[Bob Waggoner]

I wasn't sure what to do. Do I put a command button on the form and call the
function with the button? I just haven't called any functions. I can do the
query part and the make table...its just the function that has me upside down.
Thanks

You manged to find my achillies heel. I don't know how to call functions.

Ummm...

Just like I suggested:

Then add a calculated field to your Query by typing

Shuffle: RndNum([fieldname])

in a vacant Field cell, where [fieldname] is any field in
your table - this forces Access to give a different random
number for each record.

Did you try it?

 

[John W. Vinson]

I wasn't sure what to do. Do I put a command button on the form and call the
function with the button? I just haven't called any functions. I can do the
query part and the make table...its just the function that has me upside down.
Thanks

Open the Query in design view.

Look at the top row that says Fields. Find the first vacant Field cell in that
row.

In that blank cell type

Shuffle: RandNum([xyz])

where xyz is the name of some field in your table, such as the primary key.

When you run the query, the *query* will call the function. You don't need to
do so separately. You may want to replace "Shuffle" with the desired name of
your random number field.

 

[Clifford Bass]

Hi Bob,

It gets a little involved to prevent duplicates over more than one
quarter. The following will do so until it cannot prevent them, then it will
prevent them to the extent it can. So if you already have 12 quarters worth
of data (840 numbers) it will prevent for the prior 11 quarters. I am
presuming that by three digits you want to use only 100-999. If that is not
the case, you can adjust the following as needed. This assumes the presence
of SurveyYear, SurveyQuarter and SurveyNumber fields in your table.

=====================================
Public Sub GenerateSurveyNumbers(ByVal intSurveyYear As Integer, ByVal
intSurveyQuarter As Integer, ByVal intNumbersToGenerate)

Const cintLowerBound As Integer = 100
Const cintUpperBound As Integer = 999
Const cintMaximumNumbers As Integer = cintUpperBound - cintLowerBound + 1

Dim cnnCurrent As ADODB.Connection
Dim rstSurveyInfo As New ADODB.Recordset
Dim boolarrNumberUsed(cintLowerBound To cintUpperBound) As Boolean
Dim intIndex As Integer
Dim intNumberCount As Integer
Dim intNumbersUsed As Integer
Dim intSurveyNumber As Integer
Dim strWhere As String

Set cnnCurrent = CurrentProject.Connection

If intNumbersToGenerate >= 1 And intNumbersToGenerate <=
cintMaximumNumbers Then
' Remove any existing records for the specified year and quarter;
allows rerunning of process
cnnCurrent.Execute _
"delete from 64EmpSurveyNumbers " & _
"where SurveyYear = " & intSurveyYear & " and SurveyQuarter =
" & intSurveyQuarter, , adCmdText
' Figure out how many surveys' information to preload so as to avoid
the same numbers, at least for a time
With rstSurveyInfo
.Open _
"select SurveyYear, SurveyQuarter, count(*) as NumberCount "
& _
"from 64EmpSurveyNumbers " & _
"where SurveyNumber between " & cintLowerBound & " and " &
cintUpperBound & " " & _
"group by SurveyYear, SurveyQuarter " & _
"order by SurveyYear desc, SurveyQuarter desc", _
cnnCurrent, adOpenStatic, adLockReadOnly, adCmdText
intNumbersUsed = intNumbersToGenerate
Do While Not .EOF
intNumberCount = .Fields("NumberCount").Value
If intNumbersUsed + intNumberCount > cintMaximumNumbers Then
Exit Do
End If
intNumbersUsed = intNumbersUsed + intNumberCount
.MoveNext
Loop
If .EOF Then
' Plenty of numbers left; preload all survey numbers
strWhere = ""
Else
.MovePrevious
If .BOF Then
' Only one survey, which did not leave enough numbers
for this one; do not preload any
strWhere = "SurveyYear is null and "
Else
' Only preload surveys after this year and quarter
strWhere = _
"(SurveyYear > " & .Fields("SurveyYear").Value & "
or (SurveyYear = " & _
.Fields("SurveyYear").Value & " and SurveyQuarter

= " & .Fields("SurveyQuarter").Value & ")) and "

End If
End If
.Close

' Initialize the array
For intIndex = cintLowerBound To cintUpperBound
boolarrNumberUsed(intIndex) = False
Next intIndex

' Block out the existing numbers
.Open _
"select SurveyYear, SurveyQuarter, SurveyNumber " & _
"from 64EmpSurveyNumbers " & _
"where " & strWhere & "SurveyNumber between " &
cintLowerBound & " and " & cintUpperBound, _
cnnCurrent, adOpenDynamic, adLockOptimistic, adCmdText
Do While Not .EOF
boolarrNumberUsed(.Fields("SurveyNumber").Value) = True
.MoveNext
Loop

' Now generate random numbers until the needed unique quantity
has been reached
Randomize Now()
intNumberCount = 0
Do Until intNumberCount >= intNumbersToGenerate
' Use the handy formula from help on Rnd() function
' To produce random integers in a given range, use this
formula:
' Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
intIndex = Int((cintUpperBound - cintLowerBound + 1) * Rnd()
+ cintLowerBound)
If Not boolarrNumberUsed(intIndex) Then
' An unused number
.AddNew
.Fields("SurveyYear").Value = intSurveyYear
.Fields("SurveyQuarter").Value = intSurveyQuarter
.Fields("SurveyNumber").Value = intIndex
.Update
boolarrNumberUsed(intIndex) = True
intNumberCount = intNumberCount + 1
End If
Loop
.Close
End With
Set rstSurveyInfo = Nothing
MsgBox "Numbers generated."
Else
MsgBox "Bad input."
End If
Set cnnCurrent = Nothing

End Sub

=============================================

To use it, copy the code to a new module or an existing one that is not
a form or report module. Then on a form create an On Click event for a
button and that event's VBA code call the routine. Something like:

Private Sub cmdGenerateSurveyNumbers_Click()

GenerateSurveyNumbers 2008, 4, 70

End Sub

Obviuosly you will want to replace the 2008 and 4 and maybe the 70 with
text boxes from the form; into which the user would specify the values to use.

Hope that helps,

Clifford Bass

 

[Bob Waggoner]

Sorry it took so long for me to get back to you on this. The holidays & All...
I have this in my query - with the function you wrote earlier in the modules
tab.

Shuffle: RndNum([ValidationNum])
The problem is, I get "compile error in query expression
'RndNum([validationNum])'"

Here's the code in the 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

I wasn't sure what to do. Do I put a command button on the form and call the
function with the button? I just haven't called any functions. I can do the
query part and the make table...its just the function that has me upside down.
Thanks

Open the Query in design view.

Look at the top row that says Fields. Find the first vacant Field cell in that
row.

In that blank cell type

Shuffle: RandNum([xyz])

where xyz is the name of some field in your table, such as the primary key.

When you run the query, the *query* will call the function. You don't need to
do so separately. You may want to replace "Shuffle" with the desired name of
your random number field.

 

[John W. Vinson]

Sorry it took so long for me to get back to you on this. The holidays & All...
I have this in my query - with the function you wrote earlier in the modules
tab.

Shuffle: RndNum([ValidationNum])
The problem is, I get "compile error in query expression
'RndNum([validationNum])'"

Here's the code in the 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

Open the module in the VBA editor. Select Debug... Compile <my database>.

Do you get an error message? If so on what line, and what's the error?

 

[Bob Waggoner]

John,
I'd really like to shake your hand. Thanks for being so insightful. It
worked. I had a few compile errors that needed to be cleaned up. The problem
with this particular code was the lack of ' in front of comments. I should'a
known better. Anyway, thanks!

Sorry it took so long for me to get back to you on this. The holidays & All...
I have this in my query - with the function you wrote earlier in the modules
tab.

Shuffle: RndNum([ValidationNum])
The problem is, I get "compile error in query expression
'RndNum([validationNum])'"

Here's the code in the 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

Open the module in the VBA editor. Select Debug... Compile <my database>.

Do you get an error message? If so on what line, and what's the error?

 

[John W. Vinson]

John,
I'd really like to shake your hand. Thanks for being so insightful. It
worked. I had a few compile errors that needed to be cleaned up. The problem
with this particular code was the lack of ' in front of comments. I should'a
known better. Anyway, thanks!

Glad I was able to point you in the right direction; good luck!