I want to randomly select 5 records from a table

[RobertGardiner]

I have a table of equipment item numbers I want to be able to randomly select five records to a report for a serviceablity test of the item number.

ie randomly selecting items for an audit.

At present I export all the records where the last service date is less than 12 months from todays date int Excel and generate a rand between 0 and greatest line number.

This is messy I would like to get a report from access listing the five items to be checked each day.

I can't work out how to do this from the help menu

 

[GHolden]

You could use some sql like this

SELECT TOP 5 tblTable.value,tblTable.string1
FROM tblTableORDER BY Rnd(tblTable.value)

This generates a random number for each record based using any number field as the seed (in this case the value field) it then orders by this field (therefore you will get a different order each time) and returns just the first 5 records.

There are probably other ways to do this.
Hopefully this may provide a solution.

There are two ways to write error-free programs; only the third one works.

 

[GHolden]

sorry missed out the space before ORDER

SELECT TOP 5 tblTable.value,tblTable.string1
FROM tblTable ORDER BY Rnd(tblTable.value)

There are two ways to write error-free programs; only the third one works.

 

[RobertGardiner]

Thanks,

It worked and with a bit of experimenting I was able to use it. (as I haven't used SQL for some 15 years)

This has had me puzzled for a year

Thanks for the solution