generate multiple columns based on data in 1 column 1

[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!


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

 

[Remou]

Have you considered a Crosstab query?

http://lessthandot.com

 

[Bouldergirl]

I did look into a crosstab query, but I don't want to have row headings or perform any sort of arithmetical operation, and the crosstab query wizard wouldn't let me do it the way I wanted to.

 

[Remou]

You can use First.

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

I am not sure what you mean by "I don't want to have row headings"

http://lessthandot.com

 

[Bouldergirl]

Yes, that was exactly what I needed; thanks!!!!

 

[dhookom]

Bouldergirl,
It is appropriate to "Thank Remou for this valuable post!" when a correct answer is provided. This shows the thread has been answered and awards Remou.

Duane
Hook'D on Access
MS Access MVP

 

[thart21]

Tagging onto this post. Trying to do a similar thing (Just posted it right before I saw this, although I don't see my post yet) I am not trying to create a table, but a query (not sure if that matters).
Was the crosstab suggestion and example in ADDITION to the Function that the original poster had submitted? I want to try and modify this for my needs.

Thanks!

Toni

 

[dhookom]

The crosstab replaces all of the code.

Duane
Hook'D on Access
MS Access MVP

 

[thart21]

Thanks for the reply, I'm looking for something that will actually create 4 new columns in my query and take data from another column and disperse it to the 4 new ones based on product_id.

Thinking a For Each....

Thanks for getting back to me, appreciate it!

 

[dhookom]

Is there still a question here or do you have the information you need? If you need more assistance you need to provide more information.

Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

Tagging onto this post.

We do not do that here.

Please post your questions in a NEW THREAD.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}