re-arranging layout of survey dataset so each row represents one individual answer

[GREG30043]

Currently, my survey dataset (in MS Access) has one table with the following structure.

Respondent	Q1Answer	Q2Answer	Q3Answer	Q4Answer
Bob	Blue	1972	Agree	Miami
Mary	Green	1974	Agree	Dallas
Susan	Green	1980	Agree	Miami
Joe	Orange	1976	Disagree	Dallas
Jim	Red	1980	Agree	Dallas
John	Red	1970	Agree	Miami

This layout is very efficient for my purposes and I love it. However, my crazy supervisor wants me to display the data in the following manner for a special project of his. Even after trying several times to convince him of all the problems with his desired display, my supervisor insists on this request. But I don't know how to achieve this layout.

Respondent	QNumber	Answer
Bob	1	Blue
Mary	1	Green
Susan	1	Green
Joe	1	Orange
Jim	1	Red
John	1	Red
Bob	2	1972
Mary	2	1974
Susan	2	1980
Joe	2	1976
Jim	2	1980
John	2	1970
Bob	3	Agree
Mary	3	Agree
Susan	3	Agree
Joe	3	Disagree
Jim	3	Agree
John	3	Agree
Bob	4	Miami
Mary	4	Dallas
Susan	4	Miami
Joe	4	Dallas
Jim	4	Dallas
John	4	Miami

Any suggestions?

Note: Of course, the data shown above is just dummy data for illustration purposes. In my real dataset, I have many more fields with much more detail such as Respondent Unique ID Number, FName, LName, etc.

[June7]

Your crazy supervisor wants a normalized dataset. Use a UNION query to manipulate the data to normalized structure. For some reason, forum is not letting me type UNION in the example query. Replace ______ with UNION.

SELECT Respondent, 1 As QNumber, Q1Answer As Answer FROM tablename
______ SELECT Respondent, 2, Q2Answer FROM tablename
______ SELECT Respondent, 3, Q3Answer FROM tablename
______ SELECT Respondent, 4, Q4Answer FROM tablename;

There is no wizard or builder for UNION, must type or copy/paste in SQL View of query designer.

[GREG30043]

What if the cells contain more than 255 characters of text? In such cases, the union query will only display the first 255 characters of text. But with my particular database, I have many cells which are very long and which are set as "Memo" type.

Is there a way I could have MS Access save into a table (that has memo fields) all of the data which has been created by the union query? Then I would be able to export the table for utilization in other programs where I need to be able to read the entire length of the memo field variables.

[June7]

Okay, can maybe run 4 INSERT SELECT sql actions to write records to a 'temp' table. So you end up with the normalized table anyway.
Use DELETE sql action to remove all records from temp table prior to running the procedure. Example VBA:

CurrentDb.Execute "DELETE FROM temp"
CurrentDb.Execute "INSERT INTO temp(Respondent, QNumber, Answer) SELECT Respondent, 1, Q1Answer FROM tablename"
...

[GREG30043]

I think I understand. But where do I run a SQL action, other than inside a query in the SQL design mode?

[June7]

The example code I presented is VBA and it is executing SQL action statements. It can be in a button Click event.

[GREG30043]

I am learning something new! Thank you!

I was able to do this correctly using a simple dummy database but in order for it to work properly I needed to not have the line "CurrentDb.Execute "DELETE FROM temp". This is because if I utilize that line, then when MS Access gets to the second line it give me an error message stating the table "temp" does not exist. By simply not typing the "CurrentDb.Execute "DELETE FROM temp" line I was able to get it to work correctly on my dummy database.

However, when I tried to these steps with my actual database I received an error message: "Run-=time error 3061: too few parameters, expected 1." Here is how I did it in my actual database: I created a blank form with a button. In design view, I clicked the button properties and set the following event procedure for "on click"

Private Sub Command0_Click()
CurrentDb.Execute "INSERT INTO step3normtable(categ, respondent, juris, juriscode, prev, agencyname, meas, measabbrev, QNum, AnswerLong) SELECT Category, respondents_respondent, Jurisdiction, Jurisdiction_code, Prevalence, Agency, Measure, measureabbrev, 3, Col3 FROM [created-table-all-data]"
End Sub

The "step3normtable" table already exists in my database but has no records. It contains all of these fields: categ, respondent, juris, juriscode, prev, agencyname, meas, measabbrev, QNum, AnswerLong

For each field in the "step3normtable", its field type is the same as the field type of the corresponding field in the [created-table-all-data] table. The "created-table-all-data" table also exists in my database and has the following records: IDNum, Category, categories_Row, measureabbrev, respondents_Respondent, Jurisdiction, JurisdictionCode, Prevalence, Importable-data_Respondent, Importable-data_Row, Agency, Measure, Col3, Col4, Col5, Col6, Col7, Col8, Col9.

In my VBA code, this is my intention: I want to INSERT the values of 10 fields INTO the “step3normtable” table, and I have SELECT to specify the source for those 10 fields is the ten variables I have listed after SELECT, and almost all of them are from [created-table-all-data] except for the number “3” which is the value I want to specify for “QNum”. With this code, I want to obtain the values of the Col3 variable and utilize those values for the AnswerLong field (which is “memo” type) and also I want the value of the “QNum” field to be “3”.

But unfortunately I received the error message.

[June7]

Is QNum a text or number field? If it is text then 3 must be enclosed in apostrophes: '3'.

"DELETE FROM temp" should just delete all records from the table. The table should remain. I don't understand why that errors.