View a random set of records

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ben - 28 Apr 2008 11:00 GMT

Hi..

I'm trying to get a query to show me all of the records with a specific
reference number, however I want to be able to choose them at random.

If I have 2000 records, each with a unique 'sub record' (with a unique
identifier), how can I choose a random selection of records and sub record,
so that the results show like this???

Reference    Unique ID    Record    Text field
1234        1        Text 1
1234        2        Text 2
1234        3        Text 3
5678        1        Text 1
5678        2        Text 2
5678        3        Text 3
3456        1        Text 1
3456        2        Text 2
3456        3        Text 3

thanks

Ben

Reply to this Message

Michel Walsh - 28 Apr 2008 14:57 GMT

You can try something like:

SELECT TOP 10 <List Of Fields Or Expressions>
FROM tableName.
WHERE someConditionIfApplicable
ORDER BY RND(SomeNumericalField);

all cap words are to be typed as they are. RND( ) is a VBA function, see
more about it in the help.

I assume you want 10 random records (at most 10) satisfying any conditions
you eventually imposed.

Vanderghast, Access MVP

> Hi..
>
[quoted text clipped - 20 lines]
>
> Ben

Reply to this Message