MS SQl server lets you order by newid()

ie.
select * from My_Table
order by newid()

I have no clue if this works in Access or not.


Wil Genovese

One man with courage makes a majority.
-Andrew Jackson

A fine is a tax for doing wrong. A tax is a fine for doing well.

On Oct 22, 2008, at 9:17 AM, Keith McGee wrote:

> I created a random record generator for pulling current news items.  
> I am using an access database for the back end and would like to  
> know if anyone has a better solution. This code works, but I think  
> I'm missing something. Thank you in advance Keith.
>
> Step 1: Pull the current records and get a count.
> <cfquery name="get_news_count" datasource="#dsn#">
>    select fld_page_ID
>    from tbl_marketing_pages
>    where fld_item_type = "n"
>          and fld_active_date <= #now()#
>          and fld_inactive_date >= #now()#
> </cfquery>
>
> Step 2: Generate a random number from our count.
> <cfset intRandomNumber = randRange(get_news_count.recordcount,  
> listLen(get_news_count.recordcount))>
>
> Step 3: Output the query starting with the row equal to our random  
> number and only output one row.
> <cfoutput query="get_news_count" startrow="#intRandomNumber#"  
> maxrows="1">
>
> Step 4: Set a random record id with the record ID that came back  
> from our query.
>       <cfset randomID = #fld_page_ID#>
> </cfoutput>
>
> Step 5: Pull all the information from using our random record ID in  
> our where statement.
> <cfif get_news_count.fld_page_ID GT "">
>
> <cfquery name="get_news" datasource="#dsn#">
>       select fld_title, fld_body, fld_attach_ID, fld_url,  
> fld_active_date, fld_inactive_date
>    from tbl_marketing_pages
>    where fld_page_ID = #randomID#
> </cfquery>
>
>
> 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f

Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314221
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Reply via email to