Access DB - Random record query...
Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Database Discussion
Forum Description: Discussion and chat on database related topics.
URL: https://forums.webwiz.net/forum_posts.asp?TID=8227
Printed Date: 30 March 2026 at 1:57pm
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com
Topic: Access DB - Random record query...
Posted By: fernan82
Subject: Access DB - Random record query...
Date Posted: 19 December 2003 at 1:13pm
|
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 ------------- FeRnAN http://www.danasoft.com/"> 
|
Replies:
Posted By: vijay
Date Posted: 24 December 2003 at 2:08am
|
How about a counter field that increments with every display of banner, and verifying the counter and display accoring to a formula. For example, if you have set priorities 1,2,3 for three banners, the counter field increments up to 1 for banner one. When the next banner needs to be displayed, it checks the banner one, if the counter is one, it goes to the next one and checks the count. If the count is less than 2, then it displays that banner. If the count is already equal to 2, then moves on to the third banner. Say, the third banner needs to be displayed 3 times for every one display of first banner. Then it checks the third banner whether it was displayed 3 times. If so, it resets all counter fields and restarts with first banner. If not, it displays the third banner until it reaches count 3. Hope this helps. Happy holidays!
------------- vijay http://www.certexams.com - CCNA |
Posted By: psycotik
Date Posted: 25 December 2003 at 9:00pm
|
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. For an easy way to get a record set to an array, check out the http://www.devguru.com/Technologies/ado/quickref/recordset_getrows.html - GetRows Function . |
Posted By: fernan82
Date Posted: 26 December 2003 at 11:56pm
|
Thanks for the suggestions, I went with my initial idea as it seems
more efficient than anything else I can find. I made the weight limit
to 10 and just looped thru the recordset once and put all the ads on an
array making duplicates for the weight then get a random number and use
the array for that number. It's all in a sub-routine so everything is
cleared out once it's done. The ads are displayed about 8 times on each
page....
------------- FeRnAN http://www.danasoft.com/">
|