We help IT Professionals succeed at work.

transpose multiple records into one

LBarrett
LBarrett asked
on
1,097 Views
Last Modified: 2010-04-21
Hi Experts,

I have a data table that looks like this:

MINumber      PL      Calc      Percent
Prod1                B      $16.69      5
Prod1                 K      $14.06      20
Prod1                L      $13.35      24
Prod1                 P      $12.30      30

Prod2             B      $18.69      5
Prod2               K      $16.06      20
Prod2             L      $19.35      28
Prod2                  P      $15.30      32


I would like it to look like this(the headers don't matter I can create the table with the headers I need, I just need to be able to insert the above records to become the records below).  Also this is not something that will only be done once.  The query that created the table below will be run every week so "use Excel to transpose" won't work here.

MINumber   PL1  Calc1      Percent    PL2       Calc2      Percent       PL3      Calc3      Percent       PL4   Calc      Percent
Prod1          B     $16.69   5                K       $14.06      20                 L        $13.35      24                P     $12.30   30
Prod2          B     $18.69   5                K       $16.06      20                 L        $19.35      28                P     $15.30   32

Any help would be appreciated!


Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2016

Commented:
you will need vba codes to do this.

Author

Commented:
I thought as much and tried a few that I saw here but I couldn't find one that was quite what I was looking for.  Can you get me started and maybe even finished with this?  If points are too low for the time needed I will gladly adjust.
CERTIFIED EXPERT
Top Expert 2016

Commented:
place this codes in  module

you have to add to your references
Tools >references
Microsoft DAO x.x object library  { x.x is the version available }  


change tblData with the actual name of table


after running the codes open the created table newTblData
Sub createFlatTable()
Dim rs As DAO.Recordset, j As Integer, i, sPL As String, sFld
Dim rsMax As DAO.Recordset, rsNew As DAO.Recordset, maxProd
Dim rs1 As DAO.Recordset
Set rsMax = CurrentDb.OpenRecordset("select top 1 count(MINumber) from tblData group by MINumber order by count(MINumber) desc")
maxProd = rsMax(0)
For i = 1 To maxProd
    sPL = sPL & "," & "PL" & i & " Text" & "," & "Calc" & i & " Text" & ", " & "Percent" & i & " text"
Next
    sPL = mid(sPL, 2)
    sFld = "MINumber Text,"
If Not IsNull(DLookup("[name]", "msysobjects", "[name]='newTblData'")) Then
    CurrentDb.Execute "drop table newtblData"
End If
CurrentDb.Execute "create table newTblData( " & sFld & sPL & ")"
Set rs = CurrentDb.OpenRecordset("select distinct MINumber from tbldata")
Set rsNew = CurrentDb.OpenRecordset("newtbldata")
rs.MoveFirst
Do Until rs.EOF
    Set rs1 = CurrentDb.OpenRecordset("select * from tblData where MINumber='" & rs!MINumber & "'")
    rsNew.AddNew
    rsNew!MINumber = rs1!MINumber
    j = 1
    Do Until rs1.EOF
        rsNew("PL" & j) = rs1!PL
        rsNew("Calc" & j) = rs1!Calc
        rsNew("Percent" & j) = rs1!Percent
        j = j + 1
        rs1.MoveNext
    Loop
    rsNew.Update
rs.MoveNext
Loop
rs.Close
rs1.Close
rsNew.Close
rsMax.Close
 
End Sub

Open in new window

Author

Commented:
Thanks for the fast response.  Will not be able to verify this completely until tommorow but the small test I was able to do looked perfect.  Will do full test and LYK.

Thank you again.

Kudos capricorn1

Commented:
The only issue I see is that the Calc fields and Percent fields have become text fields.  I need them to remain as currency and percent (or numbers).  I will be using them in other calculations.  Can you help with this?

Author

Commented:
The only issue I see is that the Calc fields and Percent fields have become text fields.  I need them to remain as currency and percent (or numbers).  I will be using them in other calculations.  Can you help with this?
CERTIFIED EXPERT
Top Expert 2016
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
That should do it.  Response was so quick and the solution worked first time around so I increased the points.

Thanks again!

LBarrett

Author

Commented:
That should do it.  Response was so quick and the solution worked first time around so I increased the points.

Thanks again!

LBarrett

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.