order by in inner select

[john.muscat]

Hi all,

I have the below statement that basically looks in one table
(tblActive), gets the row number, and then inserts that and the name
into another table (tblpos).

I would like to modify it slightly so that its looks up the first table
at random. At the moment it seems to get it alphabetically.

Can this be done? I have tried doing an order by (lowercase) before the
")" but I can not figure out the correct syntax.

strSQL = "SELECT (select count(*)" & _
"from tblActive as c2 " & _
"where c2.Name < c1.Name)+1 AS RowNumber, c1.Name,
[RowNumber] AS Pos INTO tblpos2 " & _
"FROM tblActive AS c1 " & _
"ORDER BY c1.Name;"

Thanks in advance.

 

[Tom Wickerath]

Hi John,

Try the following KB article:

ACC2000: Sample Function to Return a Random Record from a Field
http://support.microsoft.com/?id=210468

Two Notes:
1.) Although the title of this article includes "ACC2000", the example
should work equally well with Access 2002 or 2003. If using Access 97,
substitute the "Microsoft DAO 3.51 Object Library" instead of the "Microsoft
DAO 3.6 Object Library".

2.) The title includes "Field" as the last word. I believe the author meant
to use the word "Table".


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

john muscat wrote:

Hi all,

I have the below statement that basically looks in one table
(tblActive), gets the row number, and then inserts that and the name
into another table (tblpos).

I would like to modify it slightly so that its looks up the first table
at random. At the moment it seems to get it alphabetically.

Can this be done? I have tried doing an order by (lowercase) before the
")" but I can not figure out the correct syntax.

strSQL = "SELECT (select count(*)" & _
"from tblActive as c2 " & _
"where c2.Name < c1.Name)+1 AS RowNumber, c1.Name,
[RowNumber] AS Pos INTO tblpos2 " & _
"FROM tblActive AS c1 " & _
"ORDER BY c1.Name;"

Thanks in advance.