I have a table from which I need to randomly select rows. In the table, I have a category called "Ind" which has about 100 individual values in it and I need to select 3 rows from each of these individual values- so, in the end, about 300 rows should be randomly selected from the entire table. Can anyone help with how to code for this (I am somewhat new to SQL).

Welcome to UA.

Check this thread and/or do a search here at UA on the word random.

Random

Thanks for the reply DeDe, but I was unable to apply what I found to my particular problem. Any other places that may have an answer? Thanks!

Can you post sample data? What are going to do with your randomly selected rows once you have them?

Sure- here is some sample data. I need to choose 3 randomly selected rows from each value of "Ind_Seas" (2500 total rows and about 100 individual values for "Ind_Seas"). Thanks!

Date X_COORD Y_COORD Ind_Seas
9/25/2003 426762 3552245 1F1
11/29/2003 426935 3552632 1F1
12/3/2003 426931 3552674 1F1
9/20/2004 427552 3552234 1F2
9/21/2004 427179 3551868 1F2
9/23/2004 426485 3552036 1F2
9/28/2004 426746 3552231 1F2
10/5/2004 426855 3551943 1F2
10/12/2004 427571 3551778 1F2
10/13/2004 427542 3552221 1F2
3/26/2003 427601 3551993 1Sp1
4/15/2003 428011 3552418 1Sp1
4/20/2003 428021 3552459 1Sp1
5/5/2003 427699 3552893 1Sp1
5/13/2003 427533 3552349 1Sp1

I think I need to pass this on to someone a little more experienced. Sorry.

Close but not quite:

Using your test records in myTable called "Randrecs", trying to get
3 Random records by Ind_seas; but not getting just 3 records by group??

It may be a start. I'll continue to try to get only 3 records.



ref: MyQuery is Random3RecsByGroup_UA_Oct2009

The only way I could come up with that will randomly select the top 3 from each category, where you do NOT have a primary key value in the table, is to do this with a temp table that does contain a randomly generated seed value to act as a key field on which to select 3 from each category at random.

See the attached accdb with sample tables, queries and a module to support the code.

With only a small sample to test against, I can't be 100% connfident in the randomness, but the limited testing I could do returned correct results.

Generating and deleting values in a temp table may contribute to database bloat, if you have a lot of records from which this selection will be done. If that is the case, and we can't find a pure SQL solution for you, you might want to push the temp table out to a working accdb in the same folder.

I added a module to do that. It may be overkill, but the technique is portable.

HTH

George

Edited by: GroverParkGeorge on Sat Oct 31 15:41:28 EDT 2009.

Edited by: GroverParkGeorge on Sat Oct 31 19:39:34 EDT 2009.

How about something like this.

I see a date, X and Y coordinates in your sample. Dates are stored in Acces as an numeric value off of a specific date, 01/01/1901??????? So 10/31/2009 is actually stored as a number like(not at all accurate) 87654321.

Take the max and min dates of the category and record their numeric values. What follows precludes the max date from being used in the randomizer unless the time randomizer numeric works out to Zero!!!!

All calculations that produce numbers outside the range of min date and max date need to be recalculated

What if you took the execution(current) time of when you run and converted it to it's numeric value(the randomizer), and appliedy by subtarction or division to the date numeric and returned a set of records. The first one is record one. If no records returned then alter the randomizer by a value you decide(+/- 100,+/- 1000, etc, or ?????) upon and repeat until successfull.

Got a hit, take the left/right/middle two/three/four digits of the X coordinate returned and use that to alter the randomizer and add/subtract/multilpy/divide the original number by that. Loop until you get a hit. that's your second record.

Got a hit, take the left/right/middle two/three/four numbers of the Y coordinate and repeat as above.

It requires coding but is not at all difficult. Once it works for the first pass all you need do is figure out how you want to alter the randomizer and loop until records returned is > 0.

Of course the constant in all of the queries wouldl be "ind_seas" , its code, or any other code you want to use.

Sounds busy but it'd probably run i less than a second and you'd have three random records within any category.

Sounds interesting.
My method (shown in the attached accb), randomizes on the Y_Coord, which is, of course, a number in the sample data. Assuming that any values that appear in this column will always be numeric, this SQL works fine.

I could have started with the X_Coord,just as easily.

Nonetheless, it would work just as well by randomizing on the date, which is as you note, a number field underneath the covers. That might be a safer choice, IF the other columns could ever contain non-numeric values.


That's all you need really, a numberical Seed to start the randomization on.

Once you have that , rest is done in the subquery that collects the TOP 3 by group, using that randomized unique value.

I'm not sure about the reason for walking through all the values, though. It seems like a straightforward query on the recordset should be more direct .

A few housekeeping notes:

I tossed in the external scratch mdb as an exercise; the true work is done in the queries.

The table name "tblRevOne" is just the name of the test table I built to try this out.

I multiplied the randomseed by 100000 for no other reason than I like whole number, and don't see any problem here with rounding the results to whole numbers. That assumption might prove to be wrong, though, so it bears further testing.

George

==
Edited to remove blowout caused by my long line
Edited by: GroverParkGeorge on Sun Nov 1 2:11:33 EST 2009.

Edited by: GroverParkGeorge on Sun Nov 1 16:06:42 EST 2009.

I have tried various ways to do this. Unlike George, I don't have Acc2007 so can't look at his attachment.

I do have a process that seems to work, but as others have said - the sample data is quite small.
What I have found to work is the following:

1: Create a new temp table from your table. The new table contains a new field that is a Random number
based on some data in your table. I used the 1000000 multiplier based on George's comment but don't think it is necessary. My new table is RandRecs_Ran.

I used this, to create the new table with a Randomized field MyId.


2: Query the new table to get 3 random records per group



NOTE: If you run this query multiple times, you will get the same result each time.
Because the randomization was done Before this query was created/executed.
You could get a different 3 per group if you change the



3: In order to get different results when the Select query is executed,
you have to change the Randomization.
One way to do this is to Update the RandRecs_Ran table.
I used the following update query



It ain't pretty, but I think it works. I added some more test records to see
what the results would be.

Very similar to my approach. The only difference is that my temp table is created on the fly with a new random ID (called RandomSeed) each time it runs, so that each query returns a new set of random numbers.

My bad for not including a version compatible with A2000. Here it is.

George

Edited by: GroverParkGeorge on Sun Nov 1 16:13:55 EST 2009.

To all that offered so much help: THANK YOU!!

I am very appreciative of all of your time and thought into this!