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