467,182 Members | 1,038 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,182 developers. It's quick & easy.

Random Records

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.
Nov 12 '05 #1
  • viewed: 2508
Share:
1 Reply
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.

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Keith Griffiths | last post: by
3 posts views Thread by Wim Roffil | last post: by
22 posts views Thread by Nhmiller | last post: by
2 posts views Thread by IceCube | last post: by
4 posts views Thread by Greg Strong | last post: by
7 posts views Thread by FrankEBailey@gmail.com | last post: by
5 posts views Thread by muskie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.