title: I need to append data to an existing table in Access from 2 tables
body: <p>I have data in two tables:</p>
  
  <p><code>tbl_games</code> has the following columns:</p>
  
  <pre><code>game_id, season, date, home_team, visiting_team, 
  home_score, visiting score, home_score_half_time, visiting score_half_time
  </code></pre>
  
  <p><code>tbl_formation</code> has the following columns:</p>
  
  <pre><code>game_id, home_formation, home_team, visiting_team, visiting_formation
  </code></pre>
  
  <p>I would like to append data from these tables into a <code>game_team</code> table that has the following columns:</p>
  
  <pre><code>game_id, team_id, status, end_score, half_score, Formation
  </code></pre>
  
  <p>Instead of separating out <code>home_team</code> <code>visiting_team</code> I would like to have them all under <code>team_id</code> and status indicating whether it was a home team or a visiting team for a certain game. </p>
  
  <p>I was experimenting with the query below but it did not work</p>
  
  <pre><code>INSERT INTO Game_Team ( game_id, Team_ID, End_Score, half_score, Formation )
  SELECT G.game_id, G.home_team, G.home_score_half_time, G.home_score, GL.home_formation
  FROM tbl_games AS G 
  INNER JOIN tbl_formation AS GL ON G.game_id = GL.game_id;
  </code></pre>