SOLVED
Home

Move Data from One Row to Another in Separate Columns

%3CLINGO-SUB%20id%3D%22lingo-sub-441685%22%20slang%3D%22en-US%22%3EMove%20Data%20from%20One%20Row%20to%20Another%20in%20Separate%20Columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-441685%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20file%20where%20the%20original%20format%20has%20two%20questions%20per%20person%20and%20puts%20the%20questions%20%26amp%3B%20answers%20in%20two%20separate%20rows.%20(See%20rows%202%20%26amp%3B%203%20below)%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%3E%3CSPAN%20class%3D%22lia-message-image-wrapper%20lia-message-image-actions-narrow%20lia-message-image-actions-below%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F108359i239399498AEFD700%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20border%3D%220%22%20alt%3D%22SAMPLE%20DATA.jpg%22%20title%3D%22SAMPLE%20DATA.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%20need%20to%20have%20the%20questions%20and%20the%20answers%20from%20row%203%20moved%20to%20row%202%20in%20separate%20columns.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-left%22%3E%3CSPAN%20class%3D%22lia-message-image-wrapper%20lia-message-image-actions-narrow%20lia-message-image-actions-below%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F108364iBDD05A7B40F30A90%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20border%3D%220%22%20alt%3D%22SAMPLE%20DATA%202.jpg%22%20title%3D%22SAMPLE%20DATA%202.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%20for%20any%20help%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-441685%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3E2010%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3E2013%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3E2016%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EAccess%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EAccess%20Web%20Database%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EApp%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-544504%22%20slang%3D%22en-US%22%3ERe%3A%20Move%20Data%20from%20One%20Row%20to%20Another%20in%20Separate%20Columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-544504%22%20slang%3D%22en-US%22%3EOne%20way%20to%20do%20it%20would%20be%20to%20create%20a%20query%20of%20all%20of%20the%20columns%20but%20under%20the%20question%20column%20use%20aware%20statement%20that%20says%20like%20%22age*%22%3CBR%20%2F%3EYou%20can%20call%20that%20query%20%22first%20answer%22%3CBR%20%2F%3ECreate%20a%20new%20query%20with%20just%20three%20columns%20the%20ID%20the%20question%20and%20the%20response%20but%20in%20the%20question%20the%20where%20should%20be%20like%20%22org*%22%3CBR%20%2F%3ESave%20this%20query%20as%20second%20answer.%3CBR%20%2F%3ENow%20you%20have%20one%20query%20that%20will%20select%20everything%20in%20the%20row%20but%20only%20for%20the%20first%20question.%20The%20other%20query%20will%20have%20the%20ID%20and%20the%20answer%20to%20the%20second%20question.%20Create%20a%20third%20query%20and%20put%20both%20the%20first%20query%20and%20the%20second%20query%20in%20the%20grid%20enjoying%20it%20on%20the%20ID%20field.%20Select%20all%20of%20the%20columns%20from%20first%20query%20and%20the%20question%20and%20response%20columns%20from%20the%20second%20query.%20You'll%20now%20have%20all%20of%20the%20answers%20for%20each%20person%20on%20one%20line.%20You%20might%20want%20to%20rename%20the%20columns%20to%20something%20like%20%22question%201%22%20%22Answer%201%22%20and%20%22question%20t2%22%20%22answer%202%22.%3CBR%20%2F%3EChange%20that%20query%20to%20a%20make%20table%20query%20and%20you'll%20have%20everything%20in%20one%20table.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-563885%22%20slang%3D%22en-US%22%3ERe%3A%20Move%20Data%20from%20One%20Row%20to%20Another%20in%20Separate%20Columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-563885%22%20slang%3D%22en-US%22%3EI%20did%20something%20similar.%20Ended%20up%20creating%20a%20stored%20procedure%20that%20took%20care%20of%20the%20issue.%3CBR%20%2F%3E%3CBR%20%2F%3EThanks!%3CBR%20%2F%3E%3CBR%20%2F%3EDJ%3C%2FLINGO-BODY%3E
DJPIN
New Contributor

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

2 Replies
Solution
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.
I did something similar. Ended up creating a stored procedure that took care of the issue.

Thanks!

DJ
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies