swap query axis for export toe xcel or for for reporting

[Ian Burton]

hi,
I am trying to find out how to swap the axis of a table in order to
make reporting more user friendly.

I have a financial analysis reporting tool ,which pulls data from SQL
Server. I need to create a report or query, which I can export to
excel, which mimics the behaviour of the manual excel report that some
analysts have produced. It is actually quite logical to do it this
way, just seems impossible in Access.
An example of the table format is here:

Field: week number - A
Field: Date - B
Field: average age of Outstanding invoices - C
Field: total debtors. - D

So when I view it normally in access, and normal reports, it shows
with a seperate record on each horizontal line, and the more records
going down the page. It looks like this:

weekNo date C D
12 13/3/2009 34 135
13 20/3/2009 78 298
etc etc

the problem is that field labels are very long, and there are a lot of
them, so having it across the top does not really work well.

An example of the report format I want is here.
week no: 12 13 ...
date: 13.3.2009 20/3/2009 ...
C 34 78
D 135 298

I have been looking at transposing in excel ,but it never works
properly, and is too unreliable Ideally I want it in Access, then I
can always export from ma query or report for the user later.

Any ideas at all on how to approach this problem. third party tools,
anything.#
thanks in advance for your help.
Ian Burton

 

[Duane Hookom]

Assuming field names of WeekNo, Date, AvgAge, and TotalDebtors and table name
of tblFormat ...

First create a union query [quniFormat] of your table
=== quniFormat ===========================
SELECT tblFormat.WeekNo,"Date" as RowHead, [Date] as TheValue
FROM tblFormat
UNION ALL
SELECT WeekNo, "AvgAge", AvgAge
FROM tblFormat
UNION ALL
SELECT WeekNo, "TotalDebtors", TotalDebtors
FROM tblFormat ;
==============================
Then create a crosstab based on the union query:

TRANSFORM First(quniFormat.TheValue) AS FirstOfTheValue
SELECT quniFormat.RowHead
FROM quniFormat
GROUP BY quniFormat.RowHead
PIVOT quniFormat.WeekNo;

 

[Ian Burton]

Duane,
That is really hardcore, but it works just fine. Thnak you for your
input. I am very grateful. It does seem a lot to go through, and I
guess performance is going to be a beast with a lot of data, but It
does deliver the goods.
Thanks again,#
Ian

 

[Ian Burton]

Duane,
This method does work, but only for a small number of fields. Any
more than about 6000 characters ( and 49 union select statements) in
the sql statement, and Access returns an error, and says it is too
complex. I had to remove a few fields to get it to work.

Ire there any other ideas for making this work with a large number of
fields? ( well, actually only 51 fields, making 51 unions ( it only
accepted 49 before it failed)
Thanks,
Ian

 

[Duane Hookom]

I suppose you could write some code that would loop through your records to
create a temporary table that looks like the results of the union query.
Then, finish with the crosstab.
--
Duane Hookom
Microsoft Access MVP

Duane,
This method does work, but only for a small number of fields. Any
more than about 6000 characters ( and 49 union select statements) in
the sql statement, and Access returns an error, and says it is too
complex. I had to remove a few fields to get it to work.

Ire there any other ideas for making this work with a large number of
fields? ( well, actually only 51 fields, making 51 unions ( it only
accepted 49 before it failed)
Thanks,
Ian

Assuming field names of WeekNo, Date, AvgAge, and TotalDebtors and table name
of tblFormat ...

First create a union query [quniFormat] of your table
=== quniFormat ===========================
SELECT tblFormat.WeekNo,"Date" as RowHead, [Date] as TheValue
FROM tblFormat
UNION ALL
SELECT WeekNo, "AvgAge", AvgAge
FROM tblFormat
UNION ALL
SELECT WeekNo, "TotalDebtors", TotalDebtors
FROM tblFormat ;
==============================
Then create a crosstab based on the union query:

TRANSFORM First(quniFormat.TheValue) AS FirstOfTheValue
SELECT quniFormat.RowHead
FROM quniFormat
GROUP BY quniFormat.RowHead
PIVOT quniFormat.WeekNo;

 

[Ian Burton]

Duane,
That is a good idea. I will do that instead.
Thanks for such a great help. I feel that I should have thought of
these things, but I didn't. Ah well, I must be getting old!
Thanks again,
Ian