All Possible Permutation of a data set order

Posted on 2014-07-12
Last Modified: 2014-07-27
I have three arrays represented as Body Part, Left and Right, Position standing or sitting.  The array looks like this:
Knee      Standing      Left
Ankle      Sitting      Right
Body Parts Arm, Hand and Elbow can only have sitting position. Here are all 22 possible combination pairs.  We get this because Arm, Hand and Elbow can only have sitting position:
Knee      Left      Standing
Knee      Left      Sitting
Knee      Right       Standing
Knee      Right       Sitting
Ankle      Left      Standing
Ankle      Left      Sitting
Ankle      Right       Standing
Ankle      Right       Sitting
Foot      Left      Standing
Foot      Left      Sitting
Foot      Right       Standing
Foot      Right       Sitting
Hand      Left      Sitting
Hand      Right       Sitting
Elbow      Left      Sitting
Elbow      Right       Sitting
Arm      Left      Sitting
Arm      Right       Sitting
Leg      Left      Standing
Leg      Left      Sitting
Leg      Right       Standing
Leg      Right       Sitting

I need a permutation with all possible orders.  Here is an output example:

Patient      BodyPart      Laterality      Position
A      Knee      Left      Standing
A      Knee      Right       Standing
B      Foot      Left      Standing
B      Hand      Left      Sitting
C      Elbow      Right       Sitting
D      Elbow      Right       Sitting
E      Arm      Left      Sitting
Question by:charlesbaldo
    LVL 22

    Expert Comment

    You can make 2 tables; one with body part and possible position and a second for laterality. Place those two tables into a query. Because they're not joined, you will get one record each body part, position and laterality. If you make another table for patients and then place that in a query with the first query, you'll have all possible combinations for each patient. See attached.


    Author Comment

    Hello Flyster,

    Thank you very much for helping!

    Is it possible to make all possible orders of the Permutation results below because the patient are not coming in having the same body parts examed in a row and then next, such as all Knees, all foot and all Arms.  The patients could come in any order.  Please see the example at the bottom.  I think I need all possible orders of the perpermutation results and randomize them.

    BodyPart      Position      Laterality
    Knee      Standing      Left
    Knee      Standing      Right
    Knee      Sitting      Left
    Knee      Sitting      Right
    Ankle      Standing      Left
    Ankle      Standing      Right
    Ankle      Sitting      Left
    Ankle      Sitting      Right
    Foot      Standing      Left
    Foot      Standing      Right
    Foot      Sitting      Left
    Foot      Sitting      Right
    Leg      Standing      Left
    Leg      Standing      Right
    Leg      Sitting      Left
    Leg      Sitting      Right
    Hand      Sitting      Left
    Hand      Sitting      Right
    Elbow      Sitting      Left
    Elbow      Sitting      Right
    Arm      Sitting      Left
    Arm      Sitting      Right

    Patient      BodyPart      Position      Laterality
    1      Knee      Standing      Left
    2      Knee      Standing      Right
    3      Ankle      Standing      Left
    4      Ankle      Standing      Right
    5      Knee      Standing      Left
    5      Knee      Standing      Right
    6      Foot      Standing      Left
    7      Leg      Standing      Left
    8      Knee      Standing      Left
    10      Arm      Sitting      Left
    LVL 22

    Accepted Solution

    You can do that in two tables. The first table lists all the permutations with an ID assigned to each one. The second table lists the patients with the associated permutation ID. Put both tables in a query and link the ID's.  See attached.

    Author Closing Comment

    Thank You was helpful

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now