Transposing data from row into column

[DBS1]

I have imported a table in which some the data looks like this...

ID Field1 Field2 Field3
1 AL011851 UNNAMED 14
2 18510625 0000
3 18510625 0600
4 18510625 1200
5 18510625 1800

There are more fields but are not relavent for this question so I didn't show them, and there are over 50k records. The data in the row of ID 1 is unique to records 2-15, then another record similar to record 1 exists (ID 16). Field3 specifies how many records are related to the record in ID 1. In other words, the value in Field3 says that the next 14 records are related to ID 1. I'm trying to figure out how to relate the data in records 2-15 to record 1. The idea I had in mind was to somehow add two new fields, in which AL011851 and UNNAMED are part of the records for 2-15, but not sure how to do that or if there is a better way. The number in Field 3 varies. Open to ideas. Thanks.

[June7]

I would write a VBA procedure looping through a recordset to update two new fields in table. I've responded to similar questions with this approach as solution. Give me an hour or so.

[June7]

Something like this:

Sub FixTable()
Dim rs As DAO.Recordset, strF1 As String, strF2 As String, x As Integer
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Table1 ORDER BY ID")
Do While Not rs.EOF
    strF1 = rs!Field1
    strF2 = rs!Field2
    For x = 1 To rs!Field3 + 1
        rs.Edit
        rs!Field1New = strF1
        rs!Field2New = strF2
        rs.Update
        rs.MoveNext
    Next
Loop
End Sub

[CJ_London]

pretty sure you can do this with a query - not enough data to be sure

SELECT tblT.Field1, tblT.field2, tblT_1.ID, tblT_1.Field1, tblT_1.field2
FROM tblT, tblT AS tblT_1
WHERE (((tblT_1.ID)>[tblT].[ID] And (tblT_1.ID)<=[tblT].[ID]+[tblT].[field3]) AND ((tblT.field3) Is Not Null))

produces this result from the data provided

tblT.Field1	tblT.field2	ID	tblT_1.Field1	tblT_1.field2
AL011851	UNNAMED	2	18510625	0000
AL011851	UNNAMED	3	18510625	0600
AL011851	UNNAMED	4	18510625	1200
AL011851	UNNAMED	5	18510625	1800

It relies on the ID field being consecutive, if it isn't you would need to add a counter field. You can change the order of the fields so the ID field appears to the left

Although for normalisation purposes I would expect two tables, one containing the first record and the other to contain records 2-15 with a FK to relate back to the other table

[June7]

Nice query. I wonder how fast that Cartesian product will run on 50K records.

The output does not show records 1 and 16.

Agree should probably be two tables. Wonder what data is in other fields.

[CJ_London]

The output does not show records 1 and 16.

record one is shown in the first two fields and OP only provided 5 records so did not 'complete' the full set and there is no record 16.

with regards speed, providing the ID field is indexed should not be a problem but could probably be improved by using a non standard join (which can't be shown in the QBE)

SELECT tblT.Field1, tblT.field2, tblT_1.ID, tblT_1.Field1, tblT_1.field2
FROM tblT INNER JOIN tblT AS tblT_1  ON tblT_1.ID>[tblT].[ID] And tblT_1.ID<=[tblT].[ID]+[tblT].[field3]
WHERE tblT.field3 Is Not Null

[June7]

I tested with expanded dataset of 20 records so there is a record 16 per OP description.

OP might want to show other fields for the 'parent' records.

[CJ_London]

We’ll wait for the OP’s response- there is an assumption that the ID is consecutive

[NoellaG]

Add two fields: MotherID and Done. Set Done to false in every record
Write a procedure that:

Select top(1) ID, Field3 from myTable order by ID where done = 0
will give you the ID of the first line and the number of records it is used in, put the ID in a variable @MotherID, the number of records in @NumberRecs
Then update the first line and set the field done to true
update myTable set done = 1 where ID = @MotherID

Then update the top @Numberrecs
update MyTable set MotherID = @MotherID, Done = 1 where ID in(select top(@NumberRecs) from MyTable order by ID) and Done = 0

Repeat this for all following groups

[alansidman]

an alternative is to export the data to Power Query and rearrange it and then import back to Access or export to Native Excel. Here is the Power Query Mcode

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Removed Columns" = Table.RemoveColumns(Source,{"Field3"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"ID", Int64.Type}, {"Field1", type text}, {"Field2", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains([Field1], "AL0") then [Field1] else null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if Text.StartsWith([Field2],"UNN") then [Field2] else null),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom1",{"Custom", "Custom.1", "ID", "Field1", "Field2"}),
    #"Filled Down" = Table.FillDown(#"Reordered Columns",{"Custom", "Custom.1"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Field1] = "18510625"))
in
    #"Filtered Rows"

[DBS1]

Good morning everyone. Thanks for your quick replies. I've been on vacation since I posted this and have to admit I was posting in haste on the way out the door, so I apologize for missing some of the data. I've tried the vba example that June7 provided and the query that CJ London provided (see below). But first, a little more background on the data...this is imported from a txt file, which is storm data from the weather service. There are over 55k records, there are two types of data in the rows (records).Starting with record 1, Field1 is the storm number and year, Field2 is storm name, and Field3 is the number of following rows that are related to that storm. Record 2 through 15 are details of that particular storm. In those records, Field1 is the date (yyyymmdd), Field2 is the hour, Field3 may have a certain code, but most fields are null, Field4 is category, and remaining fields are data like coordinates, and other data specific to the storm for that specific date and time. Also, table name is Hurdat2. Raw data looks like this...

[DBS1]

Not sure what happened with the attachments above. The first attachment shows the table, and associated with my reply. The next three images are screenshots of my attempts to use June7 and CJ London's advice. First two are the errors that displayed with the vba code from June 7 and the last one is the error I get with CJ London's query. Here is the query I tried...

SELECT Hurdat2.Field1 AS Expr1, Hurdat2.field2 AS Expr2, Hurdat2_1.ID AS Expr3, Hurdat2_1Field1 AS Expr4, Hurdat2_1.field2 AS Expr5
FROM Hurdat2, Hurdat2 AS Hurdat2_1
WHERE (((Hurdat2_1.ID)>[Hurdat2].[ID] And (Hurdat2_1.ID)<=[Hurdat2].[ID]+[Hurdat2].[Field3]) AND ((Hurdat2.field3) Is Not Null));

If I remove And (Hurdat2_1.ID)<=[Hurdat2].[ID]+[Hurdat2].[Field3]) the query works, but get 89 million records.

[Welshgasman]

So where are the Field1New and 2 fields?, you are not showing those?

[CJ_London]

Not sure we can trust the sql you have posted, or the source you are displaying is not the source you are referencing in the query -Hurdat2_1Field1 should be Hurdat2_1.Field1 otherwise will generate an error, Hurdat2_1.field2 would have appeared as Hurdat2_1.Field2 - and all those 'AS Expr' implies you have not used the query builder - or if you have they are somehow calculated


And given we are now seeing a more realistic set of data, you need to remove

AND ((Hurdat2.field3) Is Not Null)

from the criteria since you have values in that field for ID=6 for example.

More importantly, that means that field3 is text, not numeric so the bit of the criteria you removed needs to be replaced with

(Hurdat2_1.ID)<=[Hurdat2].[ID]+val([Hurdat2].[Field3]))

That is probably the reason you had a problem with the other method as well.

shame you didn't include row 6 in your original example data, wasted a lot of time

[Micron]

AFAIK, you cannot rs!variableNameHere because of how the bang operator works. You'd have to use rs.fields(variableNameHere)? However, what's the point of assigning the field name to a variable when you know the field name? Might as well just use rs!Field1 if that is the field name.
IIRC, someone suggested that "fields" is not required with that syntax but I've always invoked it and won't ever change. That's how shortcuts and sloppy code can stop working after Access updates.