Forum Discussion
DJPIN
Apr 11, 2019Copper Contributor
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)
I need to have the questions and the answers from row 3 moved to row 2 in separate columns.
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_TDBrass ContributorOne 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.- DJPINCopper ContributorI did something similar. Ended up creating a stored procedure that took care of the issue.
Thanks!
DJ- NextVoiceUHearCopper Contributor"Stored Procedure" ?? In MS-Access?