Converting column data to row data

Question

I am very limited in my sql abilities, I have only used simple queries and joins in MS Access. My task is to convert this data:

Meter_Number    Date    Time        Kwh
93112575    23/05/2019  08:00:00    278.334
93112575    23/05/2019  09:00:00    279.254
93112575    23/05/2019  10:00:00    280.012
93112575    23/05/2019  11:00:00    281.315
93112575    23/05/2019  12:00:00    282.256
93112575    23/05/2019  13:00:00    285.852
18794160    22/05/2019  14:00:00    6145.504
18794160    22/05/2019  15:00:00    6147.968
18794160    22/05/2019  16:00:00    6150.855
18794160    22/05/2019  17:00:00    6155.283
18794160    22/05/2019  18:00:00    6161.977
18794160    22/05/2019  19:00:00    6162.854
18794160    22/05/2019  20:00:00    6163.644
18794160    22/05/2019  21:00:00    6165.763
18794160    22/05/2019  22:00:00    6169.031
18794160    22/05/2019  23:00:00    6172.302
18794160    23/05/2019  00:00:00    6175.58
18794160    23/05/2019  01:00:00    6178.863
18794160    23/05/2019  02:00:00    6182.146
18794160    23/05/2019  03:00:00    6185.426
18794160    23/05/2019  04:00:00    6188.709
18794160    23/05/2019  05:00:00    6191.994
18794160    23/05/2019  06:00:00    6195.276
18794160    23/05/2019  07:00:00    6197.547
18794160    23/05/2019  08:00:00    6198.336
18794160    23/05/2019  09:00:00    6199.126
18794160    23/05/2019  10:00:00    6200.301
18794160    23/05/2019  11:00:00    6208.569
15296497    22/05/2019  14:00:00    62.064
15296497    22/05/2019  15:00:00    62.095
15296497    22/05/2019  16:00:00    63.044
15296497    22/05/2019  17:00:00    64.062
15296497    22/05/2019  18:00:00    65.024
15296497    22/05/2019  19:00:00    65.085

into this format:

93112575,#1,23/05/2019,08:00:00,278.334,#2,23/05/2019,09:00:00,279.254,#3,23/05/2019,10:00:00,280.012 ~etc.
18794160,#1,22/05/2019,14:00:00,6145.504,#2,22/05/2019,15:00:00,6147.968,#3,22/05/2019,16:00:00,6150.855,#4,22/05/2019,17:00:00,6155.283 ~etc.
15296497,#1,22/05/2019,14:00:00,62.064,#2,15296497,22/05/2019,15:00:00,62.095,#3,22/05/2019,16:00:00,63.044

This is a similar problem posted on your site: R Programming Converting data columns to Rows

Any assistance will be be very much appreciated. I do understand that this is not a simple task and will not be offended if no one has time to assist me in this.

I copied some of the code off one of your pages and added it to an event procedure, it populated the table but it placed all the data in one column.

Options: 1) code you already tried; 2) code writing records to a table; 3) CROSSTAB query, review allenbrowne.com/ser-67.html#MultipleValues. Last option would involve calculating a sequence identifier for each meter/date group. So you are right, not a simple task regardless of approach. — June7
– June7, Commented May 23, 2019 at 20:53
Also review: stackoverflow.com/questions/56017569/…, stackoverflow.com/questions/45898593/… — June7
– June7, Commented May 23, 2019 at 21:19

mazoula · Accepted Answer · 2019-05-26 07:44:23Z

Here is an example of reformatting the data using code:
First I imported the data into an access table:

Then I looped through the table using vba:


Public Sub ReformatData()
'declarations
'Dim rstData2 As DAO.Recordset
'Set rstData2 = CurrentDb.OpenRecordset("tblData2", dbOpenTable)
Dim rstData As DAO.Recordset
Dim currentMeter_Number As Long
Dim counter As Integer
Dim recordstring As String
Set rstData = CurrentDb.OpenRecordset("tblData", dbOpenTable)
'loop through table
If Not rstData.EOF Then
rstData.MoveFirst
Do While Not rstData.EOF
counter = 1
'process first Meter_Number in set
currentMeter_Number = rstData!Meter_Number
recordstring = recordstring & currentMeter_Number & ",#1," & rstData!DateRead & "," & _
Format(rstData!TimeRead, "hh:mm:ss") & "," & rstData!Kwh
rstData.MoveNext
    'process the rest of set
    Do While Not rstData.EOF
    If Not currentMeter_Number = rstData!Meter_Number Then
    Exit Do
    Else
        If Not IsNull(rstData!Meter_Number) Then
        If currentMeter_Number = rstData!Meter_Number Then
        counter = counter + 1
        recordstring = recordstring & ",#" & counter & "," & rstData!DateRead & "," & _
        Format(rstData!TimeRead, "hh:mm:ss") & "," & rstData!Kwh
        rstData.MoveNext
        End If
        End If
        End If
    Loop
recordstring = Left(recordstring, Len(recordstring) - 1) 'remove last comma
Debug.Print recordstring & vbCrLf
'rstData2.AddNew
'rstData2!MeterString = recordstring
'rstData2.Update
recordstring = ""
Loop
End If
End Sub

It is unclear where you want to output the data so I demonstrate outputting to debug and to another already existing table. the table version gives:

sorry about taking so long to post the answer and the swollen vba code. I was dealing with a bug in my version of access.

Collectives™ on Stack Overflow

Converting column data to row data

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related