Tranposing/Using rows/field values as column headings

[NoviceAccessUser-Melanie]

I created a db that tracks original, actual and discounted yearly numbers for
each deals. I capture the numbers through a continuous subform.
Deal
Actuals Acctgdata1 AcctgData2 AcctgData3
Yr1
Yr2
Y3-Yr20
Original AcctData1 AcctgData2 AcctgData3
Yr1
Yr2
Yr3

Now, I need to be able to get these numbers and display them in a rpt with
the Yr1, Yr2, Yr3 etc as the column headings.
Does anyone have any suggestions on how I can do this? Currently, I have
small tables to house Actual, Original and Discounted. I would really
appreciate any help, hopefully something a novice can make sense out of.
Thank you.

 

[KARL DEWEY]

First put Actual, Original and Discounted all in one table using an
additional field to designate which is which.

It seems like you need a crosstab query but you did not give a complete
example of what you want the results to look like.

 

[NoviceAccessUser-Melanie]

The result should look like this:
Deal 1
There are 6 sets of AcctgData numbers in the tables.

 

[KARL DEWEY]

Your table should look like that below.
Type Year Acctgdata
Actuals 2002 11
Original 2002 12
discounted 2002 21
Actuals 2003 9
Original 2003 31
discounted 2003 12
Actuals 2004 12
Original 2004 21
discounted 2004 9

Because it does not I used a union query to pull it like shown above.

Melanie_1 ---
SELECT Melanie.Type, Melanie.Year, Melanie.Acctgdata1
FROM Melanie
UNION ALL SELECT Melanie.Type, Melanie.Year, Melanie.Acctgdata2
FROM Melanie
UNION ALL SELECT Melanie.Type, Melanie.Year, Melanie.Acctgdata3
FROM Melanie;

Then a crosstab query to display it.
TRANSFORM Sum(Melanie_1.Acctgdata1) AS SumOfAcctgdata1
SELECT Melanie_1.Type
FROM Melanie_1
GROUP BY Melanie_1.Type
PIVOT Melanie_1.Year;

 

[NoviceAccessUser-Melanie]

By using the first tip you offered, I am now able to get the data for either
year and type out through crosstab queries. Now I am having difficulty
merging them back without exceeding the 255 field limit in a query.

I combined all the data into one table and used an identifier for the type
of data.
TYPE Year DataField1
DataField2 DataField3 etc
Actual "Yr1"
Discounted "Yr2"
Original Yr3"...(right now through Yr20)

I have the users select if an entry is Actual, and for what year of the Deal
it is.

Here's what my report should look like:

Deal No. Yr.1 Yr2 Yr3 Yr4
....
111
Actuals: 725 5301 17605 2459
Discounted: 525 5201 15605 2159
Original: 825 5401 1900 2700

112
Actuals: 1111 2222 3333 4444
Discounted: 999 888 777 666
Original: 1000 2000 3000 4000

The Crosstab query below was able to get me this result:
TRANSFORM First(LIHTC_1.LIHTC) AS ALLLIHTC
SELECT LIHTC_1.Ind, LIHTC_1.DealID
FROM LIHTC_1
GROUP BY LIHTC_1.DealID, LIHTC_1.Ind
PIVOT LIHTC_1.YearID;

Result:
DealID Yr1 Yr2 Yr3
Yr4
Actuals DataField1 DataField1 DataField1
DataField1
Original DataField1 DataField1 DataField1
DataField1
Discounted DataField1 DataField1 DataField1 DataField1

I would like to be able to get more datafields (Ex. LIHTC) added to my
results. If I do a separate query for each datafield, by the time I create a
query to bring them all together, I will exceed the 255 field limit. Is there
another approach I can take that is not apparent to me right now? I would
greatly appreciate any thoughts on how to tackle this issue.
Thank you.