Results 1 to 13 of 13
  1. #1
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Gather random sample (97-->)

    I have undertaken an exercise to match records from two db tables. My client wishes to have a quality check of proposed matches. Any methods available to me that could allow me to create a table of random records for this purpose.

    I know how to do this in Excel but I have over 65535 records!
    Jerry

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

    Re: Gather random sample (97-->)

    Create a new query based on a query or table. Add the fields you need, plus a new column:

    Sample: Rnd([numberfield])

    where numberfield is any numeric field. Sort descending on this field. Clear the Show check box for this column
    Click in an empty part of the upper part of the query window, then activate the Properties window. Set the Top property to the number or precentage you want to sample.

    Each time you run the query, a different random sample will be drawn. You can change it into a make-table query.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Gather random sample (97-->)

    The method is sound, thanks. I have a small problem that the majority of fields are text and those that have a numerical value have null fields in some records.

    I have however created this code: IIf(Len([numberfield ])=0,"1000",Int(Rnd([numberfield ])*1000)) I get a #Error coming up in the blank field any ideas?
    Jerry

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

    Re: Gather random sample (97-->)

    Is "numberfield" a genuine number field, or is it a text field that contains numeric values?

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Gather random sample (97-->)

    I am using a formatted number field---not text!
    Jerry

  6. #6
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Gather random sample (97-->)

    Sorry should clarify more. if there is a number in the number field i get a random number. If the number field is null I get the #Error
    Jerry

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

    Re: Gather random sample (97-->)

    Try

    Sample: Rnd(Nz([numberfield]))

  8. #8
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Gather random sample (97-->)

    <img src=/S/cool.gif border=0 alt=cool width=15 height=15> I did Int(Rnd(Nz([numberfield]))*10000) just to get the numbers up. Just for the academic side why did Nz() rectify the problem
    Jerry

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

    Re: Gather random sample (97-->)

    The Nz function is used to get rid of null values. It has syntax

    Nz(value, valueifnull)

    Nz will return the first argument, but null values are replaced with the value specified in the second argument or with a default value.

    You can specify the replacement value explicitly in the second argument, for example

    Nz([Choice], 999)

    or

    Nz([Option], "unknown")

    If you omit the second argument, Access will use the default value for the data type, i.e. if value is numeric, null values will be replaced with 0, and if value is text, null values will be replaced with <code>""</code>.

    PS If you only use the random values to obtain a random sample, you don't need to use Int and *10000. But perhaps you use the values for some other purpose too.

  10. #10
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Gather random sample (97-->)

    Your PS is a good point, I was just thinking about it whilst I got my sandwich, I suppose I thought it looked "tidier" but there is no point as it is hidden and will vary the randomisation as some of the values may match. Thanks for the Nz description, very useful
    Jerry

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Gather random sample (97-->)

    Even though Nz behaves as described in 2000 and above, I had problems in Access 97 when using Nz in a query without a second argument. That may have been cured in one of the service packs, but it's been too long ago to recall precisely. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

  12. #12
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Gather random sample (97-->)

    Actually, that has taught me a lesson, to make an assumption that it would work in 97. I have a habit of putting 97--> in my version text box on initial post <img src=/S/blush.gif border=0 alt=blush width=15 height=15>
    Jerry

  13. #13
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Gather random sample (97-->)

    The most general difficulty with assuming things will work in 97 is that 97 was based on VB5, while 2000 - 2003 are based on VB6, which makes all sorts of differences. Usually, we'll note that something will or won't work in a particular version. If the poster doesn't mention that, one of the mods or WMVPs will usually jump in and add the proviso. At least you didn't use 2.0-->, which would have led to a much longer discussion ... since Nz was added with VBA! <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>
    Charlotte

Posting Permissions

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