Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Feb 2001
    Location
    Georgia
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access Random Sampling (WinXP/Access2003)

    I am looking to create from an Access table of approx 92K records a random sampling of 400 records. These are names and addresses from wich I want to select ,for example, the city of Columbus and from those records (perhaps 800) I want a random sampling of 400. If any can help please make this as simple of a solution as possible as I am not very conversant with VB. Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access Random Sampling (WinXP/Access2003)

    You can do this without VBA.
    Create a query based on your table.
    Add the fields you need to the query grid.
    Specify the selection criteria ("Columbus" under City).
    Add a calculated column

    =Rnd([Field])

    where you can substitute any number field for Field.
    Sort on this column.
    Click in an empty part of the upper pane of the query window.
    Activate the Properties window.
    Enter 400 in the Top Values property.
    Each time you switch to datasheet view, a random sampling will be taken. You can copy the selected records, or export them, or make the query into a Make Table query.

  3. #3
    New Lounger
    Join Date
    Feb 2001
    Location
    Georgia
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Random Sampling (WinXP/Access2003)

    Thanks HansV for your quick response however when I attempted to use the field zip as argument to RND function I would get "type mismatch" error so I decided to use a simple number (8) as seed but then I get a dialog box to open asking to "Enter Parameter Value" to which I tried another "8" to which I get a message indicating I have attempted some complex function. I appreciate your help 'cause I'm gonna need it.
    Thanks,

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access Random Sampling (WinXP/Access2003)

    Zip is probably a text field; you can only use a number field. You can't use a constant number either, it needs to be a field from the table. If you don't have a number field in the table, you can add an AutoNumber field; this will be populated automatically.
    Oh, and you must enclose the field name in square brackets, as in my example.

  5. #5
    New Lounger
    Join Date
    Feb 2001
    Location
    Georgia
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Random Sampling (WinXP/Access2003)

    Thanks Hans, I had a little difficulty in creating the additional field for the Autonumber since it barks when you do this on an existing table. I simply created the subset city first then took a random sampling from the exported Excel file. I do appreciate all your help, now to find a good Access Book.
    Regards,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •