Question Restructuring

[vavam]

I have to work with several people that are using a database with one question per field rather than a field for the Name, a field for the Question ID and a field for the answer. Now I need the data to use with another database to measure performance, but the people do not want to change their table structure. Is there a quick way that they can append to get the data in the structure needed to give to me or that I can do once I have the data (I have to link to theirs)? This will be a monthly thing, so I'd like to be able to do it rather quickly each month.
Here is how it looks now:

Name Q1 Q2 Q3 Q4 Q5

This is how it needs to look:

Name QuestionID Answer


There are quite a few questions. Is there a quick way to do this? Oh and i forgot to mention out of several people...two of them do have it in the structure that is needed.

 

[BobMcClellan]

>>Name Q1 Q2 Q3 Q4 Q5
This is how it needs to look:
Name QuestionID Answer

How about
Select Name, QuestionID = q1 from YourTable
union
Select Name, QuestionID = q2 from YourTable
union
Select Name, QuestionID = q3 from YourTable
union
Select Name, QuestionID = q5 from YourTable
union
Select Name, QuestionID = q5 from YourTable

hth,
..bob

 

[Galaxiom]

How about
Select Name, QuestionID = q1 from YourTable
union
Select Name, QuestionID = q2 from YourTable
etc

That is not valid SQL.

I think this is what Bob was aiming for:

SELECT [Name], 1 As QuestionID, Q1 As Answer FROM TheirTable
UNION ALL
SELECT [Name], 2, Q2 FROM TheirTable
etc

BTW Name is a reserved word and should be avoided.

 

[BobMcClellan]

Galaxiom,
>>That is not valid SQL. I think this is what Bob was aiming for:
-- What I was aiming for was union .. ( distinct ) as opposed to union all which shows
everything including dupes.

I should have used [Name] with brackets though. Thanks.
And again.. I forget that aliasing names in Access requires "as" as opposed to "=". Thanks for these corrections.

 

[vavam]

Thank you for the replies.I appreciate the help. I will try this today.

 

[vavam]

Thank you so much. It worked beautifully!