Create multiple columns from data in one column

[Bouldergirl]

Hello,

I've got a rather large table (~650,000 rows) with 4 columns: latitude, longitude, month, and a column of data (where each value is a function of latitude, longitude, & month):

lat lon month data
40 120 1 10.23
40 120 2 51.8
40 120 3 0.001
60 180 1 10
60 180 2 13.4
60 180 3 105.9

And I want to create a table with latitude, longitude, and a column of data values for each month (instead of having all the months lumped together in one column):

lat lon month1 month2 month3
40 120 10.23 51.8 0.001
60 180 10 13.4 105.9

But I don't want to have to make 12 select queries and then append them all together because I do this a lot. I've been working on the following code, but I keep getting all sorts of error messages... I'm still a VB novice, and any tips/help ya'll could provide would be mightily appreciated!

(P.S. I couldn't find a way to do this using a crosstab query, as I don't want row headings or any arithmetical operations performed on my data)


Thanks,
-Tiffany

Function makemonthlycolumns()
Dim lat, lon, month2, Eriso, ofilename
Dim db As Database
Dim tdfNew As TableDef
Dim rin, rout
ofilename = "M3_final"
Set db = CurrentDb()
For i = 0 To db.TableDefs.Count - 1 ' Delete table
If db.TableDefs(i).Name = ofilename Then
DoCmd.DeleteObject A_TABLE, ofilename
Exit For
End If
Next
Set tdfNew = db.CreateTableDef(ofilename)
With tdfNew
.Fields.Append .CreateField("lat", dbSingle)
.Fields.Append .CreateField("lon", dbSingle)
.Fields.Append .CreateField("01", dbSingle)
.Fields.Append .CreateField("02", dbSingle)
.Fields.Append .CreateField("03", dbSingle)
.Fields.Append .CreateField("04", dbSingle)
.Fields.Append .CreateField("05", dbSingle)
.Fields.Append .CreateField("06", dbSingle)
.Fields.Append .CreateField("07", dbSingle)
.Fields.Append .CreateField("08", dbSingle)
.Fields.Append .CreateField("09", dbSingle)
.Fields.Append .CreateField("10", dbSingle)
.Fields.Append .CreateField("11", dbSingle)
.Fields.Append .CreateField("12", dbSingle)
db.TableDefs.Append tdfNew
End With
Set rin = db.OpenRecordset("make_22yr_avg")
Set rout = db.OpenRecordset(ofilename)
rin.MoveFirst
Do Until rin.EOF
month2(rin.classN) = rin!month
rout.AddNew
rout![lat] = rin!lat
rout![lon] = rin!lon
For month2 = 1 To 12
If month2 = 1 Then
rout![01] = rin!Eriso
If month2 = 2 Then
rout![02] = rin!Eriso
If month2 = 3 Then
rout![03] = rin!Eriso
If month2 = 4 Then
rout![04] = rin!Eriso
End If
rin.Close: rout.Close


End Function

 

[vbaInet]

Have you considered the report route using multiple columns and grouping by lat? Or implementing subreports

 

[Bouldergirl]

So the solution, which I got from posting in another forum on another site, did in fact turn out to be a simple crosstab query, the SQL of which follows:

TRANSFORM First(Bg.data) AS FirstOfdata
SELECT Bg.lat, Bg.lon
FROM Bg
GROUP BY Bg.lat, Bg.lon
PIVOT Bg.month

In case anyone else finds this thread with a similar question.

 

[vbaInet]

Glad you found a way. Thanks for posting back your solution.