Output to Spreadsheet

[millardgroups]

I have a query that returns a single row of summary data. I need to
export it to Excel when a user clicks a button. When it exports, I need
the output to run vertically rather than horizontally.

That is, instead of:
Header1 | Header2 | Header3 | Header4 | Header5 ... etc
Result1 | Result 2 | Result3 | Result4 | Result5 ... etc

I need:
Header1 | Result1
Header2 | Result2
Header3 | Result3
Header4 | Result4
Header5 | Result5
etc...

Can you suggest the best way to do this?

Thanks!

Scott

 

[Joseph Meehan]

I have a query that returns a single row of summary data. I need to
export it to Excel when a user clicks a button. When it exports, I
need the output to run vertically rather than horizontally.

That is, instead of:
Header1 | Header2 | Header3 | Header4 | Header5 ... etc
Result1 | Result 2 | Result3 | Result4 | Result5 ... etc

I need:
Header1 | Result1
Header2 | Result2
Header3 | Result3
Header4 | Result4
Header5 | Result5
etc...

Can you suggest the best way to do this?

Thanks!

Scott

Crosstab query?

 

[millardgroups]

Thanks!

Dia is Muire duit

Crosstab query?

 

[kerry_ja]

I have a query that returns a single row of summary data. I need to
export it to Excel when a user clicks a button. When it exports, I need
the output to run vertically rather than horizontally.

That is, instead of:
Header1 | Header2 | Header3 | Header4 | Header5 ... etc
Result1 | Result 2 | Result3 | Result4 | Result5 ... etc

I need:
Header1 | Result1
Header2 | Result2
Header3 | Result3
Header4 | Result4
Header5 | Result5
etc...

Can you suggest the best way to do this?

Thanks!

Scott

How about exporting it to Excel, and then calling Excel from Access to
copy and paste transposed?

 

[dbahooker]

how about not letting the grubby spreadsheet retards to be able to use
your data.

keep your data in a database; spit on anyone that tells you otherwise

 

[millardgroups]

Thanks... That sounds like a clean way to do it... can you suggest some
starting code that would make this happen?

 

[dbahooker]

THIS SHOULD BE THE CROSSTAB RESULTS
Header1 | Header2 | Header3 | Header4 | Header5 ... etc
Result1 | Result 2 | Result3 | Result4 | Result5 ... etc


THIS SHOULD BE THE RAW DATA
Header1 | Result1
Header2 | Result2
Header3 | Result3
Header4 | Result4
Header5 | Result5
etc...

if you really need to 'uncrosstab' the data; then i would look at sql
server 2005 unpivot keyword.

or if you really need to deal with unpivoting by hand; you can use a
union statement.
a) open a new query
b) change to sql view

Select Header1, Result1, 'Result1' as slice

From myQuery

UNION ALL
Select Header2, Result2, 'Result2' as slice

From myQuery

UNION ALL
Select Header3, Result3, 'Result3' as slice

From myQuery

UNION ALL
Select Header4, Result4, 'Result4' as slice

From myQuery

Hope that helps!!

-Aaron