Results 1 to 9 of 9
  1. #1
    moorecurls is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Posts
    24

    Transpose data from row to column

    I have a large amount of data that I need to transpose from row data to column. A normal crosstab query will not work because the data is in a block (fieldKeyID) 1 - 67 not a normal table.



    The Row data looks like this in just 2 fields a FieldKeyID that is always the same 1-67, and Field that provides the actual data:

    FieldKeyID Field
    1 Fieldname123
    2 123568
    3 E
    5 TUS01
    6 5/8/18
    8 ABC
    60 GL Charge
    67 50.00

    I've run several queries and created the table as I need with the Fieldnames as ColumnHeaders and the data below each fieldname, yet I have one problem see below:

    FieldKeyID Fieldname1 Fieldname2 Fieldname3 Fieldname5 Fieldname6 Fieldname8 Fieldname60 Fieldname67
    1 Filename123
    2 123568
    3 E
    5 TUS01
    6 5/8/18
    8 ABC
    60 GL Charge
    67 50.00

    I need some kind of if query that will combine FieldKeyIDs 1-67 on one row, and then go to the next line for the next 1-67 lines of data. Any suggestions on how to get this all on one line.

    I know that I could export to Excel and easily transpose the data as I had been doing til this week when I found out that excel was scrambling the data at about line 30k; therefore now I don't trust excel. Therefore I'd like to keep all of this data in access where it seems to handle the large amount data better.
    Attached Thumbnails Attached Thumbnails DataDetail.PNG  

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I'll have to take your word that a crosstab won't work because I don't know what you mean by a "block" and why that's a barrier.
    I suppose you could loop through a recordset based on the original data and create/update another recordset that's written to your transposed data table. Then again, someone else might jump in and do one better because I tend to over complicate things.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I am really confused on what the initial data looks like and what the data should look like after transposing.
    Can't tell if the data ("blocks") are in Excel, a text file or an Access table.

    I need some kind of if query that will combine FieldKeyIDs 1-67 on one row, and then go to the next line for the next 1-67 lines of data. Any suggestions on how to get this all on one line.
    If a normal crosstab query will not work, I would think that VBA with looping would be able to transpose the data.

    I would like/need to see about 5 examples of the data ("blocks") before transposing and after transposing.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I am surprised Excel waited until 30k to scramble.

    CROSSTAB won't work because there is no column with an identifier value that associates rows of each block as a single record. This ID would become the Row field in CROSSTAB.

    Either fix raw data to include ID or build VBA procedure that opens and manipulates recordset in looping structure. Actually, without this ID it will be difficult to use a recordset because can't sort records to keep rows of block together. So maybe have to use Excel automation to pull data from worksheet or read from text file.

    Second thread this week I've responded to with this exact scenario https://www.accessforums.net/showthread.php?t=73660
    Last edited by June7; 09-14-2018 at 02:40 AM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    moorecurls is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Posts
    24
    Yes the Field Key is the cause of the simple crosstab will not working because for the attached example I need all of the Field Key ID 1-67 on one line of data for the crosstab to be correct. So I need for each Field Key ID 1-67 to pick up the unique value which is Field Key ID 2 in the third column Fieldname2. Surely there is a VBA code that will loop thorough each set of Field Key Ids 1-67 and in another column place the unique value of fieldname2. This is unique no matter how many 100k lines of data I have. Any suggestions! I'm currently using excel and it works ok but takes forever and as I mentioned before I just found out this week that it can scramble the data probably because of our slow company network but now I don't trust excel at all. Thanks for any suggestions you might have.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I described how can accomplish with VBA, just not with a query based on the sample.

    Your output sample shows more rows and data than in the raw data sample. Why? Provide an accurate sample.

    Exactly what are you doing in Excel? I tested transpose and it does not work.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    JSR is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2018
    Location
    Reno, NV
    Posts
    41
    Crosstab should work. The column heading (PIVOT in the SQL statement) would be "fieldname" & [FieldKeyID] (that way you get fieldname15 instead of just 15). The Value (TRANSFORM in the SQL statement) would be First([DataField]). The reason for using the First() function is because a crosstab query takes all the rows from the input table that match your requirements and applies the aggregate function to display one value in the output's relevant row/column. This could be Sum(), Min(), etc., if they were numbers, but since you have just one piece of data for each row/column of the output, and it's text, First() (or Last(), same result) will work (i.e. the first one, or the last one, in a list of just one, is that one and only piece of data).

    However, there is a problem, and that is you need another field in the input table which tells you which one of the data blocks numbered 1-67 you are on. Your example has one but you said you have lots of them, so you need that in order for the data to be usable (i.e. if you have a thousand of them, there are a thousand rows with FieldKeyID=1, so which one of the 1,000 is this). This field would be the row heading in the crosstab query design. Add this field in Excel and then import it, so it looks like this:

    Code:
    DataBlockNum FieldKeyID    DataField
    1             1            Fieldname123
    1             2            123568
    1             3            E
    1             5            TUS01
    1             6            5/8/18
    1             8            ABC                      
    1            60            GL Charge
    1            67            50.00
    2             1            Fieldname124
    2             2            123569
    2             3            F
    2             5            TUS02
    2             6            5/9/18
    2             8            DEF                      
    2            60            GG Charge
    2            67            51.00
    ...
    So it's actually quite easy once you have DataBlockNum. Crosstab query with Row heading DataBlockNum, Column heading "fieldname" & [FieldKeyID], Value First([DataField]).

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    And that's exactly what I meant in post 4. Some VBA can generate that ID. Then CROSSTAB should work.
    Code:
    Sub SetBlockID()
    Dim c As Integer, r As Integer, intID As Integer
    r = 2
    c = 1
    intID = 1
    While Not IsEmpty(Worksheets("Sheet1").Range("A" & r))
        If c <= 8 Then
            Worksheets("Sheet1").Cells(r, 3) = intID
            c = c + 1
            If c = 9 Then
                intID = intID + 1
                c = 1
            End If
        End If
        r = r + 1
    Wend
    End Sub
    Or example of VBA code in workbook that transposes on worksheet:
    Code:
    Sub Transpose()
    Dim c As Integer, r As Integer, x As Integer
    x = 2
    r = 2
    c = 3
    While Not IsEmpty(Worksheets("Sheet1").Range("A" & x))
        If c < 12 Then
            c = c + 1
            Worksheets("Sheet1").Cells(r, 4) = r - 1
            Worksheets("Sheet1").Cells(r, c) = Worksheets("Sheet1").Range("B" & x)
            x = x + 1
        Else
            c = 4
            r = r + 1
        End If
    Wend
    End Sub
    Assumes: each block has 8 rows; no blank cells in column A until the end.
    Last edited by June7; 09-14-2018 at 04:19 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    The OP still hasn't clarified what a "block (fieldKeyID) 1 - 67" is, what it look like or how it is stored.
    Still curious about it, but it sounds like a CROSSTAB should work, so I'll back out.

    Good luck with your project........

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

Similar Threads

  1. Replies: 5
    Last Post: 04-22-2017, 06:13 AM
  2. Transpose the data
    By deepakes2 in forum Access
    Replies: 6
    Last Post: 07-11-2016, 01:27 PM
  3. Transpose Data
    By jamesfranklin in forum Queries
    Replies: 15
    Last Post: 03-07-2013, 09:19 AM
  4. Transpose Data
    By Ray67 in forum Database Design
    Replies: 10
    Last Post: 08-07-2012, 06:18 PM
  5. Replies: 3
    Last Post: 06-20-2011, 03:09 PM

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