UnPivot columns with column data and column names in Access/VBA (1 Viewer)

JJNero · Nov 12, 2013

Hello gurus, I have been struggling for the past three days with this. I really need this to be done.
I need my columns with data un-pivoted and also have a title column adjacent populated with “column names” for data reference.
I did achieve a bit of it but the Do loop is not working for me. Below is my test case.

This is de-normalized source table:
Entry form:

Code:

id	Fname	Lname	Cars	Bikes	Vans	Count
1	James	Philman	  2	  0	  1	  3
2	Robert	Kinsey	  1	  1	  2	  4

This is what my results should look like:
Result should be

Code:

id	Title	Count
1	Cars	  2
1	Bikes	  0
1	Vans	  1
2	Cars	  1
2	Bikes	  1
2	Vans	  2

Things to note:
1. In the result set some of the columns are skipped during pivot like Fname, LName, TotalCount.
2. I also need the item count to be “Numeric field”. When I change it in my table I get an error during run time.
3. The above source table is de-normalized and done this way to meet a certain business need.
4. The id repeats with every represented data column. Like in my above source have 2 (id) but result set shows 3 (id) rows. This is correct because 3 columns have the related data (NumOfCars, NumOfBikes, NumOfVans). Even a null or a default zero.
5. The bottom table shows the result from my code. Think I got the column data. Column names are problem. The column names should show to their related id and data. I my case (NumOfCars, NumOfBikes, NumOfVans) twice for each unique id.

This is what I got so far:

Code:

id	Title	Count
 	id	 
 	FName	 
 	Lname	 
 	Cars	 
 	Bikes	 
 	Vans	 
 	Count	 
 	id	 
 	FName	 
 	Lname	 
 	Cars	 
 	Bikes	 
 	Vans	 
 	Count	 
1	 	2
1	 	0
1	 	1
2	 	1
2	 	1
2	 	2

Code:

Private Sub Command1_Click()
    Dim db As DAO.Database
    Dim sqlStr1 As String
    Dim rs2 As DAO.TableDef
    
    DoCmd.SetWarnings False
    
    Set db = Currentdb
    
    sqlStr1 = "SELECT * FROM tblData"
    Set rs1 = db.OpenRecordset(sqlStr1)
    
    Set rs2 = db.TableDefs("tblData")
    
        Do While Not rs1.EOF = True
            For i = 3 To rs1.Fields.Count - 2

                CurrentProject.Connection.Execute "INSERT Into tblResult (id,ItemCount) Values('" & (rs1!id) & "','" & (rs1(i - l)) & "') "
                Next
         
                For Each p In rs2.Fields
                CurrentProject.Connection.Execute "INSERT Into tblResult (Title) Values('" & (p.Name) & "') "
               
                Next
                 rs1.MoveNext
                Loop


        Set rs1 = Nothing
        Set rs2 = Nothing
        
    DoCmd.SetWarnings True
End Sub

I really appreciate any help I can get with this. Thanks in advance.

JHB · Nov 12, 2013

Change your code to the below, (remember to use your table names and field names).

Code:

  Dim dbs As Database, rst As Recordset, x As Integer
  
  Set dbs = CurrentDb
  
  Set rst = dbs.OpenRecordset("SELECT * FROM tblData")
  
  If Not rst.EOF Then
    Do
      For x = 3 To rst.Fields.Count - 2
        dbs.Execute "INSERT Into tblResult (id, Titel, Count) " _
        & "Values (" & rst![Id] & ", '" & rst(x).Name & "', " & rst(rst(x).Name) & ")"
      Next x
      rst.MoveNext
    Loop Until rst.EOF
  End If

JJNero · Nov 18, 2013

JHB Thanks a lot. The code did work great.
One more thing can I pass the table name from the database.
Tables I need start with name P1_TableName1, P1_TableName2 and so on.
Can we loop through all tables to find any table name starting with P1 and pass the name to a variable? Till all P1's are done? From there the code you gave me is executed. In the below code "MyName" is the variable.

Here:
Dim db As DAO.Database, rst As Recordset, x As Integer, MyData As String
MyData = MyName
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT * FROM [" & MyData & "]")
If Not rst.EOF Then
Do
For x = 3 To rst.Fields.count - 1
dbs.Execute "INSERT Into tbl_CollectionMain (EmpId, Title, Count) " _
& "Values (" & rst![EmpId] & ", '" & rst(x).Name & "', " & rst(rst(x).Name) & ")"
Next x
rst.MoveNext
Loop Until rst.EOF
End If

Once again thanks a lot for your help JBH.

JHB · Nov 19, 2013

Please use code tags when you post code, it is easier to read:
Something like below, (it is not tested)

Code:

  Dim dbs As Database, ctr As Container, doc As Document
  
  Set dbs = CurrentDb
  Set ctr = dbs.Containers!Tables
  For Each doc In ctr.Documents
    If UCase(Left(doc.Name, 3)) = UCase("P1_") Then
      'your former code goes in here    
    End If
  Next doc
  dbs.Close
  Set dbs = Nothing

To avoid your self for problem with undeclared variables use the:
Option explicit
in your database.

JJNero · Nov 19, 2013

Worked!!! Too good! Just the way I wanted. Thanks a lot again. You are awesome.

JJNero · Nov 24, 2013

JHB, I keep getting this
"Type mismatch (Run-time error Error '13')" all of a sudden.

Stops at this line of code

Code:

Set rst = dbs.OpenRecordset("SELECT * FROM [" & MyData & "]")

I can see the tables name when I have the pointer on "MyData". But when I have the pointer on rst it says rst=Nothing. Can you help with this.

Thanks for your help in advance.

JHB · Nov 25, 2013

Post the hole code, please use code tags.
Try a "Compact and Repair"!
Or the best post a stripped version of you database with some sample data, (zip it when you don't have post 10 post).

UnPivot columns with column data and column names in Access/VBA (1 Viewer)

JJNero

New member

JHB

Have been here a while

JJNero

New member

JHB

Have been here a while

JJNero

New member

JJNero

New member

JHB

Have been here a while

Similar threads

Users who are viewing this thread