Transpose data from form into table

[accessnewb24]

I am designing a form to use for our production database. This will be replacing our current build form which currently is just paper copies. The problem I am having is this form will need to have data entered horizontally and there isn't really any way I can think of to change that.

Is there a way for me to have this data entered into a horizontal table then added into the 'real' vertical table? System ID and Cell # will already be in the table by this step, the operators just need to assign the proper part # to the cell number which will be done as they build.

SystemID---A1---A1---A1
Cell#--------1-----2-----3
Part#------a-----b-----c


SystemID-----Cell#------Part#
A1---------------1-----------a
A1---------------2-----------b


A1---------------3-----------c

[alansidman]

In Excel, load the Table/Query into Power Query. Run the Mcode below to transform the data.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Transposed Table" = Table.Transpose(Source),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Cell#", Int64.Type}, {"Part#", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "System ID", each "A1"),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"System ID", "Cell#", "Part#"})
in
    #"Reordered Columns"

Close and Load data back to Excel. Reload to your Access Table.

[accessnewb24]

Thanks for the reply! I was hoping to keep this within Access and have it be done at the click of a button

[Bob Fitz]

How many entries of Cell# and Part# will there be for each SystemID
Edit:

Is there a way for me to have this data entered into a horizontal table then added into the 'real' vertical table? System ID and Cell # will already be in the table by this step, the operators just need to assign the proper part # to the cell number which will be done as they build.

So why does the data entry have to be horizontal and not vertical.

[alansidman]

Thanks for the reply! I was hoping to keep this within Access and have it be done at the click of a button

Is this a one time event? If so, what difference does it make if this is how you need the data?