transpose query

[Song Su]

I have qrySem with only 4 records result:

sem year
0 2008
1 2008
2 2009
3 2009

Sem is Winter (0), Spring (1), Summer (2) and Fall (3). These would not
change. Just Year change. I want to transform above into a query with just
one record

0 2008 1 2008 2 2009 3 2009

How to do that? Thanks.

 

[Ken Snell MVP]

You use copies of the table:

SELECT T1.sem, T1.year, T2.sem, T2.year, T3.sem, T3.year,
T4.sem, T4.year
FROM ((YourTableName AS T1 CROSS JOIN
YourTableName AS T2) CROSS JOIN
YourTableName AS T3) CROSS JOIN
YourTableName AS T4
WHERE T1.sem = 0 AND T2.sem = 1
AND T3.sem = 2 AND T4.sem = 4;

 

[Song Su]

When I test your code, it says "Syntax error in JOIN operation" and it
highlight the first CROSS. Can you help again? Thanks.

 

[John Spencer MVP]

Try the following. I think Ken was using a different SQL version than JET SQL
("native" Access database engine).

SELECT T1.sem, T1.year
, T2.sem, T2.year
, T3.sem, T3.year
, T4.sem, T4.year
FROM YourTableName AS T1,
YourTableName AS T2,
YourTableName AS T3,
YourTableName AS T4
WHERE T1.sem = 0
AND T2.sem = 1
AND T3.sem = 2
AND T4.sem = 3;

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

 

[Song Su]

Works great! Thanks.

Try the following. I think Ken was using a different SQL version than JET
SQL ("native" Access database engine).

SELECT T1.sem, T1.year
, T2.sem, T2.year
, T3.sem, T3.year
, T4.sem, T4.year
FROM YourTableName AS T1,
YourTableName AS T2,
YourTableName AS T3,
YourTableName AS T4
WHERE T1.sem = 0
AND T2.sem = 1
AND T3.sem = 2
AND T4.sem = 3;

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

 

[Ken Snell MVP]

Try the following. I think Ken was using a different SQL version than JET
SQL ("native" Access database engine).

Thanks, John. My "day job" involves SQL Server, and my brain crosswires
sometimes between it and ACCESS SQL syntaces nowadays!