I do not want the random number to change - in query

[basement131]

i have the following query.

Random number:Rnd([DE_fjrnldate])

the DE_fjrnldate - is a date that may or maynot be the same for different records.
now when i run the query each record has a unique number. which is exactly what i want.
however, when i rerun the query or click on the random number field the random number changes. i need to be able to run the random number and then that random number not change. right now i have to export the query into MS Excell and then import it back into MSAccess, to do further work on it. i have tried to put in a seed number ie. (55) but that results in all the records having the same random number.

any help appreciated.

 

[GingerR]

Sounds like you want to store the random number forever? How about adding a field to your table, and writing an update query to add the random number to the table?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[basement131]

Thanks but - No i do not want it to be a part of the table.
instead i want it to be in the query. such that when i go to run the query i am asked to enter a number (i think this is what they call the seed) which if i enter the same number it will always give me the same result (but still a different number for each record) and when i sort the query after it is ran, then my random numbers will not change.

 

[PHV]

You may try this:
Random number:Rnd(-[DE_fjrnldate])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[pbrodsky]

It sounds as though you want something that will give you the same sequence of numbers every time the saem seed is used.

If you look at the Help for Rnd(number),
"Less than zero: The same number every time, using number as the seed.
Greater than zero: The next random number in the sequence.
Equal to zero: The most recently generated number.
Not supplied: The next random number in the sequence."

I think you would need to construct a custom function for that, which would call rnd with a seed the first time and without a seed every other time.

 

[basement131]

PHV
Your reply does what i am looking for to an extent. however, there is one little problem. There are duplicate records in the field [DE_fjrnldate] and i di not have a field in which each record is unique. this results in duplicate random numbers.
The only way i could use this would be if i would assign an autonumber to a field for each record (i.e. 1, 2, 3, 4,)

thanks - any other ideas.

 

[basement131]

PBRODSKY
thanks for your reply.
i did review the help however, it does not do what i would like (in its simple form). With that being said, i am sure a simple code would work.

basically it would work like this.
1st Every time i run the query it would ask me for the seed number. (i enter 50).
then it would generate a random number for the first record. then the random number function would loop and assign a random number to the second record using the previous random number as the seed. this process would continue until all records would be assigned a number.

Since the code to do this would be simple I would appreciate if you could write it. However, if this is a complex code, then i do not expect you to do it & i will continue to utilize the old way of doing this by exporting to Excel & importing back into Access - if too complex please indicate too complex. Also where do i insert code. i think it goes in SQL view. To assist you i have included the what is under the SQL view. of the current query. I would like the random number field called "Random No"

SELECT q010.DE_fjrnldate, FROM q010;


Please excuse me, my skills in MS Access are limited primarily to utilizing the basic functions and pre developed functions - which are used mainly for data extraction and data analysis. i do not know how to write code, and understand only a little of it.

Thanks