random rows from a query

[pb21]

I have a query that returns x number of rows. From that data I need to select for example 10% of those rows from the original sample but a random selection.

I dont have a clue where to start with that in the query grid.


advice would be much appreciated.

sql that forms original sample;
SELECT Amber.[First Name], Amber.[Last Name], Amber.[Person Id], Amber.[Course names]
FROM Amber
GROUP BY Amber.[First Name], Amber.[Last Name], Amber.[Person Id], Amber.[Course names], Amber.[Centre Code]
HAVING (((Amber.[Centre Code]) Like "SE02LC" & [enter centre code last 2 digits]))
ORDER BY Amber.[Last Name], Amber.[Centre Code];

 

[The_Doc_Man]

Well, there are various ways to do this, but they can be ugly.

One simple way that isn't too bad is if you have a unique ID on the table from which you want this selection.

If you do, then write a make-table query that writes two fields. One is the unique ID from your master table. The other uses the Rnd() function (Q.V. in the help files) to store a number. Normally, this is a REAL (floatingpoint) number between 0 and 1 but you could scale it however you like. If you are commonly looking at percentage, store it as the integer CINT( 100. * Rnd() ).

OK, now build a query that does an INNER JOIN of the two tables - the original and the one with the unique ID and random number. Then, in the column for the random number, put the target criteria as a percentage. If you want X% of all records AND the number of records is high enough that the random number probabilities level out, then select all records for which the random number is less than or equal to your target X.