Solved

Access Table Transpose Column Name as first column

Posted on 2011-09-15
9
450 Views
Last Modified: 2012-05-12
Hi,

I found some transposer VBA that allows me to transpose my table (http://support.microsoft.com/kb/182822).  The transposer puts the row number into the header of each column.  I would like the first column into headers.  Anything thoughts?

Here is the current transposed table
From  (Headers Bolded)

Column1    Column2
Name         Bob
Phone        111-111-1111

To (Headers Bolded)
1               2            3
Column1   Name     Phone
Column2   Bob        111-111-1111


I would like it to look like this (Headers Bolded):

Column1   Name     Phone
Column2   Bob        111-111-1111
0
Comment
Question by:atljarman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36543233
0
 

Author Comment

by:atljarman
ID: 36543647
capricorn1,

Their main field is Month and my table is Field.  To reflect this, I've changed part of the code to:


sFld = "[Field] Text"
Do Until rstSource.EOF
    sFld = sFld & "," & "[" & rstSource!field & "] Number"
rstSource.MoveNext
Loop

I get an error "3421 Data type conversion error."   I've deleted rows, columns based on data type, and haven't found where the issue is coming from.

Do you know where there error is coming from.


--------------------------------------------------

table structure:

Field   Current
ID        1
Date    11/1/2010
Name   Bob


---------------------------------------------------

Function Transposer2(strSource As String, strTarget As String)

        If TableExists(strTarget) Then
            CurrentDb.TableDefs.Delete (strTarget)
        End If
       
       
   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
   
Dim sFld
   On Error GoTo Transposer_Err

   Set db = CurrentDb()
   Set rstSource = db.OpenRecordset(strSource)
        '   rstSource.MoveLast

           ' Create a new table to hold the transposed data.
           ' Create a field for each record in the original table.
        '   Set tdfNewDef = db.CreateTableDef(strTarget)
        '   For i = 0 To rstSource.RecordCount
        '      Set fldNewField = tdfNewDef.CreateField(CStr("[" & rstSource(0) & "]"), dbText)
        '      tdfNewDef.Fields.Append fldNewField
        '   Next i
        '   db.TableDefs.Append tdfNewDef

sFld = "[Field] Text"
Do Until rstSource.EOF
    sFld = sFld & "," & "[" & rstSource!field & "] Number"
rstSource.MoveNext
Loop
If DCount("*", "msysobjects", "name='" & strTarget & "'") > 0 Then
    CurrentDb.Execute "drop table " & strTarget
End If

'create the target table
CurrentDb.Execute "create table " & strTarget & "(" & sFld & ")"

   ' Open the new table and fill the first field with
   ' field names from the original table.
   Set rstTarget = db.OpenRecordset(strTarget)
   'For i = 0 To rstSource.Fields.Count - 1
   For i = 1 To rstSource.Fields.Count - 1
      With rstTarget
        .AddNew
        .Fields(0) = rstSource.Fields(i).name
        .Update
      End With
   Next i

   rstSource.MoveFirst
   rstTarget.MoveFirst
   ' Fill each column of the new table
   ' with a record from the original table.
   For j = 0 To rstSource.Fields.Count - 1
      ' Begin with the second field, because the first field
      ' already contains the field names.
      For i = 1 To rstTarget.Fields.Count - 1
         With rstTarget
            .Edit
            .Fields(i) = rstSource.Fields(j)
            rstSource.MoveNext
            .Update
         End With

      Next i
      rstSource.MoveFirst
      rstTarget.MoveNext
   Next j

   db.Close

   Exit Function

Transposer_Err:

   Select Case Err
      Case 3010
         MsgBox "The table " & strTarget & " already exists."
      Case 3078
         MsgBox "The table " & strSource & " doesn't exist."
      Case Else
         MsgBox CStr(Err) & " " & Err.Description
   End Select

   Exit Function
End Function
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36543696
first of all,  do not use "Field" as a name of an object, this is an Access reserved word.

upload a  .MDB version of your db
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:atljarman
ID: 36544209
Here is a sample file with the required code.   I keep getting the same error when stripping out the other database objects.  I renamed Field to Field1 with the same error.
db1.mdb
0
 

Author Comment

by:atljarman
ID: 36544250
FYI - The form is hidden with the button that calls the conversion.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36544255
post an excel file, showing exactly how you want the table transposed, use the values from the table.
0
 

Author Comment

by:atljarman
ID: 36544348
The code as is works, ha ha, execept it does not include the data.
file.xls
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 36544359
test this


db1-rev.mdb
0
 

Author Closing Comment

by:atljarman
ID: 36544471
Excellent.  Capricorn1's suggestion worked like a charm.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question