SQL: Random number from compound string key?

Question

http://www.carlj.ca/2007/12/16/selecting-random-records-with-sql says that one can generate a random field value for each record by the expression Rnd(Field1), where Field1 is the name of your primary auto-numbered key field.  I'm working with a View which has the effective primary key composed of 2 string fields.  That is, the two string fields together uniquely identify a record.  Using only SQL-92 as provided by Access 2010 (avoiding VBA), is there a query expression converts the two strings into a number for use as the argument to Rnd()?

Answer 1

This works:

   CREATE VIEW tBadPart AS

   SELECT TOP 100 * FROM ( SELECT Part FROM SourceTable ORDER BY getRndVal(Part) )

This does not:

   CREATE VIEW tBadPart AS

   SELECT TOP 100 Part FROM SourceTable ORDER BY getRndVal(Part)

The latter causes an error about only allow simple queries in creating views.

But for me, the source table was so big that it took a few seconds to create the random column and sort by it.  So I decided against a view and created a table instead.  It is, after all, a small table being created from a very big table.  This allowed to to avoid the bracketed nesting.

Answer 2

Since the existing table is just a View, I suppose one way to get a random number is to generate an extra column containing random values in the query that creates the view.  However, the problem seems to be the same as in my original post -- to get an expression that results in a random value that varies by the record.

http://www.techrepublic.com/blog/how-do-i/how-do-i-retrieve-a-random-set-of-records-in-microsoft-access describes the use of a VBA function.  I guess there's no avoiding VBA.

Answer 3

Actually, I did run into that idea in rummaging around the web, but ASC only returns the code for the 1st character.  The effective key (I didn't make it an actual key in the database) is the complete strings from two fields.

Answer 4

Using only SQL-92 as provided by Access 2010 (avoiding VBA), is there a query expression converts the two strings into a number for use as the argument to Rnd()?

Try this --

   Rnd(Asc([UrString]))