title: MS Access SQL: Mapping different IDs in a query?
body: <p>A benchmark database stores questionnaire questions in one table and the participants responses to those questions in another table. For each year, new tables were created, and over the years, question IDs changed. Thus, it became difficult to generate one joined table of all responses over all years because the question IDs do not match anymore (for example, in 2010, the question "material cost" had question ID "bp2.12" but in the 2011 benchmarking study it became question ID "bp2.16" because new questions were added). However, the question table also stores a mapping to the old question ID.</p>
  
  <p>What SQL query (MS Access SQL) generates a table with all responses of all participants for two years with the correct mapping?</p>
  
  <p>Base tables (with sample data):</p>
  
  <ul>
  <li>tbl_questions_2010: ID | questiontext | unit
  <ul>
  <li>bp2.12 | material cost | USD</li>
  <li>bp2.13 | personnel cost | USD</li>
  <li>bp2.14 | FTE | FTE</li>
  <li>...</li>
  </ul></li>
  <li>tbl_questions_2011: ID | questiontext | unit | 2010_ID
  <ul>
  <li>bp2.16 | material cost | USD | bp2.12</li>
  <li>bp2.20 | personnel cost | USD | bp2.13</li>
  <li>bp2.21 | FTE | FTE | bp2.14</li>
  </ul></li>
  <li>tbl_responses_2010: ID | question_ID | participant_ID | response 
  <ul>
  <li>1 | bp2.12 | 1 | 2,289,990</li>
  <li>...</li>
  </ul></li>
  <li>tbl_responses_2011: ID | question_ID | participant_ID | response
  <ul>
  <li>78 | bp2.16 | 3 | 723,091</li>
  <li>...</li>
  </ul></li>
  <li>tbl_participants: ID | participant_name
  <ul>
  <li>1 | ACME LTD.</li>
  <li>3 | John Doe Group</li>
  </ul></li>
  </ul>
  
  <p>Desired result (with sample data):</p>
  
  <ul>
  <li>tbl_result: question_ID | year | questiontext | participant_name | response
  <ul>
  <li>bp2.16 | 2012 | material cost | ACME Ltd. | 2,289,990</li>
  <li>bp2.16 | 2010 | material cost | John Doe Group | 723,091</li>
  <li>...</li>
  </ul></li>
  </ul>
  
  <p>This is what I have so far:</p>
  
  <pre><code>SELECT tbl_responses_2010.question_ID_2010 AS Question_ID, "2010" as Year, tbl_questions_2010.questiontext, tbl_participants.participant_name,  tbl_responses_2010.response
  FROM tbl_responses_2010, tbl_questions_2010, tbl_participants
  WHERE tbl_responses_2010.participant_ID=tbl_participants.participant_ID AND tbl_questions_2010.question_ID_2010=tbl_responses_2010.question_ID_2010
  
  UNION 
  
  SELECT tbl_responses_2011.question_ID_2011 AS Question_ID, "2011" as Year, tbl_questions_2011.questiontext, tbl_participants.participant_name,  tbl_responses_2011.response
  FROM tbl_responses_2011, tbl_questions_2011, tbl_participants
  WHERE tbl_responses_2011.participant_ID=tbl_participants.participants_ID AND tbl_questions_2011.question_ID_2011=tbl_responses_2011.question_ID_2011
  
  ORDER BY Question_ID, response, Year;
  </code></pre>
  
  <p>This gives me a joined table of all responses for 2011 and 2011 but having wrong question IDs for the 2010 questions. I don't know how I can map them using the field tbl_questions_2011.2010_ID</p>