Results 1 to 3 of 3
  1. #1
    KP_SoCal is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    5

    Question Transpose specific records to table (VBA)

    Below is a very handy function that transposes fields from "Table1" into "Table2". Note, in Table1 the Field2, Field3, etc represent Option codes.

    Basically it takes this...
    Table1
    ProdBreakDown --Field2----- Field3-----Field4
    Widget1------------1253--------3843-------3986
    Widget2------------1130--------1234-------3843
    Widget3------------1234--------5698-------4207

    And transposes it into this...
    Table2
    ProdTarget--OptionCodes
    Widget1--------1253
    Widget1--------3843
    Widget1--------3986
    Widget2--------1234
    Widget2--------1253
    Widget2--------3843
    Widget3--------1234
    Widget3--------5698
    Widget3--------4207

    To see this in action. Look at my attached database.


    MY QUESTION: Instead of transposing ALL records, I only want to transpose records from that contain specific option codes. For instance, the criteria would restrict transposed records to be where only Option Codes from Table1 that
    have a string that begins with '12' OR a full string that equals '3843' are transposed.



    For a small set of records, I could easily query this from the total results of Table2 after my function has been ran by using a simple "OR" statement in SQL. The problem is, my real data consists of over 70,000 records. So transposing all the data results in millions of records being created which over inflates my database and causes it to corrupt. That's why I need the restrict to specific criteria when transposing it into Table2 to avoid creating all the addition unnecessary records.

    I hope this explanation is helpful. Any feedback is much appreciated!

    KP

    Code:
    Option Compare Database
    Option Explicit
    
    Public Sub TransRecords()
    Call TransposeRecordset("Table1", "Table2", "ProdBreakDown")
    MsgBox "Table2 updated!", vbExclamation + vbOKOnly
    End Sub
    
    Private Function TransposeRecordset(pstrrecoriginal As String, pstrrecnew As String, pstrkey As String)
    
        Dim db          As Database
        Dim recorg      As Recordset
        Dim recnew      As Recordset
        Dim intCount    As Integer
        Dim varkeyvalue As Variant
        Dim bolfound    As Boolean
        
        Set db = CurrentDb()
        
        Set recorg = db.OpenRecordset("select * from [" & pstrrecoriginal & "]")
        Set recnew = db.OpenRecordset("select * from [" & pstrrecnew & "]")
        
        'Loop through records in recorginal
        While Not recorg.EOF
                        
            intCount = 0
            bolfound = False
            
            'Loop through fields in recorginal looking for key
            While intCount <= recorg.Fields.Count - 1 And bolfound = False
            
                If recorg(intCount).Name = pstrkey Then
                
                    varkeyvalue = recorg(intCount)
                    bolfound = True
                    DoCmd.Echo True, "Transposing " & varkeyvalue
                End If
                
                intCount = intCount + 1
                
            Wend
            
            For intCount = 0 To recorg.Fields.Count - 1
            
                'skip key field
                If recorg(intCount).Name <> pstrkey Then
                    recnew.AddNew
                    recnew(0) = varkeyvalue
                    
                    recnew(1) = Nz(recorg(intCount).Value, "")
                    recnew.Update
                End If
                    
                    
            Next
        
            recorg.MoveNext
            
        Wend
        DoCmd.Echo True, ""
    End Function

  2. #2
    SoftwareMatters is offline Access VBA Developers
    Windows XP Access 2003
    Join Date
    Mar 2009
    Location
    Dorset
    Posts
    274
    To do this you just need to filter the recordset. So, to import all with OptionCode that equals 3843 you would put something ike this:

    Set recorg = db.OpenRecordset("select * from [" & pstrrecoriginal & "] WHERE OptionCode = 3843")

  3. #3
    DerekCAL is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2013
    Posts
    1

    Transpose

    Hi - How do I run this code? I created a macro that runs the function name TransRecords() but get the following error:

    "the expression you entered has a function name that microsoft can't find"



    Quote Originally Posted by KP_SoCal View Post
    Below is a very handy function that transposes fields from "Table1" into "Table2". Note, in Table1 the Field2, Field3, etc represent Option codes.

    Basically it takes this...
    Table1
    ProdBreakDown --Field2----- Field3-----Field4
    Widget1------------1253--------3843-------3986
    Widget2------------1130--------1234-------3843
    Widget3------------1234--------5698-------4207

    And transposes it into this...
    Table2
    ProdTarget--OptionCodes
    Widget1--------1253
    Widget1--------3843
    Widget1--------3986
    Widget2--------1234
    Widget2--------1253
    Widget2--------3843
    Widget3--------1234
    Widget3--------5698
    Widget3--------4207

    To see this in action. Look at my attached database.


    MY QUESTION: Instead of transposing ALL records, I only want to transpose records from that contain specific option codes. For instance, the criteria would restrict transposed records to be where only Option Codes from Table1 that
    have a string that begins with '12' OR a full string that equals '3843' are transposed.

    For a small set of records, I could easily query this from the total results of Table2 after my function has been ran by using a simple "OR" statement in SQL. The problem is, my real data consists of over 70,000 records. So transposing all the data results in millions of records being created which over inflates my database and causes it to corrupt. That's why I need the restrict to specific criteria when transposing it into Table2 to avoid creating all the addition unnecessary records.

    I hope this explanation is helpful. Any feedback is much appreciated!

    KP

    Code:
    Option Compare Database
    Option Explicit
    
    Public Sub TransRecords()
    Call TransposeRecordset("Table1", "Table2", "ProdBreakDown")
    MsgBox "Table2 updated!", vbExclamation + vbOKOnly
    End Sub
    
    Private Function TransposeRecordset(pstrrecoriginal As String, pstrrecnew As String, pstrkey As String)
    
        Dim db          As Database
        Dim recorg      As Recordset
        Dim recnew      As Recordset
        Dim intCount    As Integer
        Dim varkeyvalue As Variant
        Dim bolfound    As Boolean
        
        Set db = CurrentDb()
        
        Set recorg = db.OpenRecordset("select * from [" & pstrrecoriginal & "]")
        Set recnew = db.OpenRecordset("select * from [" & pstrrecnew & "]")
        
        'Loop through records in recorginal
        While Not recorg.EOF
                        
            intCount = 0
            bolfound = False
            
            'Loop through fields in recorginal looking for key
            While intCount <= recorg.Fields.Count - 1 And bolfound = False
            
                If recorg(intCount).Name = pstrkey Then
                
                    varkeyvalue = recorg(intCount)
                    bolfound = True
                    DoCmd.Echo True, "Transposing " & varkeyvalue
                End If
                
                intCount = intCount + 1
                
            Wend
            
            For intCount = 0 To recorg.Fields.Count - 1
            
                'skip key field
                If recorg(intCount).Name <> pstrkey Then
                    recnew.AddNew
                    recnew(0) = varkeyvalue
                    
                    recnew(1) = Nz(recorg(intCount).Value, "")
                    recnew.Update
                End If
                    
                    
            Next
        
            recorg.MoveNext
            
        Wend
        DoCmd.Echo True, ""
    End Function

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 0
    Last Post: 02-24-2010, 12:56 AM
  2. Replies: 15
    Last Post: 02-16-2010, 10:58 AM
  3. Replies: 2
    Last Post: 07-15-2009, 04:08 AM
  4. Enumerating records in a table
    By EricMK in forum Programming
    Replies: 5
    Last Post: 07-08-2009, 07:40 AM
  5. Query two different records in the same table
    By Eildydar in forum Access
    Replies: 5
    Last Post: 04-15-2009, 01:39 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums