Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2012
    Posts
    9

    Red face Unanswered: VB script convert crosstab data to columns

    Hi,

    This is probably stupid but i am new to VB code

    I am trying to convert data in cross tab format to a list in columns.

    The input data is:

    Site AA BB CC
    1 6.930 0.164 0.110
    2 6.920 0.410 0.510
    3 6.720 0.591 0.010


    I need it to look like:
    Site Parameter Value
    1 AA 6.930
    1 BB 0.164
    1 CC 0.110
    2 AA 6.920
    2 BB 0.410
    2 CC 0.510
    3 AA 6.720
    3 BB 0.591
    3 CC 0.010

    The code i found seems to work, but i have to specify the "AA" "BB" values in the output table, i would like the script to take the column headings from the input table and use those automatically.

    Any help would be appreciated, thanks

    The code is as follows:
    Private Sub Command3_Click()
    Dim db As DAO.Database
    Dim tblINPUT As DAO.Recordset
    Dim tblOUTPUT As DAO.Recordset
    Dim sqlcode As String

    Set db = CurrentDb()

    Set tblINPUT = db.OpenRecordset("input")
    Set tblOUTPUT = db.OpenRecordset("output", dbOpenDynaset)

    DoCmd.SetWarnings False
    sqlcode = "DELETE output.* FROM output"
    DoCmd****nSQL sqlcode
    DoCmd.SetWarnings True

    With tblINPUT
    If .RecordCount > 0 Then
    .MoveLast
    Do Until .BOF


    tblOUTPUT.AddNew
    tblOUTPUT("Site") = ![SiteName]
    tblOUTPUT("Parameter") = "AA"
    tblOUTPUT("Value") = ![AA]
    tblOUTPUT.Update


    tblOUTPUT.AddNew
    tblOUTPUT("Site") = ![SiteName]
    tblOUTPUT("Parameter") = "BB"
    tblOUTPUT("Value") = ![BB]
    tblOUTPUT.Update

    .MovePrevious


    Loop ' looping to bof

    End If

    End With
    End Sub

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    This is completly untested as I dont have the data to try it on, but maybe this does what you require (or something similar)?

    Code:
    Private Sub Command3_Click()
        Dim db As DAO.Database
        Dim tblINPUT As DAO.Recordset
        Dim tblOUTPUT As DAO.Recordset
        Dim sqlcode As String
    
        Set db = CurrentDb()
        
        Set tblINPUT = db.OpenRecordset("input")
        Set tblOUTPUT = db.OpenRecordset("output", dbOpenDynaset)
        
        DoCmd.SetWarnings False
        sqlcode = "DELETE output.* FROM output"
        DoCmd****nSQL sqlcode
        DoCmd.SetWarnings True
        
        With tblINPUT
            Do Until .EOF
                Dim j As Integer
                For j = 1 To .Fields.Count - 1
                    tblOUTPUT.AddNew
                    tblOUTPUT("Site") = .Fields(0)
                    tblOUTPUT("Parameter") = .Fields(j).Name
                    tblOUTPUT("Value") = .Fields(j)
                    tblOUTPUT.Update
                Next j
                .MoveNext
            Loop
        End With
    End Sub

    MTB

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    I've never had occasion to use this particular critter, and perhaps don't understand it as I should, but could you not simply use the Query the Crosstab was originally based on?

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    Sep 2012
    Posts
    9
    Hi MTB, seems like it should do what i want but is falling over at:

    tblOUTPUT("Value") = .Fields(j)

    with a Data type conversion error

    any ideas?

    Thanks

  5. #5
    Join Date
    Sep 2012
    Posts
    9
    Hi Missinglinq, i am trying to format data from a crosstab type format - many rows and columns into a lists. It is not actually from a crosstab query but not sure what to call the input format, but it has a simlar structure to the output of a crosstab.

  6. #6
    Join Date
    Sep 2012
    Posts
    9
    Hi MTB, thanks worked out the problem, there was an extra column in front. Thanks for your help.

    Is there any way to check the values being copied and if there is a < sign in front remove it and divide the value by 2 before pasting? Its easy in excel but not sure in Access?

    Thanks

  7. #7
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi Rhabdo2

    No real idea other than to ask if what the field data type is for data in the Input table and for the 'Value' field in the Output table? Although VB does not normaly have too much trouble coercing numbers to strings and vica versa.

    I was inthe middle of answering your first question as above before you second arrived. But in view of your second question the question above still applies!?

    Yes there is a way of checking, I assume that the data is text/string data in the Input table, if so, then maybe somthing like this
    Code:
        With tblINPUT
            Do Until .EOF
                Dim j As Integer
                For j = 1 To .Fields.Count - 1
                    tblOUTPUT.AddNew
                    tblOUTPUT("Site") = .Fields(0)
                    tblOUTPUT("Parameter") = .Fields(j).Name
                    If Left(.Fields(j), 1) = "<" Then
                        tblOUTPUT("Value") = Mid(.Fields(j), 2) / 2
                    Else
                        tblOUTPUT("Value") = .Fields(j)
                    End If
                    tblOUTPUT.Update
                Next j
                .MoveNext
            Loop
        End With
    ??



    MTB

  8. #8
    Join Date
    Sep 2012
    Posts
    9
    HI MTB,

    that worked perfectly, thanks for your help

    Cheers

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
  •