Microsoft KB Archive/170986: Difference between revisions

From BetaArchive Wiki
m (Text replacement - ">" to ">")
m (Text replacement - "&" to "&")
 
(One intermediate revision by the same user not shown)
Line 54: Line 54:
This article demonstrates a method that you can use to fill a table with random records from another table. The sample procedure in this article uses Data Access Objects (DAO) to fill a table with a user-specified number of records from an existing table. The following example randomly selects records from the Orders table in the Northwind sample database and adds them to a new table.<br />
This article demonstrates a method that you can use to fill a table with random records from another table. The sample procedure in this article uses Data Access Objects (DAO) to fill a table with a user-specified number of records from an existing table. The following example randomly selects records from the Orders table in the Northwind sample database and adds them to a new table.<br />
<br />
<br />
This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the &quot;Building Applications with Microsoft Access&quot; manual.
This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.


</div>
</div>
Line 63: Line 63:
One way that you can get a certain number of random records from a table is to create a query based on the table. You can create an expression that applies the Rnd() function to a Number field in the table; if there is no Number field available, you can apply the Len() function to a text field and then apply the Rnd() function to the result. Next, you set the sort order of this calculated field to ascending or descending, and then select from the table the other fields that you want to see in the query result. Finally, you set the query's TopValues property to the number of records you want.<br />
One way that you can get a certain number of random records from a table is to create a query based on the table. You can create an expression that applies the Rnd() function to a Number field in the table; if there is no Number field available, you can apply the Len() function to a text field and then apply the Rnd() function to the result. Next, you set the sort order of this calculated field to ascending or descending, and then select from the table the other fields that you want to see in the query result. Finally, you set the query's TopValues property to the number of records you want.<br />
<br />
<br />
However, there is a disadvantage to this method. When you start Microsoft Access and run the query, a particular set of records is returned, which can be referred to as &quot;Recordset A.&quot; You may run the query several more times with different results, and then quit Microsoft Access. The next time you start Microsoft Access and run the query, the result is again Recordset A.<br />
However, there is a disadvantage to this method. When you start Microsoft Access and run the query, a particular set of records is returned, which can be referred to as "Recordset A." You may run the query several more times with different results, and then quit Microsoft Access. The next time you start Microsoft Access and run the query, the result is again Recordset A.<br />
<br />
<br />
One resolution is to run a procedure that uses DAO to fill a table with a specified number of records from an existing table; this procedure also uses the Randomize statement to reinitialize the random-number generator. The following steps demonstrate how to use the sample subroutine BuildRandomTable.<br />
One resolution is to run a procedure that uses DAO to fill a table with a specified number of records from an existing table; this procedure also uses the Randomize statement to reinitialize the random-number generator. The following steps demonstrate how to use the sample subroutine BuildRandomTable.<br />
Line 106: Line 106:
         Set dbsRandom = CurrentDb
         Set dbsRandom = CurrentDb
         ' Delete any existing records from tblRandom table.
         ' Delete any existing records from tblRandom table.
         dbsRandom.Execute &quot;Delete * from tblRandom;&quot;
         dbsRandom.Execute "Delete * from tblRandom;"
         ' Open Orders as a Table Type recordset.
         ' Open Orders as a Table Type recordset.
         Set rstOrders = dbsRandom.OpenRecordset(&quot;Orders&quot;, dbOpenTable)
         Set rstOrders = dbsRandom.OpenRecordset("Orders", dbOpenTable)
         rstOrders.MoveFirst
         rstOrders.MoveFirst
         LowerLimit = rstOrders!OrderID
         LowerLimit = rstOrders!OrderID
Line 115: Line 115:
         lngRecordCount = rstOrders.RecordCount
         lngRecordCount = rstOrders.RecordCount


         Set rstRandom = dbsRandom.OpenRecordset(&quot;tblRandom&quot;, _
         Set rstRandom = dbsRandom.OpenRecordset("tblRandom", _
                         dbOpenDynaset)
                         dbOpenDynaset)
         lngCounter = 1
         lngCounter = 1
Line 122: Line 122:
         ' records requested is reasonable.
         ' records requested is reasonable.
         If lngRequest > lngRecordCount Then
         If lngRequest > lngRecordCount Then
             MsgBox &quot;Request is greater than the total number of records.&quot;
             MsgBox "Request is greater than the total number of records."
             Exit Sub
             Exit Sub
         Else
         Else
Line 134: Line 134:
               * Rnd + LowerLimit)
               * Rnd + LowerLimit)
         ' Ensure that it exists in the Orders table.
         ' Ensure that it exists in the Orders table.
         rstOrders.Index = &quot;PrimaryKey&quot;
         rstOrders.Index = "PrimaryKey"
         rstOrders.Seek &quot;=&quot;, lngGuess
         rstOrders.Seek "=", lngGuess
         If rstOrders.NoMatch Then
         If rstOrders.NoMatch Then
             ' Drop through and generate a new number.
             ' Drop through and generate a new number.
         Else
         Else
             ' Check to see if it's already been used in the new table.
             ' Check to see if it's already been used in the new table.
             rstRandom.FindFirst &quot;lngOrderNumber =&quot; &amp; lngGuess
             rstRandom.FindFirst "lngOrderNumber =" & lngGuess
             ' If not, add it to the new table.
             ' If not, add it to the new table.
             If rstRandom.NoMatch Then
             If rstRandom.NoMatch Then
Line 173: Line 173:
[[../128874|128874]]: ACC: Find N Records in Random Order<br />
[[../128874|128874]]: ACC: Find N Records in Random Order<br />
<br />
<br />
For more information about the Rnd function and the Randomize statement, search the Help Index for &quot;random numbers.&quot;
For more information about the Rnd function and the Randomize statement, search the Help Index for "random numbers."


</div>
</div>

Latest revision as of 11:30, 21 July 2020

Knowledge Base


ACC: How to Fill a Table with Random Records from Another Table

Article ID: 170986

Article Last Modified on 1/20/2007



APPLIES TO

  • Microsoft Access 95 Standard Edition
  • Microsoft Access 97 Standard Edition



This article was previously published under Q170986

Advanced: Requires expert coding, interoperability, and multiuser skills.


SUMMARY

This article demonstrates a method that you can use to fill a table with random records from another table. The sample procedure in this article uses Data Access Objects (DAO) to fill a table with a user-specified number of records from an existing table. The following example randomly selects records from the Orders table in the Northwind sample database and adds them to a new table.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

MORE INFORMATION

One way that you can get a certain number of random records from a table is to create a query based on the table. You can create an expression that applies the Rnd() function to a Number field in the table; if there is no Number field available, you can apply the Len() function to a text field and then apply the Rnd() function to the result. Next, you set the sort order of this calculated field to ascending or descending, and then select from the table the other fields that you want to see in the query result. Finally, you set the query's TopValues property to the number of records you want.

However, there is a disadvantage to this method. When you start Microsoft Access and run the query, a particular set of records is returned, which can be referred to as "Recordset A." You may run the query several more times with different results, and then quit Microsoft Access. The next time you start Microsoft Access and run the query, the result is again Recordset A.

One resolution is to run a procedure that uses DAO to fill a table with a specified number of records from an existing table; this procedure also uses the Randomize statement to reinitialize the random-number generator. The following steps demonstrate how to use the sample subroutine BuildRandomTable.

  1. Open the sample database Northwind.mdb.
  2. Create the following table:

          Table: tblRandom
          -------------------------------
          Field Name: lngGuessNumber
             Data Type: Number
             Field Size: Long Integer
             Indexed: Yes (No Duplicates)
          Field Name: lngOrderNumber
             Data Type: Number
             Field Size: Long Integer
             Indexed: No
    
          Table Properties: tblRandom
          ---------------------------
          PrimaryKey: lngGuessNumber
                            
  3. Create a module and type the following line in the Declarations section if it is not already there:

          Option Explicit
                            
  4. Type the following procedure:

          Sub BuildRandomTable(lngRequest as Long)
    
             Dim dbsRandom As Database
             Dim rstOrders As Recordset
             Dim rstRandom As Recordset
             Dim UpperLimit As Long
             Dim LowerLimit As Long
             Dim lngCounter As Long
             Dim lngGuess As Long
             Dim lngRecordCount As Long
    
             ' Assumes that this module is in the Northwind database.
             Set dbsRandom = CurrentDb
             ' Delete any existing records from tblRandom table.
             dbsRandom.Execute "Delete * from tblRandom;"
             ' Open Orders as a Table Type recordset.
             Set rstOrders = dbsRandom.OpenRecordset("Orders", dbOpenTable)
             rstOrders.MoveFirst
             LowerLimit = rstOrders!OrderID
             rstOrders.MoveLast
             UpperLimit = rstOrders!OrderID
             lngRecordCount = rstOrders.RecordCount
    
             Set rstRandom = dbsRandom.OpenRecordset("tblRandom", _
                             dbOpenDynaset)
             lngCounter = 1
    
             ' Check to make sure the number of
             ' records requested is reasonable.
             If lngRequest > lngRecordCount Then
                MsgBox "Request is greater than the total number of records."
                Exit Sub
             Else
                lngRequest = lngRequest + 1
             End If
    
             Randomize
             Do Until lngCounter = lngRequest
                ' Generate a random number
                lngGuess = Int((UpperLimit - LowerLimit + 1) _
                   * Rnd + LowerLimit)
             ' Ensure that it exists in the Orders table.
             rstOrders.Index = "PrimaryKey"
             rstOrders.Seek "=", lngGuess
             If rstOrders.NoMatch Then
                ' Drop through and generate a new number.
             Else
                ' Check to see if it's already been used in the new table.
                rstRandom.FindFirst "lngOrderNumber =" & lngGuess
                ' If not, add it to the new table.
                If rstRandom.NoMatch Then
                    With rstRandom
                        .AddNew
                        !lngGuessNumber = lngCounter
                        !lngOrderNumber = lngGuess
                        .Update
                    End With
                    lngCounter = lngCounter + 1
                End If
              End If
              Loop
              ' Clean up.
              dbsRandom.Close
          End Sub
                            
  5. To test this procedure, type the following line in the Debug window, and then press ENTER:

    BuildRandomTable 5


REFERENCES

For more information about using a query to select random records, please see the following article in the Microsoft Knowledge Base:

128874: ACC: Find N Records in Random Order

For more information about the Rnd function and the Randomize statement, search the Help Index for "random numbers."


Additional query words: seed

Keywords: kbcode kbhowto KB170986