Randomizing Query for Multiple fields (1 Viewer)

I have a need to randomize multiple records against eachother (see below).

ONE record should consist of:
Random day from 1-32 (each day can only occur 6 times, if possible).
Random organization from organization1, organization2...organization200.
Random action from action1, action2... action 10
Random detail from detail1, detail2... detail 200.

So basically, I have the need for A LOT of randomization in a single query.
Currenlty, i have an append query that has these 4 fields WITH 4 expressions (Rnd([OrganizationID], Rnd([TimeID])...)

So these 4 fields and 4 rnd expressions take 20 minutes to query.. a single rnd takes less than a second to query. 2 rnd's take about 3 seconds. 3 rnd's takes about 5 minutes... It's rediculous! =(

So other than this issue, I have even a bigger one. Sometimes, these "random" measures will task an organization twice in the same time... So if there were a way to limit an organization to a single task per day (like, IF duplicate day (14th) for organization (organization9), re-randomize)...


I know I'm looking at this the wrong way. Can somebody please help me? Preferrably, I would like 4 randomizations to occur on a single click and append to a table... Since these randomizations would have to NOT affect the source, it will all have to be done through append... I've been working on this for about a week and my boss is getting really upset that I can't figure this one out.. It's really kicking my A. Any help will be greatly appreciated (esp. since Google isn't yeilding any useful returns). PS: working in Access 2007.

It's entirely unclear to me what you want with "randomize multiple records against eachother"

Do you have four separate tables and from each you need to select one random value and the resulting four values must be appended to another table? You could order the tables per Rnd once, and then grab the first whatever number of rows you need. Take care of the seed, otherwise your random sequence might be exactly the same each time - look into the docs for Rnd.

Thank you for the reply.

The reason I need each to be "random" is because my operating instructions state they must be random to be in compliance.

To answer your question: yes. I have mutliple tables for each datatype. oragnization is paired w/ organization_ID, action w/ action_ID, detail w/ detail_ID, and day w/ day_ID. I call each table w/ a different rnd([]). This wasn't working for me because when I have a single query try to pass each table:

1) Organization_Table > Organization_Name
2) Action_Table > Action_Name
3) Detail_Table > Detail_Name
4) Day_Table > Day_Name

along with each random:

1) Expr1: Rnd([Organization_ID]), Sort By Ascending.
2) Expr2: Rnd([Action_ID]), Sort By Ascending.
3) Expr3: Rnd([Detail_ID]), Sort By Ascending.
4) Expr4: Rnd([Day_ID]), Sort By Ascending.

It takes 30+ minutes to query -_-.

So now i run each Rnd in its OWN query as an append ran w/ a vba open.query (instead of SELECT like I did before). Each of these 4 queries appends to a new set of tables:

1) Second_Organization_Table > Organization_Name
2) Second_Action_Table > Action_Name
3) Second_Detail_Table > Detail_Name
4) Second_Day_Table > Day_Name

And NOW, I finally put it all together in a single append query.. So it's pretty ugly... I'm still stuck on the issue of double taskings...

Basically, an orgnaization can be tasked every day if the randomization runs that particular course.. But the organization can only have 1 tasking/day... How do I write something like this to integrate into what I currently have??

Thanks again,
Glenn