If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Random number query question
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. |
#2
|
|||
|
|||
Random number query question
Hi Bob,
Yes, there is a way. Do they need to be unique? Overall or just per quarter? Clifford Bass "Bob Waggoner" wrote: 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. |
#3
|
|||
|
|||
Random number query question
I'd like them to be unique, but that's not as important as just different
each quarter. "Clifford Bass" wrote: Hi Bob, Yes, there is a way. Do they need to be unique? Overall or just per quarter? Clifford Bass "Bob Waggoner" wrote: 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. |
#4
|
|||
|
|||
Random number query question
On Fri, 19 Dec 2008 06:20:01 -0800, Bob Waggoner
wrote: 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 [MVP] |
#5
|
|||
|
|||
Random number query question
You manged to find my achillies heel. I don't know how to call functions.
"John W. Vinson" wrote: On Fri, 19 Dec 2008 06:20:01 -0800, Bob Waggoner wrote: 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 [MVP] |
#6
|
|||
|
|||
Random number query question
On Fri, 19 Dec 2008 10:00:01 -0800, Bob Waggoner
wrote: 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 [MVP] |
#7
|
|||
|
|||
Random number query question
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 "John W. Vinson" wrote: On Fri, 19 Dec 2008 10:00:01 -0800, Bob Waggoner wrote: 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 [MVP] |
#8
|
|||
|
|||
Random number query question
On Fri, 19 Dec 2008 10:57:00 -0800, Bob Waggoner
wrote: 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 [MVP] |
#9
|
|||
|
|||
Random number query question
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" wrote: I'd like them to be unique, but that's not as important as just different each quarter. |
#10
|
|||
|
|||
Random number query question
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 "John W. Vinson" wrote: On Fri, 19 Dec 2008 10:57:00 -0800, Bob Waggoner wrote: 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 [MVP] |
|
Thread Tools | |
Display Modes | |
|
|