How to translate from MS Access to MS SQL?

[ombina]

sql = "SELECT questions.adminID, questions.questionID, questions.surveyID, questions.question, "
sql = sql & "Sum([rbt1r1]/1) AS t1r1, Sum([rbt1r2]/2) AS t1r2, Sum([rbt1r3]/3) AS t1r3, "
sql = sql & "Sum([rbt1r4]/4) AS t1r4, Sum([rbt1r5]/5) AS t1r5, [r4r5]/[Total] AS Percentr4r5, "
sql = sql & "[r3r4r5]/[total] AS Percentr3r4r5, [t1r1]+[t1r2]+[t1r3]+[t1r4]+[t1r5] AS Total, "
sql = sql & "[t1r4]+[t1r5] AS r4r5, [t1r3]+[t1r4]+[t1r5] AS r3r4r5 "
sql = sql & "FROM questions INNER JOIN surveyRating ON questions.questionID = surveyRating.question "
sql = sql & "WHERE questions.surveyID = '" & request.cookies("surveyID&quot & "' AND questions.adminID = '" & request.cookies("collect&quot("adminID&quot & "' "
sql = sql & "GROUP BY questions.adminID, questions.questionID, questions.surveyID, questions.question;"


Thanks,

Ricky

 

[jhall156]

This is running out of a web app right? You have a request object reference in there.

The SQL in the query (with the exception of the literals you need to supply from the cookie) looks T-SQL compliant to me.

JHall

 

[ombina]

Thanks for the quick reply -

Actual my issue is that I can not just copy and paste that MS Access statement and hope it works just like that on MS SQL?

This code was routed for MS Access but table are now in MS SQL and wondering if I need to change syntax? Right now, it's complaining about "column AS colAlias".


Thanks,

Ricky

 

[jhall156]

wherever you are trying to divide by the expression [total] you need to specify the [t1r1]+[t1r2]+[t1r3]+[t1r4]+[t1r5]

JHall