Unpivot code

[boufrog]

Beginning table:
IndustryName Rating 3 Months 6 Months Curve Slope
Basic Materials AAA 0.10% 0.10% Postive
Basic Materials AA 0.16% 0.16% Postive
Basic Materials A 0.20% 0.20% Postive
Basic Materials BBB 0.63% 0.63% Postive

Desired Result:

IndustryName Rating Tenor Spread Curve Slope
Basic Materials AAA 3 Months 0.10% Postive
Basic Materials AA 3 Months 0.16% Postive
Basic Materials A 3 Months 0.20% Postive
Basic Materials BBB 3 Months 0.63% Postive
Basic Materials AAA 6 Months 0.10% Postive
Basic Materials AA 6 Months 0.16% Postive
Basic Materials A 6 Months 0.20% Postive
Basic Materials BBB 6 Months 0.63% Postive


Current Code:
SELECT IndustryName as Industry,"3 Months" as Tenor, 3 Months as
Spread FROM [Sector Curves - w/ Slope and 3 Month]
UNION ALL
SELECT IndustryName as Industry,"6 Months" as Tenor, 6 Months as
Spread FROM [Sector Curves - w/ Slope and 3 Month]

getting an error message..."Syntax error (missing operator) in query
exporess '3 Months' "

I'm assuming I'm missing something easy here, any help is greatly
appreciated.

Thanks

 

[John W. Vinson]

Beginning table:
IndustryName Rating 3 Months 6 Months Curve Slope
Basic Materials AAA 0.10% 0.10% Postive
Basic Materials AA 0.16% 0.16% Postive
Basic Materials A 0.20% 0.20% Postive
Basic Materials BBB 0.63% 0.63% Postive

Desired Result:

IndustryName Rating Tenor Spread Curve Slope
Basic Materials AAA 3 Months 0.10% Postive
Basic Materials AA 3 Months 0.16% Postive
Basic Materials A 3 Months 0.20% Postive
Basic Materials BBB 3 Months 0.63% Postive
Basic Materials AAA 6 Months 0.10% Postive
Basic Materials AA 6 Months 0.16% Postive
Basic Materials A 6 Months 0.20% Postive
Basic Materials BBB 6 Months 0.63% Postive


Current Code:
SELECT IndustryName as Industry,"3 Months" as Tenor, 3 Months as
Spread FROM [Sector Curves - w/ Slope and 3 Month]
UNION ALL
SELECT IndustryName as Industry,"6 Months" as Tenor, 6 Months as
Spread FROM [Sector Curves - w/ Slope and 3 Month]

getting an error message..."Syntax error (missing operator) in query
exporess '3 Months' "

I'm assuming I'm missing something easy here, any help is greatly
appreciated.

Since you have blanks in the fieldnames, you must enclose the fieldnames in
square brackets. Try

SELECT IndustryName as Industry,"3 Months" as Tenor, [3 Months] as
Spread FROM [Sector Curves - w/ Slope and 3 Month]
UNION ALL
SELECT IndustryName as Industry,"6 Months" as Tenor, [6 Months] as
Spread FROM [Sector Curves - w/ Slope and 3 Month]

 

[John Spencer]

Any field or table names that have spaces must be surrounded with square
brackets. Also any field or table names that use characters other than
letters and numbers must have brackets around them.

SELECT IndustryName as Industry
,"3 Months" as Tenor
, [3 Months] as Spread
FROM [Sector Curves - w/ Slope and 3 Month]
UNION ALL
SELECT IndustryName as Industry
,"6 Months" as Tenor
, [6 Months] as Spread
FROM [Sector Curves - w/ Slope and 3 Month]


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County