union query? 1

[Machiaveli]

Hi,

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 | questionnr | answer
1 01-01-2004 test.tif 1 1000 (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. I can do this with a union query for each questionfield, but.....

How can i do this automatically, because there are more than 500 columns.

 

[SkipVought]

Hi,

Paste this code into a vba module and run...

Sub UnionQuery()
    sql1 = "select id, date, image,"
    For i = 1 To 500
        If i = 1 Then
            Sql = Sql & sql1 & i & ", question" & i & " From MyTable "
        Else
            Sql = "Union " & Sql & sql1 & i & ", question" & i & " From MyTable "
            Cells(i, 1).Value = "Union "
        End If
        Cells(i, 1).Value = Cells(i, 1).Value & sql1 & i & ", question" & i & " From MyTable "
    Next
    docmd.runsql Sql
End Sub

Skip,
[sub]
[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large! [/sub]

 

[SkipVought]

...

naturally, substitute the correct Table Name.

Skip,
[sub]
[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large! [/sub]

 

[Machiaveli]

Hi SkipVought,

Thnx for your help, but i've pasted the code in vba in access, but it can't recognize the cells part of the code. Something about sub or function not defined.

Is it a code for excell?

 

[SkipVought]

sorry.

Please remove the cells statement. I used that for testing in Excel.

Skip,
[sub]
[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large! [/sub]

 

[Machiaveli]

Hi Skip,

I've removed the cells part but i'm still getting an error message.

It seems that the sql is invalid. The errormessage is:

expected delete, select, etc.

 

[SkipVought]

Sorry, I had the Union missplaced...

    For i = 1 To 500
        If i = 1 Then
            Sql = Sql & sql1 & i & ", question" & i & " From MyTable "
        Else
            Sql = Sql & "Union " & sql1 & i & ", question" & i & " From MyTable "
        End If
    Next

Skip,
[sub]
[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large! [/sub]

 

[Machiaveli]

Hi skip,

I really appreciate the help you've given me.

I've changed the code a little bit, because i forgot something. The question must also be a fieldname and the value to (see code below).

The docmd.runsql still gives me errors about invalid statements. I used debug.print to get some results and i ran it in sql analyzer of sql server and it worked after a few adjustments.

Public Sub Union()

Dim i As Integer
Dim sql As String
Dim sql1 As String

sql1 = "select id, date, time, reference , 'question' as variablename, 'question' as value "
For i = 1 To 501
If i = 1 Then
sql = sql & sql1 & ", question" & i & " From MyTable "
Debug.Print sql
Else
sql = sql & "Union " & sql1 & ", question" & i & " From MyTable "
Debug.Print sql
End If
Next
DoCmd.RunSQL sql, False

End Sub

Can you modify it so it can work for me?

 

[SkipVought]

Public Sub Union()

Dim i As Integer
Dim sql As String
Dim sql1 As String

sql1 = "select id, date, time, reference, "
 For i = 1 To 501
        If i = 1 Then
            sql = sql & sql1 & " question" & i & ", " & i & " From MyTable "
            Debug.Print sql
        Else
            sql = sql & "Union " & sql1 & " question" & i & ", " & i & " From MyTable "
            Debug.Print sql
        End If
    Next
DoCmd.RunSQL sql, False

End Sub

Skip,
[sub]
[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large! [/sub]