Please help on Xtab query...may be simple! 1

[schredder]

Have a table like this:

UL-Ticker 3WHigh 3WLow Boll1 Boll2
-------------------------------------------------
AMGN 33.50 29.10 32.00 32.50
ADEN 61.05 58.10 62.05 60.10

at the end it should look like this:

AMGN ADEN
3WHigh 33.50 61.05
3WLow 29.10 58.10
Boll1 32.00 62.05
Boll2 32.50 60.10

Its like the transpose function in excel...but i cant get it in a crosstab query. is there an easy way to do it? any hint in the right direction is much appreciated. Thanks. Chris

 

[BobJacksonNCI]

Hi,

Sounds straight forward...

Have you run through the wizard?



HTH,
Bob

 

[dhookom]

Creating a crosstab from normalized data is fairly straight-forward. Your initial table looks to be un-normalized so you may need to create a union query first to normalize it.
[tt]
SELECT UL-Ticker, 3WHight as TheVal, "3WHigh" as Measure
FROM tblLikeThis
UNION ALL
SELECT UL-Ticker, 3WLow, "3WLow"
FROM tblLikeThis
UNION ALL
SELECT UL-Ticker, Boll1, "Boll1"
FROM tblLikeThis
UNION ALL
SELECT UL-Ticker, Boll2, "Boll2"
FROM tblLikeThis
[/tt]
You can then create a union query based on the union query with Measure as the Row Heading, UL_Ticker as the Column Heading, and THeVal as the value.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[schredder]

Fantastic....exactly what i needed! Thanks!