jazjef
asked on
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....
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....
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
ASKER
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.
[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.
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
Still, you should use Rnd(-Timer()*[ndx]) or you will get the same selection each time you open Access and launch your application.
/gustav
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.
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.
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