Is it possible to do a transpose with duplicate records?

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

So I had to copy every record nine times for nine different "Budget" changes.  In my orginal transpose table...I only have it once...here 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
  
db.Close

End Sub

Private Sub AddRecord(field As Integer)

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

rstSource.MoveLast
rstTarget.MoveLast

rstSource.MoveFirst

For j = 0 To rstSource.RecordCount - 1
        If rstSource.Fields(field) <> 0 Then
            rstTarget.AddNew
            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
            rstTarget.Update
        End If
    rstSource.MoveNext
Next j

db.Close
End Sub

Select allOpen 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...

JeffCoachman

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.
Database16--2-.zip

Still confused...
First, what you show in  tblFinal_Transposed is not a true transposition (with the rows and columns swapped), ...it 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, ...to 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];

JeffCoachman

Jeff..

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!!!