Restructuring Data

[karimi]

Hi,
I have hourly temperature climate data for the last 15 years stored in the
following way: every day has 24 temperature values (columns) and then the
next row has the next date with its 24 temperature readings etc...
Date T1 T2 T3 ......................T24

Any idea how I can restructure the data to display every date with its hours
[mm/dd/yyyy hh:mm] and the temperature value (two columns only)? (e.g.
Date/time and Temperature)
Note that the data need to be transposed and autofill to combine the date
with time (which is not stored anywhere)
This table has almost 6000 rows

 

[Steve Schapel]

Karimi,

Presumably this is a one-off requirement, to normalise your data, and as
such it is probably not necessary to look into automating the process.
All you need is to run an Append Query for each of the 24 time columns.
The SQL of such queries will look something like this...

INSERT INTO YourNewTable ( ReadingTime, Temperature )
SELECT DateAdd("h",1,[Date]), [T1]
FROM YourOldTable

Run the above query, then change it to...

INSERT INTO YourNewTable ( ReadingTime, Temperature )
SELECT DateAdd("h",2,[Date]), [T2]
FROM YourOldTable

.... then run this query, etc etc,

I have assumed the 24 columns are for [T1]=1:00am, [T2]=2:00am, etc...
If this is not correct, you will need to afdjust accordingly.

 

[karimi]

Hi Steve,
Thank you for this solution, it worked fine, however creating the 24 queries
manually is very time consuming and not error proofed. I have similar tables
for other parameters than temperature (humidity, wind direction... etc....)
and to do 24 queries for each parameter will take a very long time.
Anyway I can automate this queries?

Thanks,

Karimi,

Presumably this is a one-off requirement, to normalise your data, and as
such it is probably not necessary to look into automating the process.
All you need is to run an Append Query for each of the 24 time columns.
The SQL of such queries will look something like this...

INSERT INTO YourNewTable ( ReadingTime, Temperature )
SELECT DateAdd("h",1,[Date]), [T1]
FROM YourOldTable

Run the above query, then change it to...

INSERT INTO YourNewTable ( ReadingTime, Temperature )
SELECT DateAdd("h",2,[Date]), [T2]
FROM YourOldTable

.... then run this query, etc etc,

I have assumed the 24 columns are for [T1]=1:00am, [T2]=2:00am, etc...
If this is not correct, you will need to afdjust accordingly.

--
Steve Schapel, Microsoft Access MVP

Hi,
I have hourly temperature climate data for the last 15 years stored in the
following way: every day has 24 temperature values (columns) and then the
next row has the next date with its 24 temperature readings etc...
Date T1 T2 T3 ......................T24

Any idea how I can restructure the data to display every date with its hours
[mm/dd/yyyy hh:mm] and the temperature value (two columns only)? (e.g.
Date/time and Temperature)
Note that the data need to be transposed and autofill to combine the date
with time (which is not stored anywhere)
This table has almost 6000 rows

 

[Steve Schapel]

Karimi,

I just did a rough test by replicating the scenario you described. I
manually set up and ran the 24 queries in less than 3 minutes total.
This is less than the amount of time taken to write this reply to you,
and considerably less than my earlier relpy, so to me it is not a "very
long time". I made the first one in the query design view, clicked the
'Run' button, then just edited the numbers in 2 places, clicked 'Run'
again, and so on. You could run code like the following... but frankly
I think it would take more then 3 minutes to set the code up...

Dim i As Integer
For i = 1 To 24
CurentDb.Execute "INSERT INTO YourNewTable ( ReadingTime,
Temperature ) SELECT DateAdd('h'," & i & ",[Date]), [T" & i & "] FROM
YourOldTable"
Next i

As for the humidity, wind direction, etc, I assume these are all now
going into separate fields in the same "YourNewTable"... (or at least
they probably should!), in which case it will be an Update Query rather
than an Append Query for them.

 

[karimi]

Hi Steve,
Thank you very much for your help. I thought that I had to append query and
join tables for other values or fields for same dates/time (e.g. humidity,
wind speed etc...) and that's why I thought I had to do the same task 24
times the number of parameters...
Now I will try the update query suggestion and in this case you are right
about the time taken to do this task.

Thanks again

Karimi,

I just did a rough test by replicating the scenario you described. I
manually set up and ran the 24 queries in less than 3 minutes total.
This is less than the amount of time taken to write this reply to you,
and considerably less than my earlier relpy, so to me it is not a "very
long time". I made the first one in the query design view, clicked the
'Run' button, then just edited the numbers in 2 places, clicked 'Run'
again, and so on. You could run code like the following... but frankly
I think it would take more then 3 minutes to set the code up...

Dim i As Integer
For i = 1 To 24
CurentDb.Execute "INSERT INTO YourNewTable ( ReadingTime,
Temperature ) SELECT DateAdd('h'," & i & ",[Date]), [T" & i & "] FROM
YourOldTable"
Next i

As for the humidity, wind direction, etc, I assume these are all now
going into separate fields in the same "YourNewTable"... (or at least
they probably should!), in which case it will be an Update Query rather
than an Append Query for them.

--
Steve Schapel, Microsoft Access MVP

Hi Steve,
Thank you for this solution, it worked fine, however creating the 24 queries
manually is very time consuming and not error proofed. I have similar tables
for other parameters than temperature (humidity, wind direction... etc....)
and to do 24 queries for each parameter will take a very long time.
Anyway I can automate this queries?

Thanks,