Random Query

[AROSSATTY]

I have a very simple Access 2010 table. Two fields: (1) Primary key & (2) Name. There are approximately 50 names in the table. I want to randomly select 16 names from the 50 and randomly sort the 16 names. I want to run the query multiple times and always get a different 16 names and different sort order. I'm not all that versed in SQL.

 

[CJ_London]

To get the random numbers you could use the Rnd function - here is a link

http://www.techonthenet.com/access/functions/numeric/rnd.php

Assuming your primary key is incremental and there are no missing numbers you would use the min and max id as your upper and lower limits.

If you have 'missing' primary keys you will need a routine which keeps looping until have picked 16 numbers (rather than just looping 16 times)

If you don't want a name to be picked in a following query you will need some method to avoid picking again - tho' you will only be able to run the routine 3 times before you run out of names

Not sure what you mean by a random sort so can't help with that.

 

[ScottBeatty]

I have a question to add on to this. What if I import tables daily that do not necessarily have a consisten upper bound and lower bound? Is there a way to have enter a standardized code that when I import the table from excel, it will automatically generate random numbers?

 

[CJ_London]

Not quite sure what you are getting at, but have a look at using autonumber set to random - does this meet your needs?

 

[ScottBeatty]

well I suppose. However I much prefer to click run query for a number of times with the random integer code. I just do not know how to set it up if my upperbound is variable based on the data I import from different Excel files.