How does one randomly select 25 to 30 rows of data from MS Access using VB6?

I have the following select statement in VB6 that selects a random record from a baseball database:

SqlString = "SELECT top 1 (Master.nameFirst + '_' + Master.nameLast) AS Player, (Batting.yearID & Batting.teamID) AS Team, Fielding.POS AS POS, Batting.G, AB, R, H, [2B], [3B], HR, RBI, ((H - (([2B]) + ([3B]) + (
))) + ((2*[2B]) + (3*[3B]) + (4*
))) AS TB, BB, SO, Batting.SB, Batting.CS, ((H + BB + HBP) / (AB + BB + HBP + SF)) AS OBP, (TB / AB) AS SLG, (H / AB) AS BAvg, ((Fielding.PO + Fielding.A) / (Fielding.PO + Fielding.A + Fielding.E)) AS FPCT FROM (Batting INNER JOIN Master ON Batting.playerID = Master.playerID) INNER JOIN Fielding ON Batting.playerID = Fielding.playerID WHERE Batting.yearID > 1945 ORDER BY rnd(Batting.yearID)"

I can select about 3 or 4 records to my MSHFlexGrid by changing 'top 1' to 'top 2', or 'top 3' etc. However, notice all the math that the statement calculates while selecting a record----batting average, fielding percentage, etc. I can't do more than about 3 or 4 records because the math causes the statement to lock up and crash the program. I think my statement just forces too much down the 'throat' of my program when I try to get all the records in one gulp.

Is it possible to loop through the recordset, choose a single random record, do the math, put the record in the flexgrid, then go back to the start of the recordset and do it all again until I have my 25 to 30 random records?

Many thanks for any help....
Who is Participating?
Gustav BrockCIOCommented:
To avoid this, add a time dependant seed to Rnd which changes for every unique id:

   Table1.[field name],
   Table1.[Account Name],
   Rnd(-Timer()*[ID]) AS [Rnd Generator]

If you don't have a numeric and unique ID, you will have to create one. If you can't combine some existing fields, you may have - as a first step - to append the records to yet a temp table having an AutoNumber. Then use the table for the next steps.

Gustav BrockCIOCommented:
You could do that, but a simpler and faster method would probably be to reduce your above query to a minimum just selecting the data and 25 records you need (no calculations).

Then change the query to a create-table query to create a temp table for your data, and then use this table as source in a new query where you perform your calculations.

jazjefAuthor Commented:
Makes sense Gustav... I just get the random recordset, put it into a flexgrid, and do the calculations there. Or... put them in a temp table once they are selected and do the calculations in the temp table.

One problem I'm having----I was wrong about my 'random' selection code. I'm getting the same records over and over again a lot of times.

Your suggestion will work for the math part of my question where the crash occurs----but what can I do about getting actual 'random' records from my MS Access table? Arrrrrghh. It's frustrating. I don't have an AutoNumber column in the table as a primary key, and some internet posts seem to say one is needed if you want to get random records. Any ideas?
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

jazjefAuthor Commented:
MS Access says that an AutoNumber ID column cannot be created once a table has data. How can I 'ALTER' the exiting table and create an ID AutoNumber column?...... the table has 99K + rows of data. In addition, it's not a table that data is written to as it is 'historical' baseball data.
jazjefAuthor Commented:
I got it done Gustav.... I used your suggestion about the Temp Table with a twist:
[1] I made a copy of my main table named Batting and called it Batting2;
[2] then I deleted all the records from Batting2;
[3] then I created an ID field with AutoNumber in Batting2 and called it ndx;
[4] then I used this statement to put the records from Batting into Batting2;
SELECT * FROM Batting;

[5] then I deleted my main table Batting, and then renamed Batting2 to Batting---now I have my main table with an ID field and AutoNumber
[6] I use this statement to retrieve random records:
SELECT TOP 10 ndx, Rnd([ndx]) AS Expr1, OtherSQLStuffGoesHere FROM Batting ORDER BY Rnd([ndx]) DESC;
*just change the 10 to however many records you want to return

Now I can get the random records and just do my calculations on the MSHFlexGrid data once its in there----something I'm far more familiar with. You inspired me to think harder on this..... thanks.
Gustav BrockCIOCommented:
Still, you should use Rnd(-Timer()*[ndx]) or you will get the same selection each time you open Access and launch your application.

Martin LissOlder than dirtCommented:
I've requested that this question be closed as follows:

Accepted answer: 500 points for Gustav Brock's comment #a40759599

for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.