Random Records

[Manton]

Access 2000.
I have a field in a table (tbl_data entry) which has a field called Free and
another field called SD (containing 2 options: 5 and 6)
The Free field is a yes/no format.
I'd like to run a query which upon entering a date range ("Enquiry Date")
value -allows the query to choose 170 records (by ticking off the yes/no
field "Free") randomly from tbl_data entry.
Alternatively: It could choose (ticking off the yes/no field "Free) the
first 170 records within the date range entered (choosing the earliest date
and working down the list).
thanks.

[Allen Browne]

Assuming your table has a numeric primary key named "ID", you can get 170
random records in the date range with this kind of thing:
SELECT TOP 170 * FROM [tbl_data entry]
WHERE [Enquiry Date] Between #1/31/2004# And #2/29/2004#
ORDER BY Rnd([ID]), ID;

You can then turn it into an Update query (Update on query menu) to update
the Free field to True.

Notes:
1. Issue a Randomize in the Immediate Window Ctrl+G) before running the
query.

2. The Rnd() function does not do anything with the ID field, but if you do
not pass in a changing number, the optimizer does not call the Rnd()
function on every row.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Manton" <sk**@optusnet.com.au> wrote in message
news:40**********************@news.optusnet.com.au ...

Access 2000.
I have a field in a table (tbl_data entry) which has a field called Free and another field called SD (containing 2 options: 5 and 6)
The Free field is a yes/no format.
I'd like to run a query which upon entering a date range ("Enquiry Date")
value -allows the query to choose 170 records (by ticking off the yes/no
field "Free") randomly from tbl_data entry.
Alternatively: It could choose (ticking off the yes/no field "Free) the
first 170 records within the date range entered (choosing the earliest date and working down the list).
thanks.