esn wrote:

Thanks for the replies - I care how the record was chosen only in that
I need it to be random (or reasonably close to random). When I
checked to make sure that Access was functioning properly to select a
random record, there seemed to be a glitch, so I thought I would run
it by the experts. I figured this was just a recalculating issue and
that, at some point, the order of the records had been randomized, but
I wanted to be sure before I went too much further. And it's good to
know it's possible to build a function to stop Access from
recalculating the random field - given the crummy performance of
queries based on this one I might end up using that to speed things
up.

Now I have a question about the next step - here's the SQL I'm using
right now:

SELECT [GLSA Caps with Unit].Unit, [GLSA Caps with Unit].Point
FROM [GLSA Caps with Unit]
WHERE ((([GLSA Caps with Unit].Point) In
(SELECT TOP 3 [GLSA Caps with Unit_1].Point
FROM [GLSA Caps with Unit] AS [GLSA Caps with Unit_1]
WHERE ((([GLSA Caps with Unit_1].Unit)=[GLSA Caps with Unit].Unit))
ORDER BY Rnd([RndSeed]))))
ORDER BY [GLSA Caps with Unit].Unit;

And here's the output:

Unit Point
1 OO007
1 RR007
2 II006
2 LL001
2 LL005
2 MM001
2 MM002
3 II009
3 LL011
3 OO008
4 BB002
4 BB005
4 CC003
5 BB013
5 CC008
5 FF011
5 GG010
5 HH009
5 HH011
6 FF013
7 S002
7 U002
7 V003

Note the variable number of records per unit. FYI - Point is a text
field that identifies a geographic location (as I stated above) within
a grid based on a row identifier (a single or double letter) and a
column identifier (3 digits from 000 to 110). The source query (GLSA
Caps with Unit):

SELECT [Grid Point Info].Unit, [Trapping Data Records Table].Point,
Min([Trapping Data Records Table].[Capture/Event ID]) AS RndSeed
FROM [Grid Point Info] INNER JOIN [Trapping Data Records Table] ON
[Grid Point Info].LetterNumb = [Trapping Data Records Table].Point
WHERE ((([Trapping Data Records Table].[Species/Event])="GLSA"))
GROUP BY [Grid Point Info].Unit, [Trapping Data Records Table].Point;

To anticipate the first question - I already checked to make sure that
"GLSA Caps with Unit" returns at least three points per unit, and it
does. I've also tried using the "randomizer" custom function from the
link above, but I still get similar results. If I run the subquery on
it's own using "Unit=1" as criteria I get the right results (3 random
points in unit 1). So why does the query return less than three
points for units 1 and 6, and how can a subquery with a TOP 3 clause
be returning more than 3 points for some of the units?


Sorry, but I am having a seriously tough time unraveling
where the randon numbers are being recalculated. This is
especially compounded by the query optimizer doing whatever
it wants to combine your three queries into one with who
knows what effect on the random numbers.

I have not been able to explain the various number of
records, even when including the fact that TOP 3 will return
more than 3 records when there is a tie for the third value
in the sorted list.

--
Marsh
MVP [MS Access]