reorganizing antiquated climatology data 1

[mmusashi]

Aloha all, I've got data organized with the year moving down the rows along with a bunch of weather "type" data, and the days in columns...

eg

yearmonth weather unit day1 day2 day3... day31
197701 rain inch 1 0 0 ... 0
197701 hail inch 0 0 0 ... 0
197701 sun time 70 40 30 ... 78
... .... ... .. .. .. ... ..
197702 rain inch .4 6 2 ... 0
197702 hail inch 0 0 0 ... 2
197702 sun min

so what I'd like to do is rearrange the data to read

yearmoday rain hail sun ...
19770101 1 0 70
19770102 0 0 40 ...
19770103 0 0 30 ...
...
19770131 0 0 78 ...
19770201 .4 0

that way I can clear out the "units" variable altogether in the coumn definition, and have a chronological column that makes some sense... I am in access now but I'm taking it over to MySQL. I am open to (any) suggestion(s). So far I have created a table with the correct column names and now I need to filter the data in... thank you all very much
Anson

 

[gol4]

do a union query first them a crosstab

select yearmonth, weather, unit, day1
from tablename
union select yearmonth + 1, weather, unit, day2
from tablename
union select yearmonth + 2, weather, unit, day3
from tablename
etc... thru 31
save it
then do a crosstab based on the union qry

will want to use dateadd function or what ever but this should get you there