SimpleVBA

  • Thread starter Thread starter psinta
  • Start date Start date
P

psinta

Help! is the a simple VBA for this union query? I want a
VBA because sometimes the question is up to 100 and the
codes too long.
Thanks.

SELECT ParentSurvey.ID, 1 as Question,ParentSurvey.[1]as
answer
FROM ParentSurvey UNION
SELECT ParentSurvey.ID, 2 as Question,ParentSurvey.[2]as
answer
FROM ParentSurvey
union
SELECT ParentSurvey.ID, 3 as Question,ParentSurvey.[3]as
answer
FROM ParentSurvey
union
SELECT ParentSurvey.ID, 4 as Question,ParentSurvey.[4]as
answer
FROM ParentSurvey
 
Psinta,

Can you provide your table structure and some sample data, not sure what you
are trying to do here.
 
Help! is the a simple VBA for this union query? I want a
VBA because sometimes the question is up to 100 and the
codes too long.
Thanks.

SELECT ParentSurvey.ID, 1 as Question,ParentSurvey.[1]as
answer
FROM ParentSurvey UNION
SELECT ParentSurvey.ID, 2 as Question,ParentSurvey.[2]as
answer
FROM ParentSurvey
union
SELECT ParentSurvey.ID, 3 as Question,ParentSurvey.[3]as
answer
FROM ParentSurvey
union
SELECT ParentSurvey.ID, 4 as Question,ParentSurvey.[4]as
answer
FROM ParentSurvey
Click to expand...

VBA is one language, SQL is a different one. There isn't necessarily
any one to one translation!

What are you trying to accomplish here? a Normalizing Union Query, I'd
guess? Can you run it twenty-five questions at a time, appending each
subset to a tall-thin output table?
 
Help! is the a simple VBA for this union query? I want a
VBA because sometimes the question is up to 100 and the
codes too long.
Click to expand...

' This is unchecked air code, so treat it with extreme caution!!
' But it should give you some ideas

For dwQNum = 1 to c_dwMaxQNum
' rather than faffing about with UNIONS, just do the
' append query lots of times over
'
' this should end up looking like
' INSERT INTO GoodTable (IDNum, Question, Answer)
' SELECT ID, 43, [43]
' FROM OldSloppyTable
'
' which should be just about what you want
'
strSQL = "INSERT INTO GoodTable (IDNum, Question, Answer) " & _
"SELECT ID, " & _
Format(dwQNum, "000, ") & _
Format(dwQNum, "\[0\]") & _
"FROM OldSloppyTable "

' remove next line once you know it's working okay
MsgBox strSQL

' carry it out
db.Execute strSQL, dbFailOnError

' and go round again
Next n



Hope that helps


Tim F
 
Thank you very much to all of you. I'm sorry to get back
so late I was so busy at work.
-----Original Message-----
Help! is the a simple VBA for this union query? I want a
VBA because sometimes the question is up to 100 and the
codes too long.
Click to expand...

' This is unchecked air code, so treat it with extreme caution!!
' But it should give you some ideas

For dwQNum = 1 to c_dwMaxQNum
' rather than faffing about with UNIONS, just do the
' append query lots of times over
'
' this should end up looking like
' INSERT INTO GoodTable (IDNum, Question, Answer)
' SELECT ID, 43, [43]
' FROM OldSloppyTable
'
' which should be just about what you want
'
strSQL = "INSERT INTO GoodTable (IDNum, Question, Answer) " & _
"SELECT ID, " & _
Format(dwQNum, "000, ") & _
Format(dwQNum, "\[0\]") & _
"FROM OldSloppyTable "

' remove next line once you know it's working okay
MsgBox strSQL

' carry it out
db.Execute strSQL, dbFailOnError

' and go round again
Next n



Hope that helps


Tim F


.
Click to expand...
 
Back
Top