Extract data to new fields

[gordzilla2000]

I have a table that was created to accept the responses
from a survey.

The table was created with a field for each response to
each question:
SurveyID autonumber
q1 text 50
q2 text 50
q3 text 50
q4 text 50
etc.

I would like to change this to one record per question
i.e.

SurveyID autonumber
Question_no text 2
Response text 50

A sample of records would then be:
1 Q1 Yes
1 Q2 No
2 Q1 No
2 Q2 Yes

Is there an easy way to do this?

 

[Rodrigo]

try a union query:

make this query and save it.
select SurveryId, Q1 as Question, Response from tblSurvey
union
select SurveryId, Q2 as Question, Response from tblSurvey
union
select SurveryId, Q3 as Question, Response from tblSurvey
union
select SurveryId, Q4 as Question, Response from tblSurvey
union
select SurveryId, Q5 as Question, Response from tblSurvey

then make an append query and append all records from the union query into
your new table.

Rodrigo.



Rodrigo.

 

[gordzilla2000]

That would work except I need to get the Field name "Q2"
into the new table as well as the
The new table will have the fields
SurveyID, Question_no, Reponse
1 Q1 Yes
1 Q2 No

Currently the field Q1 holds the response and the Field
name represents the question number.
There are over 800 fields (questions) in 5 tables

 

[Rodrigo]

Then try this:

select SurveryId, "Q1" as Question, Q1 AS Response from tblSurvey
union
select SurveryId, "Q2" as Question, Q2 AS Response from tblSurvey
union
select SurveryId, "Q3" as Question, Q3 AS Response from tblSurvey
union
select SurveryId, "Q4" as Question, Q4 AS Response from tblSurvey
union
select SurveryId, "Q5" as Question, Q5 AS Response from tblSurvey

Rodrigo.

 

[Rodrigo]

Or this:
This will save you from writting all those union queries. Just change the
loop to the highest number of fields for each table.

Sub test()
Dim db As Database
Dim strSql As String
Dim i As Long
Dim j As Long
Set db = DBEngine(0)(0)
For i = 1 To 100
strSql = "INSERT INTO tmpNewSurvey ( SurveryId, Question,
esponse ) "
strSql = strSql & " SELECT SurveryId, " & """" & "Q" & i & """" & "
AS Question, " & "Q" & i & " AS Response "
strSql = strSql & " FROM tblSurvey; "
db.Execute strSql
Next i
End Sub

Rodrigo.

 

[Guest]

Thanks,
This is much more efficient than doing all the other
queries, however it still doesn't get me the actual
names of the fields into the new table.
In my examples for simplicity I called each question
field q1,q2,etc.
However, the fields are actually name in the format
P1S4Q01 which translates to Part 1 Section 4 Question 1
I need to get the "P1S4Q01" into the new table.

 

[Rodrigo]

Try This:

Basically loops trough all fields on your table and creates and runs the
append sql if the field begins with a P (like in your case).

Btw, this will just work correctly in your case, I been there before (fixing
tables back to the relational model). By no means I encourage the writing of
queries like this on production databases. Only for quick fixes.

sub test
dim db as database
dim tdf as tabledef
dim fld as field
dim strSql as string

set db = dbengine(0)(0)
set tdf = db.tabledefs("tblSurvey")
for each fld in tdf.fields
if left(fld.name,1) = "p" then ' loop trough all fields, but
only if the field starts with p (P1S4Q01)
strSql = "INSERT INTO "
strSql = strSql & " tmpNewSurvey "
strSql = strSql & " ( SurveryId, Question, Response ) "
strSql = strSql & " SELECT "
strSql = strSql & " SurveryId "
strSql = strSql & " , " & """" & tdf.name & """" & " AS
Question "
strSql = strSql & " , [" & tdf.value & "] AS Response "
strSql = strSql & " FROM "
strSql = strSql & " tblSurvey ; "
db.Execute strSql
end if
next fld
set tdf = nothing
set db = nothing

end sub

Hope it helps,

Rodrigo.

 

[Rodrigo]

I had some typos.
sub test()
dim db as database
dim tdf as tabledef
dim fld as field
dim strSql as string

set db = dbengine(0)(0)
set tdf = db.tabledefs("tblSurvey")
for each fld in tdf.fields
if left(fld.name,1) = "p" then ' loop trough all fields, but
only if the field starts with p (P1S4Q01)
strSql = "INSERT INTO "
strSql = strSql & " tmpNewSurvey "
strSql = strSql & " ( SurveryId, Question, Response ) "
strSql = strSql & " SELECT "
strSql = strSql & " SurveryId "
strSql = strSql & " , " & """" & fld.name & """" & " AS
Question "
strSql = strSql & " , " & """" & fld.value & """" & " AS
Response "
strSql = strSql & " FROM "
strSql = strSql & " tblSurvey ; "
db.Execute strSql
end if
next fld
set tdf = nothing
set db = nothing

end sub
Rodrigo.

Try This:

Basically loops trough all fields on your table and creates and runs the
append sql if the field begins with a P (like in your case).

Btw, this will just work correctly in your case, I been there before (fixing
tables back to the relational model). By no means I encourage the writing of
queries like this on production databases. Only for quick fixes.

sub test
dim db as database
dim tdf as tabledef
dim fld as field
dim strSql as string

set db = dbengine(0)(0)
set tdf = db.tabledefs("tblSurvey")
for each fld in tdf.fields
if left(fld.name,1) = "p" then ' loop trough all fields, but
only if the field starts with p (P1S4Q01)
strSql = "INSERT INTO "
strSql = strSql & " tmpNewSurvey "
strSql = strSql & " ( SurveryId, Question, Response ) "
strSql = strSql & " SELECT "
strSql = strSql & " SurveryId "
strSql = strSql & " , " & """" & tdf.name & """" & " AS
Question "
strSql = strSql & " , [" & tdf.value & "] AS Response "
strSql = strSql & " FROM "
strSql = strSql & " tblSurvey ; "
db.Execute strSql
end if
next fld
set tdf = nothing
set db = nothing

end sub

Hope it helps,

Rodrigo.

Thanks,
This is much more efficient than doing all the other
queries, however it still doesn't get me the actual
names of the fields into the new table.
In my examples for simplicity I called each question
field q1,q2,etc.
However, the fields are actually name in the format
P1S4Q01 which translates to Part 1 Section 4 Question 1
I need to get the "P1S4Q01" into the new table.

 

[John Vinson]

So close.

The fld.value comes up as an error every time.
I change both of them to read fld.name
Which results in the following SQL statement:

"INSERT INTO tmpNewSurvey ( SurveyId, Question, Response ) SELECT SurveyId , "P1S1Q01" AS Question , "P1S1Q01" AS Response FROM tblSurvey ; "

This is perfect except the Response portion should read P1S1Q01 AS Response with no " around the field name.
This SQL statement gives me the exact extraction I need. I just can't figure out how to amend strSql to get rid of the "

Replace them with [Square brackets]:

INSERT INTO tmpNewSurvey ( SurveyId, Question, Response ) SELECT
SurveyId , [P1S1Q01] AS Question , [P1S1Q01] AS Response FROM
tblSurvey ;

 

[Gordzilla]

Not that simple.
On the line indicated below, I need the fieldname to be evaluated but with
the quotes

strSql = "INSERT INTO "
strSql = strSql & " tmpNewSurvey "
strSql = strSql & " ( SurveryId, Question, Response ) "
strSql = strSql & " SELECT "
strSql = strSql & " SurveryId "
strSql = strSql & " , " & """" & fld.name & """" & " AS
Question "
===> strSql = strSql & " , " & """" & fld.name & """" & " AS Response
"
strSql = strSql & " FROM "
strSql = strSql & " tblSurvey ; "

Currently it gives me strSQL ="INSERT INTO tmpNewSurvey ( SurveyId,
Question, Response ) SELECT SurveyId , "P1S1Q01" AS Question ,
"P1S1Q01" AS Response FROM tblSurvey ; "
and I need
strSQL ="INSERT INTO tmpNewSurvey ( SurveyId, Question, Response )
SELECT SurveyId , "P1S1Q01" AS Question , P1S1Q01 AS Response FROM
tblSurvey ; "


SurveyId , "P1S1Q01" AS Question , "P1S1Q01" AS Response FROM
tblSurvey ; "figure out how to amend strSql to get rid of the "

Replace them with [Square brackets]:

INSERT INTO tmpNewSurvey ( SurveyId, Question, Response ) SELECT
SurveyId , [P1S1Q01] AS Question , [P1S1Q01] AS Response FROM
tblSurvey ;

 

[Tim Ferguson]

strSql = strSql & " , " & _
"""" & fld.name & """" & "AS Question "
strSql = strSql & " , " & _
"""" & fld.name & """" & " AS Response "

I know that the AS alias is not neccessary, and I am not sure if it's
legal.

Tim F

 

[Rodrigo]

Take out the extra quotes.

strSql = "INSERT INTO "
strSql = strSql & " tmpNewSurvey "
strSql = strSql & " ( SurveryId, Question, Response ) "
strSql = strSql & " SELECT "
strSql = strSql & " SurveryId "
strSql = strSql & " , " & """" & fld.name & """" & " AS Question "
strSql = strSql & " , " & fld.name & " AS Response"
strSql = strSql & " FROM "
strSql = strSql & " tblSurvey ; "

Rodrigo.

Not that simple.
On the line indicated below, I need the fieldname to be evaluated but with
the quotes

strSql = "INSERT INTO "
strSql = strSql & " tmpNewSurvey "
strSql = strSql & " ( SurveryId, Question, Response ) "
strSql = strSql & " SELECT "
strSql = strSql & " SurveryId "
strSql = strSql & " , " & """" & fld.name & """" & " AS
Question "
===> strSql = strSql & " , " & """" & fld.name & """" & " AS Response
"
strSql = strSql & " FROM "
strSql = strSql & " tblSurvey ; "

Currently it gives me strSQL ="INSERT INTO tmpNewSurvey ( SurveyId,
Question, Response ) SELECT SurveyId , "P1S1Q01" AS Question ,
"P1S1Q01" AS Response FROM tblSurvey ; "
and I need
strSQL ="INSERT INTO tmpNewSurvey ( SurveyId, Question, Response )
SELECT SurveyId , "P1S1Q01" AS Question , P1S1Q01 AS Response FROM
tblSurvey ; "


SurveyId , "P1S1Q01" AS Question , "P1S1Q01" AS Response FROM
tblSurvey ; "figure out how to amend strSql to get rid of the "

Replace them with [Square brackets]:

INSERT INTO tmpNewSurvey ( SurveyId, Question, Response ) SELECT
SurveyId , [P1S1Q01] AS Question , [P1S1Q01] AS Response FROM
tblSurvey ;

 

[Gordzilla]

I had already removed the extra quotes, with no luck.

Cut and paste the exact code and strSQL always evaluates to this:

"INSERT INTO tmpNewSurvey ( SurveyID, Question, Response ) SELECT
SurveyID, "& fld.Name & " AS Question, , "P1S1Q01" AS Response FROM test
; "

Then it errors on the excute.

 

[Rodrigo]

I just created a table tmpTbl with 6 fields, SurveyId, Q1-Q5 and ran this
code, and this is what I've got. As you can see is workign correctly.

Sub test()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim strSql As String

Set db = DBEngine(0)(0)
Set tdf = db.TableDefs("tmpTbl")
For Each fld In tdf.Fields
strSql = "INSERT INTO "
strSql = strSql & " tmpNewSurvey "
strSql = strSql & " ( SurveryId, Question, Response ) "
strSql = strSql & " SELECT "
strSql = strSql & " SurveryId "
strSql = strSql & " , " & """" & fld.Name & """" & " AS Question "
strSql = strSql & " , " & fld.Name & " AS Response"
strSql = strSql & " FROM "
strSql = strSql & " tblSurvey ; "
Debug.Print strSql
Next fld
Set tdf = Nothing
Set db = Nothing
End Sub


INSERT INTO tmpNewSurvey ( SurveryId, Question, Response ) SELECT
SurveryId , "Q1" AS Question , Q1 AS Response FROM tblSurvey ;
INSERT INTO tmpNewSurvey ( SurveryId, Question, Response ) SELECT
SurveryId , "Q2" AS Question , Q2 AS Response FROM tblSurvey ;
INSERT INTO tmpNewSurvey ( SurveryId, Question, Response ) SELECT
SurveryId , "Q3" AS Question , Q3 AS Response FROM tblSurvey ;
INSERT INTO tmpNewSurvey ( SurveryId, Question, Response ) SELECT
SurveryId , "Q4" AS Question , Q4 AS Response FROM tblSurvey ;
INSERT INTO tmpNewSurvey ( SurveryId, Question, Response ) SELECT
SurveryId , "Q5" AS Question , Q5 AS Response FROM tblSurvey ;


On the below it looks like tou have a single quote right after the coma on
the surveyid
SurveyID, '" & fld.name & &"' AS Question.

Take it out, also it also looks like there is an extra comma right after
Question (were did it come from?)
And the double quotes (or single quote) right after the commas on question.

Rodrigo.

 

[Rodrigo]

This is turning like the longest post ever (not big, rather a long time,
going on 10 days so far..

Rodrigo.