extracting random records

[maxmangion]

If i have a table with several records let's say 1000 records, and each record has a field called cboCategory. Now from these 1000 records, lets say i have 200 records which has a value of CategoryA in the cboCategory field. Now is it possible that i extract "randomly" a number of records from that particular category chosen.

e.g.

Table -> 1000 records

records which belong to categoryA -> 200

extract 20 random records from the table which have a value of categoryA inthe cboCategory field.

Thanks for any suggestion.

 

[Rickster57]

SELECT TOP 3 table1.avalue, Rnd([avalue]) AS Expr1, table1.sometext INTO Newtable
FROM table1
WHERE (((table1.sometext)="H"))
ORDER BY Rnd([avalue]);


This takes 3 records from the number field [avalue] and the textfield [sometext] where [sometext]="H" in table1 and dumps it into a table (basically a temporary table, maketable query) called NewTable.

Hope this gets you started

 

[AlphaZeroX]

You could try creating a query in design view that includes that particular table. Then from that table only, add the cboCategory field. (If I understand you correctly), you can then use the criteria row to filter out the "categoryA" from whatever field "categoryA" is in. Also on the queries bar (includes File, Edit, that top bar) there should be a drop-down list containing the word "All". From there highlight "All" then type in the number "20". Then with the criteria searching for "cboCategory" click the "!" (Exclamation Mark) button to run the query and view the dynaset (table including the results from the query). If the results are ones you do not wish to use, going back into the Design View of the query, then try using the "Sort" row to change the order from normal (unsorted) to ascending to descending.
Hope this helps.

 

[maxmangion]

Hi Rickster,

Thanks for your reply. I have tried the following code:

SELECT TOP 3 Questions.QuestionID, Rnd([QuestionID]) AS Expr1, Questions.Question INTO NewTable
FROM Questions
WHERE (((Questions.QuestionCategory)="fpp"));

The following is a sample of the data which i have:

1, This is question 1, fpp
2, This is question 2, fpp
3, This is question 3, fpp
4, This is question 4, fpp
5, This is question 5, fpp
etc

As i said in my first post, i would like that every time i run my query i would return three random record from the above table, however when i run the above code i always get the following result:

1, 0.9495566487312, This is question 1
2, 0.3640186786652, This is question 2
etc.

Any ideas please ?

Thanks

 

[Rickster57]

Random

If you didn't include the following line of code like I stated in my example you will get the same records every time...

ORDER BY Rnd([avalue]);


I have tested this several times and does indeed produce random results

 

[maxmangion]

Hi Rickster,

Thanks very much, including the "Order By", worked perfectly.

Thanks.