Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filtered random selections (2003)

    I know the subject of random selections in Access tables has come up a few times before, but my problem is slightly more tricky. I have a table which has (among others) two "category" fields and a date field. There are several blank elements in the date field, and I want to populate these with randomly selected values from the same field, where the two categories match.

    In other words, if I have a blank date for a record where category 1 is "Apples" and category 2 is "France", I want to select at random a non-null date from the other records where category 1 is "Apples" and category 2 is "France". (Obviously if there are no such records, no action is required). The aim is to do this for every combination of category 1 and category 2, preferably in one fell swoop!

    Hopefully I've explained that sufficiently well! Any approach I can think of is fairly long-winded; does anyone know of a fairly quick and easy way to achieve this?

    Thanks,
    Waggers
    If at first you do succeed, you've probably missed something.

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

    Re: Filtered random selections (2003)

    A word of caution: the technique of filling in missing values in a dataset by replacing them with non-missing values is called imputation. If you select the non-missing values purely randomly, you are increasing the randomness of the data, and disturbing the correlation between variables.
    Statisticians have developed special techniques to take this into account. See for example The multiple imputation FAQ page. There are software libraries available for multiple imputation, several of them free, but as far as I know they are all for statistical software applications such as SAS and SPlus.

    If all this isn't important in your situation, it would be possible to create a simplistic routine in Access, but it would be nice to have a small demo database to try it out on.

  3. #3
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtered random selections (2003)

    Thanks Hans, it's always good to learn some new terminology to make me appear <img src=/S/clever.gif border=0 alt=clever width=15 height=15>. I'll take a good look at the website and come back if I still want to try it in Access. <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>
    Waggers
    If at first you do succeed, you&#39;ve probably missed something.

Posting Permissions

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