generating sets of combinations of massage pairs

[edwin1971]

Hi,
I am organizing a day where men massage women (round 1) and vice versa (round 2).
In this example I will only focus on the first round, using 3 men and 3 women (in real, there are 6 of both sexes).

The men (m1, m2, m3) give each of the women (f1, f2, f3) a score, indicating how much they would like to give a massage to this woman.
The women give each of the man a score indicating how much they would like to receive a massage from this man.
Both scores are combined into a single pair-score. This results in the following query 'pairscore'.
Both giver and receiver are in fact columns containing id's pointing to a table person (id, name, sex).

giver receiver score
m1 f1 2,4
m1 f2 2
m1 f3 1,6
m2 f1 2,4
m2 f2 2,1
m2 f3 2,0
m3 f1 3
m3 f2 2,5
m3 f3 1,8

Now, I would like to generate all possible setups, each containing (in this example) 3 valid massage-pairs.
setup giver receiver score
1 m1 f1 2,4
1 m2 f2 2,1
1 m3 f3 1,6
2 m1 f1 ...
2 m2 f3
2 m3 f2
3 m1 f2
3 m2 f1
3 m3 f3
4 m1 f2
4 m2 f3
4 m3 f1
5 m1 f3
5 m2 f1
5 m3 f2
6 m1 f3
6 m2 f2
6 m3 f1

This query would permit me to give each setup a setup-score, by calculating the mean score fo all pairs in the setup for example.

Now my questions are:
1. How do I create a query that generates all setups as shown above, for N men/women.
2. If such a query is not possible, what VBA code will do the job.

I am using MS access 2007.

Thank you in advance for any attempt to help me.

Kind regards,

Edwin
The Netherlands

 

[pbaldy]

Presuming men and women are in the same table, you can create two queries, returning men and women respectively. Then create a third query based on those two. With no join, it will create a Cartesian product, all possible combinations. That query will be read only, but could be used to populate a table.

 

[edwin1971]

Thank you pbaldy, for your suggestion.

However, I tried this before. It does provide all possible combinations of men and women. However there is no grouping the pairs into 'setups' or sets. These are unique sets of pairs that are valid in such a way that these sets contain only pairs of men and women that are not part of another pair within the same set.

I hope this will clarifies my problem / question.

 

[jdraw]

Paul has suggested the most common approach to get all combinations of "things" -Cartesian product.
If you have some conditions that prevent that output from working in your situation, then please show us with example of the condition(s).
Pairs and sets and setups is confusing (to me at least).

 

[edwin1971]

Hi Jdraw,

Thank you for your reply.

A set (or setup, just another word for set) is a group of pairs.

In my example of 3 men and 3 women, a set contains 3 pairs (each pair is 1 man and 1 woman).

The number of possible sets is 6. I am looking for the query that generates these 6 sets, like I wrote in my first post. I also gave the concrete result set that I want this query to produce, including the first column, containing the set-id.

Paul suggested a cartesian product. This would create 3 x 3 = 9 pairs. Actually, I am not looking for this.

I hope this is clear now, if not please let me know.
Edwin

 

[jdraw]

I'm still missing something basic (group/set?)

using the combos I have identified(below) please show the group/set that restricts the combos to 6.

combos
M1F1
M1F2
M1F3
M2F1
M2F2
M2F3
M3F1
M3F2
M3F3

 

[arnelgp]

This will be your query, see attached db.

Select T1.Name As Male, T2.Name As Female, ((SELECT TOP 1 Score From Message WHERE Giver=T1.Name AND Receiver=T2.Name) & ", " & (SELECT TOP 1 Score From Message WHERE Giver=T2.Name AND Receiver=T1.Name)) AS SCORES, ((SELECT TOP 1 Score From Message WHERE Giver=T1.Name AND Receiver=T2.Name) + (SELECT TOP 1 Score From Message WHERE Giver=T2.Name AND Receiver=T1.Name)) AS Total FROM (SELECT TablePerson.Name
FROM TablePerson
WHERE (((TablePerson.Sex)="Male"))) As T1 ,(SELECT TablePerson.Name
FROM TablePerson
WHERE (((TablePerson.Sex)="Female"))) AS T2

 

[edwin1971]

Hi Jdraw,
The possible combinations need to be bundled into sets.
If a man M1 gives a massage to a female F1, he cannot massage another woman at the same time. Also F1 would not available to receive a massage from another man.
So if a pair M1F1 is included in a set, M1F2 and M1F3 are not possible. Also M2F1 and M3F1 are not possible pairs in this set.
A valid set is:
M1F1
M2F3
M3F2
another valid set is:
M1F3
M2F1
M3F2

There are 6 different sets in my (small) example. What query would produce them and would be scalable to a larger population? I need the output in this form:

set, giver, receiver
1 m1 f1
1 m2 f2
1 m3 f3
2 m1 f1
2 m2 f3
2 m3 f2
3 m1 f2
3 m2 f1
3 m3 f3
4 m1 f2
4 m2 f3
4 m3 f1
5 m1 f3
5 m2 f1
5 m3 f2
6 m1 f3
6 m2 f2
6 m3 f1

Regards,
Edwin

you wrote:

I'm still missing something basic (group/set?)

using the combos I have identified(below) please show the group/set that restricts the combos to 6.

combos
M1F1
M1F2
M1F3
M2F1
M2F2
M2F3
M3F1
M3F2
M3F3

 

[arnelgp]

So what u r saying is the system will pick the pair and will continue to be paired to the second round.?

 

[edwin1971]

Hi Arnelgp,

Thank you very much for your effort...

You asked : So what u r saying is the system will pick the pair and will continue to be paired to the second round.?

We only have to consider one round here.

In this round, three man give massage to three woman, one on one.

There are 6 possible combinations of pairs to make here, as I wrote in my previous post.

I looked at your database. In the result of your query, a column 'set' is missing. This column contains a number to determine a set of (3) pairs. Of course when upscaling the number of people, there would be more pairs in a set.

For simplicity, we can also leave out the scores, I can add this aspect later myself.

Maybe, if in your example you would work with three men and three women only, the result of your query can be more easily compared to the obtained result. After that upscaling is easy.

Thanks a lot!
Edwin

 

[arnelgp]

Here try this sample i made if this is what you want

 

[edwin1971]

Thank you Arnelgp for your attempt.
But i'm afraid it does not solve my problem. (or: what query exactly would provide the result set below?)

What I am looking for is a query (or VBA procedure) that produces
(set, giver, receiver):
1, m1, f1
1, m2, f2
1, m3, f3
2, m1, f1
2, m2, f3
2, m3, f2
3, m1, f2
3, m2, f1
3, m3, f3
4, m1, f2
4, m2, f3
4, m3, f1
5, m1, f3
5, m2, f1
5, m3, f2
6, m1, f3
6, m2, f2
6, m3, f1

using the table person:
(id, sex)
m1, m
m2, m
m3, m
f1, f
f2, f
f3, f

The number of persons and sets is both coincidentally six, but would be different when scaling up the number of persons.

Logically, the query would give all possible groups (6 in this case) of massage-pairs (a pair = 1 man + 1 woman) that can be massaging at the same time.

An example of the second group is the following part taken from the query result above:
2, m1, f1
2, m2, f3
2, m3, f2

From all possible groups, I will then choose one, but this choice is out of the scope of my question here.

So any query or procedure would only work for me, if it provides the above result set based on the given input person table.

thanks!
Edwin

 

[arnelgp]

Yes i know so i modify it.

 

[arnelgp]

Another test data