title: Access query or VBA code to move data from one table to another depending on first row unique identifier
body: <p>Is it possible to write a query to loop through the rows of a two column table, checking the first column for a certain identifier and copy the data in the second column into a new table?</p>
  
  <p>Example:</p>
  
  <pre><code>tblSurveyData
  FirstColumn     Second Column
  A0              John
  A2              Smith
  A3              05-01-1973
  
  tblSurveyReport
  FirstName   MiddleName  LastName    DateOfBirth 
  John                    Smith       05-01-1973
  </code></pre>
  
  <p>Where A0 data would go to FirstName, A1 MiddleName, A2 LastName and A3 DateOfBirth. There are many more identifiers and fields but just as an example how would you do this with a query in Access or is VBA a better solution?</p>
  
  <p>The only solution I came up with is the following VBA but this bypasses the two column table and tries to insert into the tblSurveyReport table. Unfortunately, it puts each piece of data into its own row which doesn't help. </p>
  
  <pre><code>If Identifier = "A0" Then
  DoCmd.RunSQL "INSERT INTO tblSurveyReport " _
              &amp; "(FirstName) " _
              &amp; "VALUES ('" &amp; Info &amp; "')"
  ElseIf Identifier = "A1" Then
  DoCmd.RunSQL "INSERT INTO tblSurveyReport " _
              &amp; "(MiddleName) " _
              &amp; "VALUES ('" &amp; Info &amp; "')"
  ElseIf Identifier = "A2" Then
  DoCmd.RunSQL "INSERT INTO tblSurveyReport " _
              &amp; "(LastName) " _
              &amp; "VALUES ('" &amp; Info &amp; "')"
  ElseIf Identifier = "A3" Then
  DoCmd.RunSQL "INSERT INTO tblSurveyReport " _
              &amp; "(DateOfBirth) " _
              &amp; "VALUES ('" &amp; Info &amp; "')"
  End If
  </code></pre>
  
  <p>However each piece of data is going into its own row and I need it all in the same row. </p>
  
  <p>Any help is greatly appreciated.</p>
  
  <p>Thanks in advance.</p>
  
  <p>TC</p>