ok, this should get you what you want
Based on MS KB:
http://support.microsoft.com/kb/202176
Code:
Function Transposer2(strSource As String, strTarget As String)
'Transpose Function by CT Witter for Mrexcel.com
Dim db As DAO.Database
Dim tdfNewDef As DAO.TableDef
Dim fldNewField As DAO.Field
Dim rstSource As DAO.Recordset
Dim rstTarget As DAO.Recordset
Dim i As Integer
Dim wksp As DAO.Workspace
Set db = CurrentDb()
Set wksp = DBEngine.Workspaces(0) ' set up a transaction buffer
wksp.BeginTrans ' all record set changes are buffered after this
On Error GoTo roll0
Set rstSource = db.OpenRecordset(strSource)
rstSource.MoveLast
' Create a new table to hold the transposed data.
' Create 4 New Columns (Fields)
'
'Key_Name
'Date
'Field_Name
'Data
Set tdfNewDef = db.CreateTableDef(strTarget)
Set fldNewField = tdfNewDef.CreateField("Key_Name", dbText)
tdfNewDef.Fields.Append fldNewField
Set fldNewField = tdfNewDef.CreateField("Date", dbDate)
tdfNewDef.Fields.Append fldNewField
Set fldNewField = tdfNewDef.CreateField("Field_Name", dbText)
tdfNewDef.Fields.Append fldNewField
Set fldNewField = tdfNewDef.CreateField("Data", dbText)
tdfNewDef.Fields.Append fldNewField
db.TableDefs.Append tdfNewDef
rstSource.MoveFirst
Do Until rstSource.EOF
Set rstTarget = db.OpenRecordset(strTarget)
' Open the new table and fill
' the first field with key value the original table.
' the second field with the date
' the third & fourth field are built by looping
' thru the original table
For i = 2 To rstSource.Fields.Count - 1
With rstTarget
.AddNew
.Fields(0) = rstSource.Fields(0).Value
.Fields(1) = rstSource.Fields(1).Value
.Fields(2) = rstSource.Fields(i).Name
.Fields(3) = rstSource.Fields(i).Value
.Update
End With
Next i
rstSource.MoveNext
Loop
wksp.CommitTrans
GoTo finish_it
roll0:
MsgBox Err.Number & " " & Err.Description
wksp.Rollback ' cancel everything if unexpected error
GoTo finish_it
finish_it:
rstSource.Close
Set rstSource = Nothing
rstTarget.Close
Set rstTarget = Nothing
Set db = Nothing
End Function
Call as Transposer2("tblMeter","tblMeterTrans")
This will take:
----------------------------------------------------------------------------------------------------------
| MPAN | DATE | Time1 | Time2 | Time3 |
----------------------------------------------------------------------------------------------------------
| 1 | 7/15/2008 | 1 | 2 | 5 |
----------------------------------------------------------------------------------------------------------
| 2 | 7/20/2008 | 5 | 5 | 5 |
----------------------------------------------------------------------------------------------------------
| 3 | 7/21/2008 | 0 | 0 | 5 |
----------------------------------------------------------------------------------------------------------
And create a new table like:
-------------------------------------------------------------------------------------
| Key_Name | Date | Field_Name | Data |
-------------------------------------------------------------------------------------
| 1 | 7/15/2008 | Time1 | 1 |
-------------------------------------------------------------------------------------
| 1 | 7/15/2008 | Time2 | 2 |
-------------------------------------------------------------------------------------
| 1 | 7/15/2008 | Time3 | 5 |
-------------------------------------------------------------------------------------
| 2 | 7/20/2008 | Time1 | 5 |
-------------------------------------------------------------------------------------
| 2 | 7/20/2008 | Time2 | 5 |
-------------------------------------------------------------------------------------
| 2 | 7/20/2008 | Time3 | 5 |
-------------------------------------------------------------------------------------
| 3 | 7/21/2008 | Time1 | 0 |
-------------------------------------------------------------------------------------
| 3 | 7/21/2008 | Time2 | 0 |
-------------------------------------------------------------------------------------
| 3 | 7/21/2008 | Time3 | 5 |
-------------------------------------------------------------------------------------
Make sure you test on a sample data set first. You may also have to refresh to database window (F5) to have the new table appear. I've wrapped it in a transaction so it should go faster, but this will load the data into memory, so you may want to start with a fresh reboot. If you run out of memory you will need to comment out the transaction setup.
HTH,
CT