exporting crosstab queries

[gyang]

I created a crosstab query from 1 table with the following fields:
-Group by Activity_Type, Collaborating_Agency, Duration as row headings
-Group by Client_Type as column heading
-Sum of No_per_Client_Type as value


The following is the raw SQL:

TRANSFORM Sum(final.[No_per_Client_Type]) AS [SumOfNo_per_ Client_Type]
SELECT final.[Activity_Type], final.[Collaborating_Agency], final.Duration
FROM final
GROUP BY final.[Activity_Type], final.[Collaborating_Agency], final.Duration
ORDER BY final.[Activity_Type], final.[Collaborating_Agency], final.Duration
PIVOT final.[Client_Type];

When I ran the crosstab query, the numbers were correct. When I tried to export into an Excel format, the numbers became incorrect. The No_per_Client_Type was under the wrong Client_Type.

I resolved the problem by "Selecting All Records" and did a copy and paste into Excel.

I think I remember reading something about not being able to export crosstab directly into Excel without the data being corrupted. Is that correct? Or is there a better way to to do this?

Thanks,
gy

 

[Tim K.]

Why don't you create a new table based on the crosstabl query by using Make Table query, then export the newly created table to Excel.
See if it helps!

 

[thouston]

The only problem with Tim's solution is that you lose the crosstab format when you make-table from a crosstab query. Very tedious & I generally end up going down the copy-paste route!

 

[gyang]

Thanks Tim and thouston.

Since the mkTable loses it's crosstab format, I tried making another crosstab from the mkTable, but again the data was incorrect when I exported it into Excel.

Quite puzzled why this happens? I'll stick with the copy and paste then.

 

[Pat Hartman]

How are you trying to export the query. I've tried and had no trouble.

 

[gyang]

I did a right click on the crosstab query in the queries menu:
-selected "Export"
-window pops up to save
-I keep the same File name
-Change the Save as type-->Microsoft Excel 97-2000 (instead of Microsoft Access)
-hit Save

The column and row headings look correct, but the actual value that are suppose to be in the row per column has been moved around.

 

[Kylor]

CrossTab Export

You are not crazy. I am having the same problem. So, is the answer...don't export a crosstab or is there a fix or solution?

 

[thouston]

Check that you've actually saved the query in its own right before doing a "save as". Export reverts to the data selected by the last saved version of the query - that's caught me out on more than one occasion.

 

[cunnie]

I got my crosstab query to export to excell without corrupting the data.

I created a macro, and gave it an "OutputTo" action, then changed the action arguments so the output format was *.xls

hope this helps,
cunnie

 

[Kylor]

Crosstab exporting

Excellent! Can you e-mail me the details/copy of the macro?

Thanks

Kglover@gbc.com

 

[cunnie]

heres a screenshot of the macro design view, this should tell you everything you need to know.


cunnie