Transpose Data and field from Horzantial to vertfical

ahmed shahrani
ahmed shahrani used Ask the Experts™
on
hi,
I would like to know if this is possible.
I have a data table arranged this way:

PRNo   Stage1 stage2  stage3  stage4.....stage9
1             3         0            0          0         …..    0

I would like to possibly run a query (or something) so that the table above becomes:

PRNo  Stage   Status
1          1              3
1          2              0
1          3              0
1          4              0
and so on...

I have used below code but it is for 2 field only, any help please?
also if possible when run the code every time, i need to update the existing record not to duplicate it, is it possible?
Thanks in advance.
Sub TransposeType()
Dim rs As DAO.Recordset, rs1 As DAO.Recordset
Dim i As Integer, s, fldArr()

Set rs = CurrentDb.OpenRecordset("StagesQ")  'change the name to the actual name of table
Set rs1 = CurrentDb.OpenRecordset("Invoices")

If rs.EOF Or rs.BOF Then
    MsgBox "no records"
    Exit Sub
End If
rs.MoveFirst
    For i = 0 To rs.Fields.Count - 1
        ReDim Preserve fldArr(i)
        fldArr(i) = rs.Fields(i).Name
    Next
Dim j
Do Until rs.EOF
    For j = 1 To UBound(fldArr)
            With rs1
                .AddNew
                !PRNo = rs("PRNo")
                !Discerption = rs.Fields(fldArr(j))
                .Update
               
            End With
    Next
    rs.MoveNext
Loop
End Sub
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You should add to resulting  table complex index (PRNo and Stage) to preserve duplicates and use this code:
Dim rs As DAO.Recordset, rs1 As DAO.Recordset
Dim i As Integer, s, fldArr()

Set rs = CurrentDb.OpenRecordset("StagesQ")  'change the name to the actual name of table
Set rs1 = CurrentDb.OpenRecordset("Invoices")

If rs.EOF Or rs.BOF Then
    MsgBox "no records"
    Exit Sub
End If
On Error Resume Next
rs.MoveFirst
Do Until rs.EOF
  For i = 1 To rs.Fields.Count - 1
    With rs1
        .AddNew
        !PRNo = rs("PRNo")
        !Stage = Right(rs.Fields(i).Name, 1)     'translate stage column name to number
        !Status = rs.Fields(i)
        .Update
    End With
  Next i
  rs.MoveNext
Loop
On Error GoTo 0

Open in new window

DB29163642.accdb
Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
Use a Union query:

PRNo   Stage1 stage2  stage3  stage4.....stage9
1             3         0            0          0         …..    0

SELECT PRNo, "1" as Stage, [Stage1] as Status FROM yourTable WHERE [Stage1] IS NOT NULL
UNION ALL
SELECT PRNo, "2" as Stage, [Stage2] as Status FROM yourTable WHERE [Stage2] IS NOT NULL
UNION ALL
...
UNION ALL
SELECT PRNo, "9" as Stage, [Stage9] as Status FROM yourTable WHERE [Stage9] IS NOT NULL

Author

Commented:
many thanks to all
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
glad I could help.
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
The other ways to accomplish that is either a Crosstab Query, or if you need the data in a single field, use of a custom function called DConcat():

https://www.experts-exchange.com/articles/2380/Domain-Aggregate-for-Concatenating-Values-by-Group-in-Microsoft-Access.html

Jim.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial