Transformation of a table 2

[kingz2000]

Hi,

I have a problem regarding a table which I would like to turn around - I'll explain. The table currently looks like this:

A B C D E Month
-----------------------------------------------------
0 1 2 3 4 June
1 2 3 4 5 July
6 7 8 9 10 August

Now I would like to have it in this form:


June July August
---------------------------
4 5 10
3 4 9
2 3 8
1 2 7
0 1 6

I know this is possible with unions, but I'm not sure exactly how. I need the transformation for a report.
Thanks for all advice!

kingsley

 

[lupins46]

Looks like you need to use the crosstab query wizard for this.

 

[kingz2000]

HOw do I do that? Please explain the steps.thanks.

 

[lupins46]

Start a new query.
Choose the Crosstab query wizard.
Follow the steps in the wizard.

 

[kingz2000]

HI,
I have the option the create a query with the help of the wizard or to crreate a query manually....but I can't find the option of creating a cross table with a wizard. I have the normal Access 2000 from Office 2000. Do I need to upgrade??
Please Help.
Thanks.

 

[PHV]

menu Insert -> Query

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kingz2000]

The crottable query wizard seems to only transform one row to column. As side from giving me totals which I am not interested in. I merely want to transform a 6x3 table into a 3x6 table. Am I using the crosstable wrongly??

 

[Spenney]

If I understand correctly, I think that your earlier suggestion of using a union query is the way to go.. try this SQL

SELECT "A" AS Letter, ztblTemp.A AS Val, ztblTemp.Month
FROM ztblTemp
WHERE ztblTemp.Month = "June"
UNION
SELECT "B" AS Letter, ztblTemp.B AS Val, ztblTemp.Month
FROM ztblTemp
WHERE ztblTemp.Month = "June"
UNION
SELECT "C" AS Letter, ztblTemp.C AS Val, ztblTemp.Month
FROM ztblTemp
WHERE ztblTemp.Month = "June"
UNION
SELECT "D" AS Letter, ztblTemp.D AS Val, ztblTemp.Month
FROM ztblTemp
WHERE ztblTemp.Month = "June"
UNION
SELECT "E" AS Letter, ztblTemp.E AS Val, ztblTemp.Month
FROM ztblTemp
WHERE ztblTemp.Month = "June"
UNION
SELECT "A" AS Letter, ztblTemp.A AS Val, ztblTemp.Month
FROM ztblTemp
WHERE ztblTemp.Month = "July"
UNION
SELECT "B" AS Letter, ztblTemp.B AS Val, ztblTemp.Month
FROM ztblTemp
WHERE ztblTemp.Month = "July"
UNION
SELECT "C" AS Letter, ztblTemp.C AS Val, ztblTemp.Month
FROM ztblTemp
WHERE ztblTemp.Month = "July"
UNION
SELECT "D" AS Letter, ztblTemp.D AS Val, ztblTemp.Month
FROM ztblTemp
WHERE ztblTemp.Month = "July"
UNION
SELECT "E" AS Letter, ztblTemp.E AS Val, ztblTemp.Month
FROM ztblTemp
WHERE ztblTemp.Month = "July"
UNION
SELECT "A" AS Letter, ztblTemp.A AS Val, ztblTemp.Month
FROM ztblTemp
WHERE ztblTemp.Month = "August"
UNION
SELECT "B" AS Letter, ztblTemp.B AS Val, ztblTemp.Month
FROM ztblTemp
WHERE ztblTemp.Month = "August"
UNION
SELECT "C" AS Letter, ztblTemp.C AS Val, ztblTemp.Month
FROM ztblTemp
WHERE ztblTemp.Month = "August"
UNION
SELECT "D" AS Letter, ztblTemp.D AS Val, ztblTemp.Month
FROM ztblTemp
WHERE ztblTemp.Month = "August"
UNION SELECT "E" AS Letter, ztblTemp.E AS Val, ztblTemp.Month
FROM ztblTemp
WHERE ztblTemp.Month = "August";

Where ztblTemp is the name of your table

This should give you a result along the lines of

LETTER VAL MONTH
A 0 June
A 1 July
A 6 August
B 1 June etc...

You could then use a CROSSTAB query to display the results as you said in your original post. Something like...

TRANSFORM First(zquniTemp.Val) AS FirstOfVal
SELECT zquniTemp.Letter
FROM zquniTemp
GROUP BY zquniTemp.Letter
PIVOT zquniTemp.Month;

Where 'zquniTemp' is the name of the union query.

I hope that is of some use.

 

[dhookom]

I believe Spenny is correct. I would however suggest modifying the sql to

SELECT "A" AS Letter, ztblTemp.A AS Val, ztblTemp.Month
FROM ztblTemp
UNION
SELECT "B", ztblTemp.B, ztblTemp.Month
FROM ztblTemp
UNION
SELECT "C", ztblTemp.C, ztblTemp.Month
FROM ztblTemp
UNION
SELECT "D", ztblTemp.D, ztblTemp.Month
FROM ztblTemp
UNION
SELECT "E", ztblTemp.E, ztblTemp.Month
FROM ztblTemp;

You don't need the "As" beyond the first select and I don't see any reason to use a WHERE clause.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[kingz2000]

Thanks a lot Spenney!!Excellent!!

 

[Spenney]

No problem.

Duane is quite correct though - My SQL is overly complex (I tried it with 1 month first which is why I had a WHERE clause and unnecessarily included it when adding the other months)

I didn't know that the "AS" was not required after the first SELECT, so thanks Duane for telling me that.

SPEN