Random query not updating when DB is first opened

[monkeymagic2222]

Hi,

I have created a query in access that pulls out a random selection of data using the SQL: ORDER BY Rnd([ID]);

This works fine, each time you click on the query the data is random as expected. However when you close the database and reopen it, then run the query again it still shows the last set of random data so you have to run the query a second time in order for the data to refresh.

I'm not too familiar with the way Access works but am guessing there must either be an option to refresh data when the db is loaded or do I have to write a macro to refresh the query?

 

[lameid]

Read the help topic for the RND Function. Unless the data changes you would get the same reuslts.

 

[monkeymagic2222]

Thanks for the post. All I am doing is selecting 50 random records from a database by ordering by a random number using the primary key of the table as a seed.

The query is working perfectly, every time I run it I get the 50 random records as expected, different every time. What fails is if I close the database, relaunch it and run that same query again. In the first instance the random records will be the same ones as before I closed the database. If I click on the query a second time the data will be random again.

 

[lameid]

I see now... I thought it was alwasys the same.

Try creating a function that calls the randomize statement and run that in the Autoexec macro.

 

[monkeymagic2222]

I think I've fixed it, the random thing was always using the same sequence of numbers which is why it reset back to the beginning each time I closed the database. I've changed the Order By bit of the query to create a Random seed from the current date and time:

ORDER BY RND(INT(NOW*ID)-NOW*ID);

Seems to work ok now.

Thanks.