randomize 2 columns separately.

[Happy YN]

I have read a lot about the rnd function to succesfully randomize the order of records in a db.
I want to randomize 2 columns separately . These are Q's and A's so if the db is 2 fields (the second -small- letter is supposed to be under Field 2!)
Field1 Field2
A a
B b
C c
D d

etc...
I want it to look like
Field1 Field2
B c
A d
D b
C a

I have managed to construct two separate queries which obviously give 2 random lists for each field but how to put them both together to achieve the above?
I have tried applying a rank order to each list and then linking them on that but the rank number keeps changing hence no link there. All I want is to link them and keep each column in the same order as it has been randomized.
Thanks

 

[lightray]

I would think you need a third field Field0. perhaps an autonumber?
have your results fixed to that.

 

[Happy YN]

Thanks!
I have tried that too. It isn't that simple to autonumber in a query -it is usually based on code outside the query so two queries using that code would end up with different numbers as the code does not restart for each query

 

[lightray]

It is quite easy to add an autonumber field to a query, but you shouldn't attempt to change it in any shape or form. I was thinking this would be the anchor point for the end result of the randomizing. maybe I didn't think it out properly, I will bang a few keys in access.

 

[Happy YN]

Thanks. Looking forward to your solution. I still have not been successful!

 

[lightray]

Hi Happy YN, sorry couldn't get my theory to work, but there are a lot of posts for rnd() I've enclosed one of the links.
http://www.access-programmers.co.uk/forums/showthread.php?t=116038

Hope this helps

 

[Happy YN]

Thanks again but rnd is not really the problem as I can successfully produce a different order of records each time.
What I need to do is jumble the 2 columns so I need to be able to take record 1 from field 1 and record 1 from field 2 and put them next to each other. To do this I need to link the 2 random queries in some way. Autonumber or rank would have been the easiest but if I run a third query to link the first two I get no records because since I am querying both at the same time the autonumber creates different numbers for each!
Thanks again
Happy YN