Crosstab query trouble

[wh00t]

Is it possible to get more than 3 row headings?

I have a database and I have 12 fields which I need to format in crosstab style, is there any other way to do this?


EDIT - Can this by done by exporting to Excel? If so can I have a little advise?

 

[Rich]

Base the crosstab on a union query

 

[wh00t]

I've never created a union query, is there a good example flying around anywhere?

 

[wh00t]

I don't think Union Query is what I want

current status

table
field1 - field2 - field3 - field4 - field5 - field6 - field7 - field8


what I want to achieve is
-------field1 as colum headers
field2
field3
field4
field5
field6
field7
field8

 

[Rich]

Here's one that selects from two fields and adds another calculated one
SELECT Deductions.Heading, Sum(IIf(FinancialYear([TrDate])=FinancialYear(Date()),(([Debits]/FinancialWeek([TrDate])*(52-FinancialWeek([TrDate]))+([Debits]))),[Debits])) AS SumOfDebits, FinancialYear([TrDate]) AS FYear
FROM Deductions
WHERE (((Deductions.Heading)<>"Receipts") AND ((FinancialYear([TrDate]))>FinancialYear(Date())-3))
GROUP BY Deductions.Heading, FinancialYear([TrDate]);

UNION SELECT Deductions.Heading, Sum(IIf(FinancialYear([TrDate])=FinancialYear(Date()),(([Credits]/FinancialWeek([TrDate])*(52-FinancialWeek([TrDate]))+([Credits]))),[Credits])) AS SumOfCredits, FinancialYear([TrDate]) AS FYear
FROM Deductions
WHERE (((Deductions.Credits)>0) AND ((FinancialYear([TrDate]))>FinancialYear(Date())-3))
GROUP BY Deductions.Heading, FinancialYear([TrDate]);

UNION SELECT "Profit" AS Profit, Sum(IIf(FinancialYear([TrDate])=FinancialYear(Date()),(([Credits])-(IIf(

="EquipPurchase",0,[Debits])))/FinancialWeek([TrDate])*(52-FinancialWeek([TrDate]))+([Credits])-(IIf(

="EquipPurchase",0,[Debits])),([Credits])-(IIf(

="EquipPurchase",0,[Debits])))) AS Expr1, FinancialYear([TrDate]) AS FYear
FROM Deductions
WHERE (((FinancialYear([TrDate]))>FinancialYear(Date())-3))
GROUP BY "Profit", FinancialYear([TrDate]);​

 

[wh00t]

thanks for the info

I achieved what I wanted, but the powers that be dont want it like that!!!

basically they want to turn the whole thing on it's side

so instead of

field1- field2 - field3 - field4 - field5 etc

want to transform it to
field1--
field2--
field3--
field4--
etc

is this possible?

 

[Jon K]

In Access, you can do it with VBA code. However I think it is easier if you export the table to Excel and transform the data there.

Try this:-

Export table to Excel.

In Excel, select the whole data, click Copy
Click where you want to paste the data
Choose menu Edit, Paste Special..., select Transpose, click OK