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....
LVL 4
jazjefAsked:
Who is Participating?
 
Gustav BrockCIOCommented:
To avoid this, add a time dependant seed to Rnd which changes for every unique id:

 SELECT
   Table1.[ID],
   Table1.[field name],
   Table1.[Account Name],
   Rnd(-Timer()*[ID]) AS [Rnd Generator]
 INTO
   table2
 FROM
   Table1
 ORDER BY
   Rnd(-Timer()*[ID]);

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
0
 
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.

/gustav
0
 
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?
0
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.
0
 
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;
INSERT 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.
0
 
Gustav BrockCIOCommented:
OK.
Still, you should use Rnd(-Timer()*[ndx]) or you will get the same selection each time you open Access and launch your application.

/gustav
0
 
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.
0
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.