Access DB - Random record query...

Is there anyway to pull a random record from the database using giving priority to certain records?

For example, I got a table with 4 fields for a banner rotator:

BannerID
BannerImage
TargetURL
Weight

The way should determine the frequency in which the banner if displayed, for example if banner1's weight is 100 and banner2 is 200 then banner2 should be displayed twice as much as banner1.

Is there any way to do that with access.?

If not what would be the best way to do it with ASP.? I was thinking of pulling all the records to an array and make duplicates according to the weight, for example allow only to set the weight from 1 to 10 and if I banner1's weight is 3 and banner2's is 2 then the array would have 3 values for banner one and and two for banner2 like:

arr(0) = banner1
arr(1) = banner1
arr(2) = banner1
arr(3) = banner2
arr(4) = banner2

And then pick a random record from the array, but I feel that wouldn't be the best way to do it, any suggestions?

Thanks

I've found getting a random record is a bit hard to do from a query point of view. Especially with weighting as random numbers are normally evaluated once per query, not on a per-record basis.

Your best bet is to do something like this in asp:

1) Put everything into an array but dont repeat rows.
2) Find a total by adding the weighting of each record (loop through)
3) Get a random number and find the related record by looping throught and adding the weights until you go past the random number.

For an easy way to get a record set to an array, check out the GetRows Function.

Edited by psycotik