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.
Jass SainiAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Walter RitzelSenior Software EngineerCommented:
Please explain further.
My first answer would be Yes, if the field in question does not need to be part of the final dataset.
Jass SainiAuthor Commented:
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

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.
Walter RitzelSenior Software EngineerCommented:
I think I'm not understanding what you mean by transposed.
Can you care to explain?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Jeffrey CoachmanMIS LiasonCommented:
...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
Jass SainiAuthor Commented:
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
Jeffrey CoachmanMIS LiasonCommented:
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
Jass SainiAuthor Commented:
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!!!
Jeffrey CoachmanMIS LiasonCommented:
oK, Thanks
I can get the number of records the same as yous with that grouping...:
SELECT Final_Table.[Org Name], Final_Table.CostCen, Final_Table.PEC
FROM Final_Table
GROUP BY Final_Table.[Org Name], Final_Table.CostCen, Final_Table.PEC;


Unfortunately, I cannot help with the rest, ...so lets wait and see what other experts may contribute.

JefCoachman

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.