Forum Discussion

DJPIN's avatar
DJPIN
Copper Contributor
Apr 11, 2019

Move Data from One Row to Another in Separate Columns

Hi,

 

I have file where the original format has two questions per person and puts the questions & answers in two separate rows. (See rows 2 & 3 below)

SAMPLE DATA.jpg

I need to have the questions and the answers from row 3 moved to row 2 in separate columns.

SAMPLE DATA 2.jpg

 

Thanks in advance for any help

  • One way to do it would be to create a query of all of the columns but under the question column use aware statement that says like "age*"
    You can call that query "first answer"
    Create a new query with just three columns the ID the question and the response but in the question the where should be like "org*"
    Save this query as second answer.
    Now you have one query that will select everything in the row but only for the first question. The other query will have the ID and the answer to the second question. Create a third query and put both the first query and the second query in the grid enjoying it on the ID field. Select all of the columns from first query and the question and response columns from the second query. You'll now have all of the answers for each person on one line. You might want to rename the columns to something like "question 1" "Answer 1" and "question t2" "answer 2".
    Change that query to a make table query and you'll have everything in one table.
  • George_TD's avatar
    George_TD
    Brass Contributor
    One way to do it would be to create a query of all of the columns but under the question column use aware statement that says like "age*"
    You can call that query "first answer"
    Create a new query with just three columns the ID the question and the response but in the question the where should be like "org*"
    Save this query as second answer.
    Now you have one query that will select everything in the row but only for the first question. The other query will have the ID and the answer to the second question. Create a third query and put both the first query and the second query in the grid enjoying it on the ID field. Select all of the columns from first query and the question and response columns from the second query. You'll now have all of the answers for each person on one line. You might want to rename the columns to something like "question 1" "Answer 1" and "question t2" "answer 2".
    Change that query to a make table query and you'll have everything in one table.
    • DJPIN's avatar
      DJPIN
      Copper Contributor
      I did something similar. Ended up creating a stored procedure that took care of the issue.

      Thanks!

      DJ

Resources