Results 1 to 9 of 9
  1. #1
    webisti is offline The Wisher
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    115

    Randomizing based on criteria

    Hi I have created a db where I need to run a report based on randomizing.
    Basically there is a table ORDERS that is updated daily with orders from clients.

    Table structure is:
    ORDER ID (autonumber).
    CLIENT.
    ORDER.
    Quantity,
    Date.

    Sampling criteria is:
    if number of Orders is from:
    1-10 show me 90% of them
    11-20 show me 85 % of them
    21-100 show me 51% of them etc

    I did found this code for the randomizing with autonumber:
    Private Sub cmdSelect_Click()
    Dim SQL As String
    Dim sWhere As String
    Dim iCount As Integer
    Dim i As Integer
    Dim j As Integer
    Dim iSelectHowMany As Integer
    Dim aSel() As Integer
    Dim IsRepeated As Boolean
    Dim vItem As Variant

    iSelectHowMany = 3

    SQL = "Select * from [tblORDERS]"

    ' Find number of records in table.
    iCount = DCount("*", "tblORDERS")

    Randomize
    i = Int(iCount * Rnd()) + 1
    ReDim aSel(0)
    aSel(0) = i

    j = 1
    Do While j < iSelectHowMany
    IsRepeated = False
    Do While Not IsRepeated
    i = Int(iCount * Rnd()) + 1

    For Each vItem In aSel
    If vItem = i Then
    IsRepeated = True


    Exit For
    End If
    Next vItem

    If Not IsRepeated Then
    ReDim Preserve aSel(j)
    aSel(j) = i
    j = j + 1
    Exit Do
    End If
    Loop
    Loop

    For Each vItem In aSel
    sWhere = sWhere & ", " & vItem
    Next
    SQL = SQL & " where [OrderID] in (" & Mid(sWhere, 2) & ")"

    Me.subform.Form.RecordSource = SQL
    End Sub

    Can anyonr Help me how to integrate into this code my sampling?

    Thanks a lot

    Webisti

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    That is some pretty cool code you have there. Try this....

    Replace the code just after the declarations and just before "Randomize" with this


    Code:
    'iSelectHowMany = 3
    SQL = "Select * from [tblORDERS]"
    ' Find number of records in table.
    iCount = DCount("*", "tblORDERS")
    
    Dim sglPerc As Single
    Select Case iCount
    Case 1 To 10
    sglPerc = 0.9
    Case 11 To 20
    sglPerc = 0.85
    Case 21 To 100
    sglPerc = 0.51
    Case Else
    sglPerc = 0.1
    End Select
    iSelectHowMany = iCount * sglPerc

  3. #3
    John_G is offline VIP
    Windows XP Access 2003
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Hi -

    A little internet seaching found this:

    http://www.carlj.ca/2007/12/16/selec...ords-with-sql/

    I think it will lead you to what you want with less code. You would do the same thing, i.e. generate a SQL string that looks something like this:

    select top 10 * from Job_Statement where job_statement_id > 0 order by rnd(Statement_id)

    I tried this on my own database and it works perfectly - a different set of 10 records each time.

    HTH

    John

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    Interresting. I would never expect that Order By statement to function correctly.

  5. #5
    webisti is offline The Wisher
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    115
    and can I implement the results into button and make out of it a report?


    Quote Originally Posted by ItsMe View Post
    That is some pretty cool code you have there. Try this....

    Replace the code just after the declarations and just before "Randomize" with this


    Code:
    'iSelectHowMany = 3
    SQL = "Select * from [tblORDERS]"
    ' Find number of records in table.
    iCount = DCount("*", "tblORDERS")
    
    Dim sglPerc As Single
    Select Case iCount
    Case 1 To 10
    sglPerc = 0.9
    Case 11 To 20
    sglPerc = 0.85
    Case 21 To 100
    sglPerc = 0.51
    Case Else
    sglPerc = 0.1
    End Select
    iSelectHowMany = iCount * sglPerc

  6. #6
    webisti is offline The Wisher
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    115
    I tried to add a code like
    On Error GoTo ErrorHandler
    Dim strSQL as String
    Dim strTable as String
    strTable = "tblreporting"
    'Delete the table if it exists
    DoCmd.DeleteObject acTable, strTable
    strSQL = "Select * INTO " & strTable & " FROM tblorders " & _
    "Where order= ''"
    Currentdb.Execute strSQL
    Exit Sub
    ErrorHandler:
    IF Err.Number = 7874 Then
    Resume Next 'Tried to delete a non-existing table, resume
    End If
    End Sub

    SORRY FOR THE CONFUSSION
    I already slocved this issue
    Last edited by webisti; 12-18-2013 at 10:59 AM.

  7. #7
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    I guess it is a good thing your code did not work unless you are trying to delete table objects in your DB.

    I can only guess what you are trying to do with your results. Why don't you explain your goal? Are you trying to filter a percentage of records within a form's recordset?

  8. #8
    webisti is offline The Wisher
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    115
    basically What I wanted is to create a temp table with those results filtered and out of that table print a report.
    Solved though pretty fast.
    Thanks for your inputs
    ' create a temp table and then print that table
    strSQL = strSQL & "SELECT tblOrders.* INTO REPORT_Orders FROM tblOrders " & " where [AUTO_NR] in (" & Mid(sWhere, 4) & ")"
    CurrentDb.Execute (strSQL)
    and then on the report I added:
    Private Sub PRINT_Click()
    If MsgBox("Are you sure you want to print the report?", _
    vbQuestion + vbYesNo) = vbYes Then
    DoCmd.PrintOut

  9. #9
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    I quick and easy way would be to assign the SQL directly to the report via public string variable declared in a general module. This would be faster and easier than create temp tables.

    gstrSQL = ""
    gstrSQL = SQL

    and then in your report's On Open event you could have
    Me.RecordSource = gstrSQL

    Or you could use openargs in the Docmd statement and then
    Me.RecordSource = Me.OpenArgs
    in the open event of the report

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Newest dates based on criteria
    By benjammin in forum Queries
    Replies: 13
    Last Post: 05-15-2012, 02:49 PM
  2. Criteria based on value in another field
    By karusya in forum Access
    Replies: 1
    Last Post: 04-03-2012, 01:46 PM
  3. Criteria based on another field
    By jlclark4 in forum Queries
    Replies: 12
    Last Post: 02-14-2012, 12:29 PM
  4. Replies: 1
    Last Post: 06-12-2011, 07:08 AM
  5. IIF Criteria based on another table
    By BED in forum Queries
    Replies: 6
    Last Post: 11-24-2010, 01:55 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums