Exporting multiple crosstabs

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,

I am am trying to export many crosstabs using the following code

DoCmd.RunSQL "TRANSFORM Count(RSI.respid) AS Count" _
& " SELECT JPURP_T.[Journey purpose]" _
& " FROM RSI INNER JOIN JPURP_T ON RSI.jpurp = JPURP_T.JPURP" _
& " WHERE (((RSI.timeper)=2) AND ((RSI.vtype)=1) AND ((RSI.site)=3702))" _
& " GROUP BY JPURP_T.[Journey purpose]" _
& " PIVOT RSI.survey;", -1
DoCmd.Save acQuery, "3702test"
DoCmd.OutputTo acQuery, "3702test", "MicrosoftExcel(*.xls)",
"C:\rsi\3702.xls", False, ""

I know that the TRANSFORM command is causing difficulties although I am not
to sure why.
I know the code is probably not very good but I have used something similar
for SELECT queries which worked prefectly for me. I need to keep it in a
similar format as most of the values will change and I will use this code
multiple times, for each of the different values in a single script.
 
I'm not sure what RunSQL has to do with the remainder of the code. Or, why
is the a Save command in your code. Is the crosstab open in design view?
I tried just the last line of your code with a crosstab query and it
correctly exported to Excel.
 
Back
Top