1

I know there are topics on this, but the examples all go backwards from what I need to accomplish.

I have data in Access 2016 that looks like this:

Source Data

And I need it turned vertically so it looks like this:

Preferred Output

Etc. The student ID column never changes, but the number of questions does so I think it would have to be some kind of loop until end of record.

Powerquery does this easily in excel, but I need it to be native in access.

Alternatively, if someone can explain how to do this TRANSFORM and PIVOT in Access 2013 SQL backwards I may be able to finish it from there.

Normal pivot and unpivot won't handle this because I need to bring the column title down as a data field AND I need it to loop this move until the end of the record and won't know exactly how many columns there will be each time. So one time the file may go to Q07, and another go to Q43.

My skill level with access is amateur. I can do enough VBA to copy and modify code but not enough to write this in Access.

Thank you for your assistance!

Wintersbite
  • 11
  • 4

2 Answers2

1

You simply use union all:

select studentId, 'Q01' as question, q01 as response
from data
union all
select studentId, 'Q02' as question, q02 as response
from data
union all
. . .
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • That gets me close, but I need to bring the column header down as a field as well. Student ID / QuestionID / Response A000000 / Q01 / 1 A000000 / Q03 / 2 A000001 / Q01 / 3 Etc. – Wintersbite Oct 29 '18 at 15:29
0

First I tried this.

SELECT StudentID, [Questionnaire #] as QuestionnaireID, 'Q01' as Question, [Q01] as Response FROM Random_data_generator UNION ALL
SELECT StudentID, [Questionnaire #] as QuestionnaireID, 'Q02' as Question, [Q02] as Response FROM Random_data_generator UNION ALL
SELECT StudentID, [Questionnaire #] as QuestionnaireID, 'Q03' as Question, [Q03] as Response FROM Random_data_generator UNION ALL
SELECT StudentID, [Questionnaire #] as QuestionnaireID, 'Q04' as Question, [Q04] as Response FROM Random_data_generator UNION ALL
SELECT StudentID, [Questionnaire #] as QuestionnaireID, 'Q05' as Question, [Q05] as Response FROM Random_data_generator UNION ALL
SELECT StudentID, [Questionnaire #] as QuestionnaireID, 'Q06' as Question, [Q06] as Response FROM Random_data_generator UNION ALL
SELECT StudentID, [Questionnaire #] as QuestionnaireID, 'Q07' as Question, [Q07] as Response FROM Random_data_generator
ORDER BY StudentID, Question;

But I couldn't figure out how to do it dynamically for a random number of answers. So I blew the dust off my VB books and ended up with this which I'm sure is poorly coded and will make folks wince. But does work.

Dim db As DAO.Database
Set db = CurrentDb
Dim qdf As DAO.QueryDef

ColumnCount = CurrentDb.TableDefs("Random_data_generator").Fields.Count
ColumnCount2 = ColumnCount - 2
Dim QueryString As String
Dim QueryEntry As String
Dim counter As Integer
Dim counterEntry As String
Dim QueryTest As Recordset
Dim QuestionnaireNum As String
counter = 1


Do While counter <= ColumnCount2

 counterEntry = Format(counter, "00")

    QueryString = "SELECT StudentID, [QuestionnaireNum] as QuestionnaireID, 'Q" & counterEntry & "' as Question, [Q" & counterEntry & "] as Response FROM Random_data_generator UNION ALL " & vbCrLf
    counter = counter + 1
    QueryEntry = QueryEntry + QueryString

 Loop

 counterEntry = Format(counter, "00")

 QueryString = "SELECT StudentID, [QuestionnaireNum] as QuestionnaireID, 'Q" & counterEntry & "' as Question, [Q" & counterEntry & "] as Response FROM Random_data_generator ORDER BY StudentID, Question;"
 QueryEntry = QueryEntry + QueryString

 On Error Resume Next
 DoCmd.DeleteObject acQuery, "tempQry"
 On Error GoTo 0

 Set qdf = db.CreateQueryDef("tempQry", QueryEntry)

 DoCmd.OpenQuery ("tempQry")
Wintersbite
  • 11
  • 4