I am not aware of the 'stratified' meaning, in this context
To pick random records, in a query, you can use the VBA function Rnd( ), but
supply, as argument, a numerical field.
In Northwind:
SELECT Categories.CategoryName, Rnd() AS Expr1
FROM Categories
ORDER BY Rnd( )
returns:
Category Name Expr1
Seafood 0.579518616199493
Produce 0.579518616199493
Meat/Poultry 0.579518616199493
Grains/Cereals 0.579518616199493
Dairy Products 0.579518616199493
Confections 0.579518616199493
Condiments 0.579518616199493
Beverages 0.579518616199493
where the random number you get is probably different than mine, but note
that it is the same for all records, Indeed, since Rnd( ) does not depend
of ANY field in the table, the query optimizer assumes this is a CONSTANT,
and computes it only ONCE.
With:
SELECT Categories.CategoryName, Rnd([CategoryID]) AS Expr1
FROM Categories
ORDER BY Rnd([CategoryID]);
I got:
Category Name Expr1
Dairy Products 0.364018678665161
Seafood 0.524868428707123
Beverages 0.767111659049988
Condiments 5.35045266151428E-02
Produce 0.592458248138428
Grains/Cereals 0.468700110912323
Confections 0.298165440559387
Meat/Poultry 0.622696697711945
which shows different random numbers, ... but the ordering seems wrong. In
fact, Rnd([CategoryID]) is called twice, once for the SELECT clause, and
once for the ORDER BY clause. But since you want randomness, is that
important to see it in the SELECT clause?
You can add a TOP 3, to get only 3 random records:
SELECT TOP 3 Categories.CategoryName
FROM Categories
ORDER BY Rnd([CategoryID]);
Vanderghast, Access MVP
