shuffle two fields of a Db differently! but retain ability to relink them

[Happy YN]

in a Db there are 2 fields which contain 'matching' data. (there are other columns too)
A Genesis
B Exodus
C leviticus
D Numbers
E Deutronomy

I want to shuffle both columns so they are both randomly mixed up
B Numbers
A Exodus
C Detronomy
E Leviticus
D Genesis

How do I do it?
I have explored the random function but that randomises the records but keeps each record intact.

I then want to print these two columns to a report which numbers each line - easy enough once I have the query BUT I want to print an 'answer page ' at the end of the report which will look like this:-

1 B Numbers (5)
2 A Exodus (1)
3 C Detronomy (4)
4 E Leviticus (3)
5 D Genesis (2)

I thought of appending a unique ID string to each pair and only displaying the first part of the string e.g
A@1 Genesis@1
etc and I can do this but maybe there is a simpler way.
Lastly supposing I would want to keep the order of column one and only shuffle column two?
Thanks for any advice!

[Rabbit]

in a Db there are 2 fields which contain 'matching' data. (there are other columns too)
A Genesis
B Exodus
C leviticus
D Numbers
E Deutronomy

I want to shuffle both columns so they are both randomly mixed up
B Numbers
A Exodus
C Detronomy
E Leviticus
D Genesis

How do I do it?
I have explored the random function but that randomises the records but keeps each record intact.

I then want to print these two columns to a report which numbers each line - easy enough once I have the query BUT I want to print an 'answer page ' at the end of the report which will look like this:-

1 B Numbers (5)
2 A Exodus (1)
3 C Detronomy (4)
4 E Leviticus (3)
5 D Genesis (2)

I thought of appending a unique ID string to each pair and only displaying the first part of the string e.g
A@1 Genesis@1
etc and I can do this but maybe there is a simpler way.
Lastly supposing I would want to keep the order of column one and only shuffle column two?
Thanks for any advice!

To retain the ability to relink them just have another table.

As for random arrangement...
Store A, B, C, etc. in one table and Store Numbers, Genesis, Leviticus, etc. in another table. Randomly get one row from each table and insert the combination into a third table. Then, delete the rows you used from the first 2 tables. Loop till the first 2 tables are empty.

Of course, each time you do this you'll need to repopulate the first 2 tables. So perhaps keep a backup of each to restore them.

Maybe someone else can provide a better solution but this is the best I can think of right now.

[nico5038]

1) update your *table* with a RND([some unique numeric field])
2) then create a query for the sort of the *table* on the filled/updated field.
Here you can even use a TOP 3 to get just the first three when needed.

Nic;o)

[Rabbit]

1) update your *table* with a RND([some unique numeric field])
2) then create a query for the sort of the *table* on the filled/updated field.
Here you can even use a TOP 3 to get just the first three when needed.

Nic;o)

Doesn't this run the risk of repeating numbers? And he'd still have to seperate his two fields into different tables.

[nico5038]

Hardly chance for a dupe when you use an autonumber as Seed for the RND() function.
Best to have two append queries filling the records into a newly created table1 and table2. Now the autonumber can be used to JOIN the two into a query showing the result.

Nic;o)

[Happy YN]

A bit risky joining on autonumber. If ever the tables get out of synch you run the risk of losing the join. Also appending and deleting records to/from a table bloats a database unnecessarily. I'd rather have two queries sorting both lists at random and then combining them. However if you have that for 5 records you end up with a query of 25 unless you can force a join on something so I am a bit stuck. (unless I then ask for the max 5 records of the expression which strips the appended number string off the end of the field - see my earlier post.
Thanks for all the support!

[nico5038]

Using a DROP TABLE and CREATE TABLE will limit the bloat and when you apply a weekly compact, you'll need a lot of rows and runs to exhaust Access :-)
No problem to join on autonumbers, just check afterwards or the number of rows is the same as from the originaltable when you have doubt's.

Nic;o)