Three Stooges

[Harry Shmedlap]

Given a table with 3 fields for people, where each person can be either Curly, Lary or Moe, and 3 fields for chairs, where each chair can be either Blue, Red or Green. Here are two such records:

Per1 Per2 Per3 Per1_Chair Per2_Chair Per3_Chair
Curly Lary Moe Blue Green Red
Lary Curly Moe Red Blue Green

How do I get 3 new fields - Blue, Green and Red - each of which shows the person in that color for the respective record:

Blue Green Red
---- ------ -----
Curly Lary Moe
Curly Moe Lary

I did this in Excel by adding 3 columns and using a formula with functions match, offset and lookup, but I want to do this instead with a query.
Thanks in advance.

 

[Rabbie]

Your data structure is working against you here. Try reading up on Data Normalization. It will make your life easier.

 

[Harry Shmedlap]

The table is already given and cannot be changed.
The question is how I can solve the problem using a query and joins using multiple copies of the table.

 

[georgedwilkinson]

Ask the professor.