Hi ,
I constructed an MS access query in java to retrieve the rows in random. The code snipet
Connection con = DriverManager.g etConnection( database ,"","");
Statement s = con.createState ment()
strQry = "select top 10 S_No , Rnd(S_No) as exp from Questions order by Rnd(S_No) desc" ;
s.execute(strQr y) ;
This is the query I used to fetch the records in random. When i tried executing this code several times it fetches only the same set of records each time.
Actually my requirement is , for every time you query this , it should fetch different records.
Please help me in this.
Thanks in advance
Arun kumar
3 3912
Hi. Rnd() is evaluated once only in your expression, so it returns the same randomised number for all rows. You will need to fund another approach. One such is to create a small array, populate it with a list of random numbers between 1 and the number of records N, sort the list in ascending order, then loop through your recordset selecting the rows that correspond with the random numbers. I am not a Java programmer so am unable to advise further on the code you would need.
-Stewart
Hi. Rnd() is evaluated once only in your expression, so it returns the same randomised number for all rows. You will need to fund another approach. One such is to create a small array, populate it with a list of random numbers between 1 and the number of records N, sort the list in ascending order, then loop through your recordset selecting the rows that correspond with the random numbers. I am not a Java programmer so am unable to advise further on the code you would need.
-Stewart
Hi Stewart ,
Thanks for finding your time in replying me. I understood your point well.
Let me explain you my requirement in brief.
I am in a process of creating a online test portal.
There is a table called "Questions" which has 4000 rows in it. When a user takes test , some 30 questions has to be picked up randomly and displayed to the user.
When a second user takes test another set of random questions and displayed to him. There can be repeated questions in both the occasions , but should not be the identical as the previous one.
I can randomly pick questions using java by getting all the 4000 rows and randomising using java. But my worry is the performance .
Is there any easy way to achieve this in access query?
Thanks
Arun kumar
Hi Arun. I am sorry to say there is no easy way to do as you ask in an Access query. Access queries are based on an implementation of SQL, which in turn is based on set arithmetic. As a consequence of its set-based nature SQL has no direct concept of record number or position.
The random selection of records relies on record numbering, either express or implied, along with the generation of a set of random numbers. In SQL there is no native function which will number the rows of a query sequentially, hence why loop processing in code is necessary (making use of implied record numbering by counting the records within the loop).
There are other approaches that do not involve any programming. One I use myself to select randomised samples of student data is to export the main query details to Excel (about 6000 rows), number the rows of the table, generate a set of random numbers in another Excel sheet, and use the VLookup function on the numbered table to return the details from the randomised rows.
-Stewart
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: John Pastrovick |
last post by:
I use a function, myrandomPIN (), to generate random PIN numbers.
The following sql query updates records with the SAME PIN number but.
I want to generate DIFFERENT pin numbers for every record. The
function is ok but I can't figure out how to run it individually for
each record. In other words I do not know the correct syntax to use
UPDATE in a loop (if necessary) so that a different call to the
function is done every time or ecah...
|
by: Sidney Linkers |
last post by:
Hi,
I'm trying to make a calculated text field in a query where the textvalue is
being populated from multiple records.
I already use a VBA function to loop through records and concatenate the
text. It works but the performance is really bad. I'm looking for a solution
in plane SQL, so the looping is done by joining tables. I can't get it to
work....
|
by: Mark Reed |
last post by:
Hi all,
I have a query (query1) which shows scan date, scan time & operator. One
scan = 1 record. What I want to do is create a report based on query 2 from
query1 which shows all the scans AND the difference between each scan.
Something like below. I have spent days on this and can't figure it out.
Date Time Login Diff
04-Dec-03 23:33:12 27478
04-Dec-03 23:33:38 27478...
|
by: Manton |
last post by:
Access 2000.
I have a field in a table (tbl_data entry) which has a field called Free and
another field called SD (containing 2 options: 5 and 6)
The Free field is a yes/no format.
I'd like to run a query which upon entering a date range ("Enquiry Date")
value -allows the query to choose 170 records (by ticking off the yes/no
field "Free") randomly from tbl_data entry.
Alternatively: It could choose (ticking off the yes/no field "Free)...
|
by: Nhmiller |
last post by:
Is there a way to do this? Thanks.
Neil
Cat Paintings At Carol Wilson Gallery
http://www.carolwilsongallery.com
| |
by: Bernard Lebel |
last post by:
Hello,
I'm stumbled at a serious problem, and quite frankly getting
desparate. This is a rather long-winded one so I'll try to get
straight to the point.
I have this Python program, that performs MySQL queries to a database.
These queries are performed at regular intervals. Basically it is
looking for fields that match certain criterias. Such fields are not
present at all time. When the program finds such field, it then takes
|
by: John Fairhurst |
last post by:
Hi,
The following code should select the specified number of records randomly
from the database
<%
....
query = "SELECT FROM "
Set RS = Server.CreateObject("ADODB.Recordset")
|
by: srusskinyon |
last post by:
I need some help getting unique records from our database! I work for
a small non-profit homeless shelter. We keep track of guest
information as well as what services we have offered for statistical
purposes.
I've been using
Here's the situation:
I have two main tables:
|
by: padmaneha |
last post by:
Hi
Thanks for your response but the query which you gave in my previous post doesnt work. It displays the same cnt for movie reviews and videoreviews
Let me explain you in detail
As I said I have got two tables like one is 'Food table' and the other one is the 'Videos' table
Columns which I have created in 'Food' table are 'foodid' and 'blogcid'
colcumns which I have created in Videos table are 'videoid, 'destid', 'videotitle'
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |