Is it possible to do a reverse crosstab query?

[fishcakes]

I have obtained some data from the web, which is average sea surface
temperature with a spatial resolution of 4 km. The data is in a format with
the latitude listed as a whole bunch of rows in a field (17-19 etc), and each
longtitude as a separate field (80-82 etc), with the temperature data as
values (31 - 33 oC).
longtitude
latitude 80 81 82 etc
19 32 33 32.5
18 31 31.4 33
17
etc

This format is basically what it would look had the data been in a more
conventional format, with three fields (latitude, longtitue and temperature)
and I had then done a crosstab query using latutide as the row heading,
longtitude as the column heading and temperature as the value field.

My question is:
Is it possible to reverse this kind of crosstab query, and turn this data
matrix back into three fields?

 

[Pieter Wijnen]

Yupp, Some *simple* coding will do it
(excuse compile errors as I'm doing this with no Access on the computer)

assuming first field is latitude & all the rest are longitudes
assuming a table containing the fields latidude, longitude, temperature

Sub deCrossTab()

Dim Db AS DAO.Database
Dim RsCt AS DAO.Recordset
Dim Qdef AS DAO.QueryDef
Dim i As Long

Set Db = Access.CurrentDB
Set RsCT = Db.OpenRecordset("SELECT * FROM TableisCrossTab",
DAO.DbOpenSnapshot)
Set Qdef = Db.CreateQueryDef(VBA.vbNullString)
QDef.SQL = "PARAMETERS pLatitude Long, pLongitude Long, pTemperature Real; "
& _
"INSERT INTO TableisNormalized(Latitude,
Longitude,Temperature) " &_
"VALUES(pLattitude,pLongitude,pTemperature);"
While Not RsCt.EOF
Qdef.Parameters(0).Value = RsCt.Fields(0).Value ' Lattitude
For i = 1 To RsCt.Fields.Count -1
Qdef.Parameters(1).Value = rsCt.Fields(i).Name ' Longitude
Qdef.Parameters(2).Value = RsCt.Fields(i).Value ' Temperature
Qdef.Execute DAO.DbSeeChanges
Next
RsCt.MoveNext
Wend
RsCt.Close : Set RsCt = Nothing
Qdef.Close : Set Qdef = Nothing
Set Db = Nothing
End Sub

HTH

Pieter

 

[Dirk Goldgar]

I have obtained some data from the web, which is average sea surface
temperature with a spatial resolution of 4 km. The data is in a
format with the latitude listed as a whole bunch of rows in a field
(17-19 etc), and each longtitude as a separate field (80-82 etc),
with the temperature data as values (31 - 33 oC).
longtitude
latitude 80 81 82 etc
19 32 33 32.5
18 31 31.4 33
17
etc

This format is basically what it would look had the data been in a
more conventional format, with three fields (latitude, longtitue and
temperature) and I had then done a crosstab query using latutide as
the row heading, longtitude as the column heading and temperature as
the value field.

My question is:
Is it possible to reverse this kind of crosstab query, and turn this
data matrix back into three fields?

Hmm. You could set it up as a union query, with a SELECT statement for
each longitude, all UNIONed together, along these lines:

SELECT Latitude, 80 As Longitude, Temperatures.[80] As Temperature
FROM Temperatures
UNION
SELECT Latitude, 81 As Longitude, Temperatures.[81] As Temperature
FROM Temperatures
UNION
SELECT Latitude, 82 As Longitude, Temperatures.[82] As Temperature
FROM Temperatures
...

Depending on how many longitudes you have to deal with, I suppose it's
possible you may not be able to get all longitudes into one union query.
I'm not sure offhand what limitations there are on union queries.
Ideally, this query (or these queries) would be used as the basis of an
append or make-table query, to put the transformed data turn into a
table that will be easier to work with.

An alternative option to the union query would be to open a recordset on
the original table and loop through the records, looping through the
longitude fields in each record to write records to a different
recordset. That would not be as fast as a single union query, but might
be a useful way to do it if you need multiple union queries, or if
setting up the union query is a pain.

 

[fishcakes]

Hi Pieter,
thanks for your help, this looks to be exactly the kind of thing i need. I
have to admit, however, that i am a bit out of my depth. I tried to use the
code that you wrote in a query in sql view and i am having trouble getting it
to work...
Firstly i get an error message "invalid SQL statement; expected 'DELETE',
'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'"
Aside from that, i am a bit unclear from the code which parts are table
names etc. Would you mind clarifying a little? I know you probably spent
quite some time on this already, so i am sorry to both you...

 

[John Vinson]

I tried to use the
code that you wrote in a query in sql view

<g> That's the problem: it's VBA, not SQL. Rather more different than
Dutch and English...!

John W. Vinson[MVP]

 

[fishcakes]

Hi Dirk,
as a result of my clear lack of VBA programing skills (see the alternate
discussion thread) i decided to give your suggestion a try, at least for a
small number of longtitude values, and it worked a treat. I only have about
70, maybe i will get away with a single query.
I am sure the VBA approach is more elegant, but this moves me forward at
least.
Thanks heaps.

I have obtained some data from the web, which is average sea surface
temperature with a spatial resolution of 4 km. The data is in a
format with the latitude listed as a whole bunch of rows in a field
(17-19 etc), and each longtitude as a separate field (80-82 etc),
with the temperature data as values (31 - 33 oC).
longtitude
latitude 80 81 82 etc
19 32 33 32.5
18 31 31.4 33
17
etc

This format is basically what it would look had the data been in a
more conventional format, with three fields (latitude, longtitue and
temperature) and I had then done a crosstab query using latutide as
the row heading, longtitude as the column heading and temperature as
the value field.

My question is:
Is it possible to reverse this kind of crosstab query, and turn this
data matrix back into three fields?

Hmm. You could set it up as a union query, with a SELECT statement for
each longitude, all UNIONed together, along these lines:

SELECT Latitude, 80 As Longitude, Temperatures.[80] As Temperature
FROM Temperatures
UNION
SELECT Latitude, 81 As Longitude, Temperatures.[81] As Temperature
FROM Temperatures
UNION
SELECT Latitude, 82 As Longitude, Temperatures.[82] As Temperature
FROM Temperatures
...

Depending on how many longitudes you have to deal with, I suppose it's
possible you may not be able to get all longitudes into one union query.
I'm not sure offhand what limitations there are on union queries.
Ideally, this query (or these queries) would be used as the basis of an
append or make-table query, to put the transformed data turn into a
table that will be easier to work with.

An alternative option to the union query would be to open a recordset on
the original table and loop through the records, looping through the
longitude fields in each record to write records to a different
recordset. That would not be as fast as a single union query, but might
be a useful way to do it if you need multiple union queries, or if
setting up the union query is a pain.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Hi Dirk,
as a result of my clear lack of VBA programing skills (see the
alternate discussion thread)

i decided to give your suggestion a
try, at least for a small number of longtitude values, and it worked
a treat. I only have about 70, maybe i will get away with a single
query.
I am sure the VBA approach is more elegant, but this moves me forward
at least.

Great. Post back if you need any help.