transform data

[Samuel]

Hello,

Could someone help me with transforming data from a table into a new table?

I have table where i have the following fields in datasheetview:

id | date | image | question1 | question2 | question..N

I would like to have it in this way:

id | date | image | answer
1 01-01-2004 test.tif 100 (this is the value of field question1)

As you can see the first 3 fields remains the same, but the records of those
3 fields should be inserted for each record of the question fields.

Is this possible

 

[Douglas J. Steele]

Well, actually you'll need to add an additional "Question Number" field to
your table in order to know which answer is which...

You can write a union query that will do this transformation for you:

SELECT id, date, image, "1" AS QuestionNumber, question1 AS answer
UNION
SELECT id, date, image, "2" AS QuestionNumber, question2 AS answer
UNION
....
UNION
SELECT id, date, image, "N" AS QuestionNumber, questionN AS answer

 

[Samuel]

Hi douglas,

Thnx for correcting me, i forgot about the extra questionnr.

I can manage what you're suggesting, but there are over 500 fields. There
should be a way to automatically do this. Do you know how?

Thnx

 

[Douglas J. Steele]

No, there's no way to do it automatically. And it's impossible for there to
be over 500 fields: Access has a 255 field limit. (Of course, you could be
connecting via ODBC to a DBMS that does allow more fields...)

You can always write VB code to do it. Something along the lines of the
following untested air-code:

Dim intLoop As Integer
Dim strField As String
Dim strSQL As String


For intLoop = 1 to 500
strField = "question" & intLoop
strSQL = "INSERT INTO NewTable " & _
"(Id, Whatdate, Image, QuestionNb, Answer) " & _
"SELECT Id, [date], Image, " & intloop & ", " & _
strField & " FROM OldTable"
CurrentDb.Execute strSQL, dbFailOnError
Next intLoop

Something I forgot to mention earlier: Date is a reserved word, so should be
avoided as a field (or variable) name. You'll notice I renamed the field in
NewTable (and indicated how you can proceed without renaming the field in
OldTable, since presumably you'll be throwing it away once you're done)

 

[Ezekiël]

Hi Douglas,

I have the same problem and i've used your codeexample but i can't make it
work. Seems there was something to be expected.

No, there's no way to do it automatically. And it's impossible for there to
be over 500 fields: Access has a 255 field limit. (Of course, you could be
connecting via ODBC to a DBMS that does allow more fields...)

You can always write VB code to do it. Something along the lines of the
following untested air-code:

Dim intLoop As Integer
Dim strField As String
Dim strSQL As String


For intLoop = 1 to 500
strField = "question" & intLoop
strSQL = "INSERT INTO NewTable " & _
"(Id, Whatdate, Image, QuestionNb, Answer) " & _
"SELECT Id, [date], Image, " & intloop & ", " & _
strField & " FROM OldTable"
CurrentDb.Execute strSQL, dbFailOnError
Next intLoop

Something I forgot to mention earlier: Date is a reserved word, so should be
avoided as a field (or variable) name. You'll notice I renamed the field in
NewTable (and indicated how you can proceed without renaming the field in
OldTable, since presumably you'll be throwing it away once you're done)


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Hi douglas,

Thnx for correcting me, i forgot about the extra questionnr.

I can manage what you're suggesting, but there are over 500 fields. There
should be a way to automatically do this. Do you know how?

Thnx

field

to

 

[Douglas J. Steele]

Can you provide more details?

What happened? Did you get an error message? If so, what was it?


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Hi Douglas,

I have the same problem and i've used your codeexample but i can't make it
work. Seems there was something to be expected.

No, there's no way to do it automatically. And it's impossible for there to
be over 500 fields: Access has a 255 field limit. (Of course, you could be
connecting via ODBC to a DBMS that does allow more fields...)

You can always write VB code to do it. Something along the lines of the
following untested air-code:

Dim intLoop As Integer
Dim strField As String
Dim strSQL As String


For intLoop = 1 to 500
strField = "question" & intLoop
strSQL = "INSERT INTO NewTable " & _
"(Id, Whatdate, Image, QuestionNb, Answer) " & _
"SELECT Id, [date], Image, " & intloop & ", " & _
strField & " FROM OldTable"
CurrentDb.Execute strSQL, dbFailOnError
Next intLoop

Something I forgot to mention earlier: Date is a reserved word, so

should

be
avoided as a field (or variable) name. You'll notice I renamed the field in
NewTable (and indicated how you can proceed without renaming the field in
OldTable, since presumably you'll be throwing it away once you're done)


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


field

records

of