Link to home
Start Free TrialLog in
Avatar of Jass Saini
Jass Saini

asked on

Is it possible to do a transpose with duplicate records?

Is it possible to do a transpose table with duplicate records there is one field that is different...Please let me know if you need more info.
Avatar of Walter Ritzel
Walter Ritzel
Flag of Brazil image

Please explain further.
My first answer would be Yes, if the field in question does not need to be part of the final dataset.
Avatar of Jass Saini
Jass Saini


So I had to copy every record nine times for nine different "Budget" changes.  In my orginal transpose table...I only have it is the code for that.

ub Transposer()

Dim db As DAO.Database
Dim tdfNewDef As DAO.TableDef
Dim fldNewField As DAO.field
Dim rstSource As DAO.Recordset, rstTarget As DAO.Recordset
Dim i As Integer, j As Integer, k As Integer

Set db = CurrentDb()

db.Execute "delete from tblFinal_Transposed where Change <> 'Initial'"

For k = 11 To 38 Step 3
    Call AddRecord(k)
Next k

End Sub

Private Sub AddRecord(field As Integer)

Set db = CurrentDb()
Set rstSource = db.OpenRecordset("Final_Table")
Set rstTarget = db.OpenRecordset("tblFinal_Transposed")



For j = 0 To rstSource.RecordCount - 1
        If rstSource.Fields(field) <> 0 Then
            rstTarget.Fields(0) = rstSource.Fields(1)
            rstTarget.Fields(1) = rstSource.Fields(2)
            rstTarget.Fields(2) = rstSource.Fields(3)
            rstTarget.Fields(3) = rstSource.Fields(4)
            rstTarget.Fields(4) = rstSource.Fields(5)
            rstTarget.Fields(5) = rstSource.Fields(field).Name
            rstTarget.Fields(6) = rstSource.Fields(field + 1)
            rstTarget.Fields(7) = rstSource.Fields(field + 2)
                For i = 8 To rstTarget.Fields.Count - 1
                    If rstTarget.Fields(i).Name = rstSource.Fields(6) Then
                        rstTarget.Fields(i) = rstSource.Fields(field)
                   Else: rstTarget.Fields(i) = 0
                    End If
                Next i
        End If
Next j

End Sub

Open in new window

Now I have added a colunm with the nine "budget" changes....which I had to copy each record nine times.  But now the transpose is not doing anything.
I think I'm not understanding what you mean by transposed.
Can you care to explain?
...and it would be helpful if you posted a screenshot of what you have now.
Then post a clear graphical example of the exact output you need...

I need to take Final_Table and make it look like tblFinal_Transposed....with the addition of BC_Change (column) incorporated.  The code above is with the BC_Change.
Still confused...
First, what you show in  tblFinal_Transposed is not a true transposition (with the rows and columns swapped), looks more like a "summary" (Group By)
Second, it is not clear how you go from 4130 records for Avenal State Prison, in Final_Table, just 17 records in tblFinal_Transposed.
I tried grouping by, what I can only presume are possibly the duplicate fields of: Line Item and Line Number, ..and I ended up with 27 record for Avenal State Prison
SELECT Final_Table.[Org Name], Final_Table.[Line Item], Final_Table.[Item Number]
FROM Final_Table
GROUP BY Final_Table.[Org Name], Final_Table.[Line Item], Final_Table.[Item Number];


I don't understand it either..someone else in the Unit did this coding... It's not a true transpose.

The 4130 going to 17 records is because it is group by OrgName and PEC..with the Line Items as colunm headers.

I added a colunm of the different budget changes that needs to be incorporated some how. I am not sure how to do this and the person who wrote the code refuses to help!!!
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial