Query to change table layout from columns to rows

[VBAguy22]

Hi,
I have a table with cashflows that go for 100 years across (I know Excel layouts are not fun).
I imported it into Access.
I have the following fields:

StartYr,Y1,Y2....Y100

In the Y1, Y2.. columns we have amounts that the cashflow takes, interest rate varies a lot so I can't re-calculate the amounts in Access. StartYr is just a year, say 1980.

I want to make it into a "normal" format table and have:


StartYr, YrsPassed, Amt


So I will have 100 records for each StartYr with 100 amounts the cash flow would take depending on how many years passed.

I tried using :
(Select [StartYr], "Y1" As YrsPassed, Y1 As [Amt] From CIDAT)
UNION ALL (Select [StartYr], "Y2" , Y2 From CIDAT)
...
etc.. 100 times but Access tells me my query is too complex.
Anyone has another way of doing it?

THX!

 

[lupins46]

Transpose it in XL and re-import it.

 

[VBAguy22]

too big for excel

 

[lupins46]

Sorry I don't understand.
You said the data was in XL so how could it be too big for XL.