MS Access Report Problem

My query currently looks like this

Year Value1 Value2 Value 3
2001 8.0 7.3 5.7
2002 3.4 4.9 5.3
2003 8.5 7.4 4.5

When I'm generating my report however, I want the year to accross the top of the page, so I can look at the trend of Value1 over the course of several years.

For example:
Year 2001 2002 2003
Value1 8.0 3.4 8.5
Value2 7.3 4.9 7.4
Value3 5.7 5.3 4.5

I used a "Columnar" layout, and got the values to line up along the left side. The result was:

Year 2001
Value1 8.0
Value2 7.3
Value3 5.7

Year 2002
Value1 3.4
Value2 4.9
Value3 5.3

Year 2003
Value1 8.5
Value2 7.4
Value3 4.5

I also tried the "tabular" layout, and it wouldn't work because I had too many fields. (I have Value1, Value2, ...through Value80+)

However, when I export to excel, and copy/paste transpose, then import it back into access, the "columnar" layout works great!

So I need help with either transposing a query in access, or writing some sort of automated macro to export to excel, transpose, re-import, and create the report. (The only problem is that it has to be flexible, because the number of years will change as more data is added).

Any thoughts? Ideas? Thanks!!

My best bet would be just to export to excel.

I have done this. Even if there are more rows in one report than the next, it still works great. I even added sums, using absolute referencing.

I will find the code and post it. You can then manipulate it to how you want.

Unfortunatley, the code is at work. I am going on Holiday today so i won't be able to post it until i get back which is on the 11th July and i think a little too late for you.

Your best bet is go to planetsourcecode.com and type in excel and choose vb.

Sorry.