Atomic Shrimp
Humanoid lifeform
- Local time
- Today, 04:43
- Joined
- Jun 16, 2000
- Messages
- 1,954
I've often wanted code that would take a table with any number of fields, say:
Store,SalesJan,SalesFeb,SalesMar,SalesApr
1,100,150,170,120
2,50,75,90,80
And convert it to a format like:
Store, Month, SalesValue
1,Jan,100
1,Feb,150
1,Mar,170
1,Apr,120
2,Jan,50
2,Feb,75
2,Mar,90
2,Apr,80
The other day, I figured out how to do it; here's the code:
Store,SalesJan,SalesFeb,SalesMar,SalesApr
1,100,150,170,120
2,50,75,90,80
And convert it to a format like:
Store, Month, SalesValue
1,Jan,100
1,Feb,150
1,Mar,170
1,Apr,120
2,Jan,50
2,Feb,75
2,Mar,90
2,Apr,80
The other day, I figured out how to do it; here's the code:
Code:
Dim db As Database
Dim rstin As Recordset
Dim rstout As Recordset
Dim strsql As String
Dim recordloop As Integer
Dim fieldloop As Integer
Set db = CurrentDb()
strsql = "SELECT [SalesIn].* FROM [SalesIn];"
Set rstin = db.OpenRecordset(strsql, dbOpenSnapshot)
Set rstout = db.OpenRecordset("SELECT SalesOut.* FROM SalesOut;", dbOpenDynaset)
rstin.MoveLast
rstin.MoveFirst
For recordloop = 1 To rstin.RecordCount
For fieldloop = 0 To rstin.Fields.Count - 1
If (rstin.Fields(fieldloop).Name Like "Sales*") Then
With rstout
.AddNew
!Store = rstin!Store.Value
!SalesValue = rstin.Fields(fieldloop).Value
!Month = Mid(rstin.Fields(fieldloop).Name,6)
'NB the Mid Statement above just chops off 'Sales' from the fieldname to leave the name of the month
.Update
End With
End If
Next fieldloop
rstin.MoveNext
Next recordloop
db.Close