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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.