Access db: select one row randomly, but taking into account a weight

I looked at SQL : select one row randomly, but taking into account a weight

and there i find something i need, but i need it on an Access db.

I'm using ColdFusion to build my page and i want to output images. I also want to give a "weight" to the image, so the images with a higher number will be shown more often than the ones with a lower number in weight.

I will be outputting 1 image at a time and on refresh i want to show another (random, taking in account the "weight") image.

Anybody go开发者_如何转开发t an idea on this matter ?

There's probably a way to do this in MS Access as well, but I don't have it up and running to play with. This solution will work with any database.

<cfscript>
/* equivalent to SELECT id,name,weight FROM images ORDER BY name */
images = queryNew("id,name,weight", "integer,varchar,integer");

for (i=1; i<=4; i++) {
    queryAddRow(images);
    querySetCell(images, "id", i);
    querySetCell(images, "name", "Image #i#");
}

querySetCell(images, "weight", 20, 1);
querySetCell(images, "weight", 30, 2);
querySetCell(images, "weight", 50, 3);
querySetCell(images, "weight", 100, 4);
</cfscript>

<cfset totalScore = 0>
<cfset scores = []>
<cfloop query="images">
    <cfset totalScore += weight>
    <cfset arrayAppend(scores, totalScore)>
</cfloop>

<cfset selectionScore = randRange(1,totalScore)>

<cfloop from="1" to="#arrayLen(scores)#" index="rowNumber">
    <cfset score = scores[rowNumber]>
    <cfif selectionScore LTE score>
        <cfbreak/>
    </cfif>
</cfloop>

<cfoutput>
#selectionScore#<br />
#images.id[rowNumber]#<br />
#images.name[rowNumber]#<br />
#images.weight[rowNumber]#
</cfoutput>