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

    Unanswered: Access 2007 - Convert Rows to Columns

    I am trying to change rows to columns via Access 2007 SQL Queries. I’ve Googled other examples, but am still unsuccessful in many attempts to get my changes to work.

    For simplicity sake, I have four different tables I will call: RegionA, RegionB, RegionC and RegionD. They contain the same format, but require different processing to obtain their table data results. Once I can convert rows to columns for one table, I can create similar queries for the other three tables. I will then append the three new query results to the new table. I also need to add a column in the new table and then total each column upon completion.

    I've attached a word document to describe my Table Inputs and desired Table Output results. Could someone please provide me assistance?

    Thx -Tim

  2. #2
    Join Date
    Oct 2010
    Posts
    9
    Create a table containing the different regions. In that table - Id; Region.
    i would have one table (Datatable) where you have an Id and dropdown box and can select either Region A or B or C a or D and then add the "data" next to this region selected, in another field ie quantity.
    You then use a query that looks like this :
    RegionA: IIf([tblregions].[Region]=1,[Quantity],0)
    The "1" is the autonumber next to the region in the previous table mentioned.
    This will take the data from the row and add it to a column and add a zero if no data appears in that formula. Do this for all 4 regions and change the title ie regionB and ie 1 to 2 or 3 or 4 for regions.
    There must also be one field that is the same for all the regions when you select a region and added the quantity.
    Instead of appending data i will rather use queries to display the data because it will cause problems should you need to change values, and it needs to be updates in the different tables. Hope this helps

  3. #3
    Join Date
    Jan 2005
    Posts
    146
    You can try using a function. I'd first create the new table with PK and Region Fields, you could do that manually or using VBA. Then use VBA to determine the columns to add (based on the number of records in RegionA table) and then add the values from all 4 tables.
    Code:
    Dim Rst As DAO.Recordset
    Dim NewRst As DAO.Recordset, db As DAO.Database
    
    Set db = CurrentDb
    
    'create new table
    DoCmd****nSQL "CREATE TABLE RegionAll (Region Text, PKID AUTOINCREMENT PRIMARY KEY);"
    
    'Now add each new column
    If DCount("*", "RegionAll") > 0 Then
        For X = 1 To DCount("*", "RegionA")
        DoCmd****nSQL "ALTER TABLE RegionAll ADD " & X & " Integer;"
        Next X
    End If
    
    'now add data
    X = 1
    Set NewRst = db.OpenRecordset("RegionAll", dbOpenDynaset)
    
    'add from RegionA
    NewRst.AddNew
    NewRst!Region = "Transactions-A"
        If DCount("*", "RegionA") > 0 Then
            Set Rst = db.OpenRecordset("RegionA", dbOpenSnapshot)
            Rst.MoveLast
            Rst.MoveFirst
            Do While Not Rst.EOF
                'Now add data
                NewRst.Fields(X) = Rst!RegionA-Totals
            X = X + 1
            Rst.MoveNext
            Loop
           Rst.Close
           Set Rst = Nothing
        End If
    X = 1
    NewRst.Update
    
    'add from RegionB
    NewRst.AddNew
    NewRst!Region = "Exception-B"
        If DCount("*", "RegionA") > 0 Then
            Set Rst = db.OpenRecordset("RegionB", dbOpenSnapshot)
            Rst.MoveLast
            Rst.MoveFirst
            Do While Not Rst.EOF
                'Now add data
                NewRst.Fields(X) = Rst!RegionB-Totals
            X = X + 1
            Rst.MoveNext
           Rst.Close
           Set Rst = Nothing
            Loop
        End If
    X = 1
    NewRst.Update
    
    'add from RegionC
    NewRst.AddNew
    NewRst!Region = "Administrative-C"
        If DCount("*", "RegionA") > 0 Then
            Set Rst = db.OpenRecordset("RegionC", dbOpenSnapshot)
            Rst.MoveLast
            Rst.MoveFirst
            Do While Not Rst.EOF
                'Now add data
                NewRst.Fields(X) = Rst!RegionC-Totals
            X = X + 1
            Rst.MoveNext
            Loop
           Rst.Close
           Set Rst = Nothing
        End If
    X = 1
    NewRst.Update
    
    'add from RegionD
    NewRst.AddNew
    NewRst!Region = "Corporate-D"
        If DCount("*", "RegionA") > 0 Then
            Set Rst = db.OpenRecordset("RegionD", dbOpenSnapshot)
            Rst.MoveLast
            Rst.MoveFirst
            Do While Not Rst.EOF
                'Now add data
                NewRst.Fields(X) = Rst!RegionD-Totals
            X = X + 1
            Rst.MoveNext
            Loop
           Rst.Close
           Set Rst = Nothing
        End If
    NewRst.Update
    NewRst.Close
    Set NewRst = Nothing
    db.Close
    Set db = Nothing
    Without your DB I'm shooting in the dark but it should be structured something like I am showing.

  4. #4
    Join Date
    Sep 2012
    Posts
    21
    Sounds like a good idea, except my processing steps will be automated. When a user selects a Statistic Transaction button, I extract, calcuate and produce four tables. Then I will reformat and consolidated those four tables into one (this is the step I need help on). Afterwards I generate a report of that table, which gets exported to it's proper destination for the user(s). All of those steps are automated behind the scenes with a click of a button. Hope you can still assist me.

  5. #5
    Join Date
    Sep 2012
    Posts
    21
    Just saw the post with the VBA info. That would probably be great if I knew anything about VBA programming.

  6. #6
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Tim

    I have attached a sample database with tables tbl_region_a to tbl_region_d that have your data as per your attachment. Table tbl_region_summary is the summary of your tables as per your Region All.

    I have assumed that you have 4 tables and each table has 9 rows as per your input layout.

    Form frm_transpose_data is a form that shows the output data. Click the button Run Code to run the code. When you change a value in one of the tables and click the Run Code button the data displayed will be updated with the new data etc.

    The module mod_transpose_regions is used to read each table in order then converts the rows into columns, stores this conversion into an array then outputs the array to the tbl_region_summary.

    There is no need to use queries etc.

    If you need assistance using this for your application please contact me off forum on the email below.

    Edit
    ******
    There is one additional field in the table tbl_region_summary called data_order, when you sort on this field the data will be displayed in the correct order.
    Attached Files Attached Files
    Last edited by Poppa Smurf; 09-07-13 at 03:47. Reason: Additonal information

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    personally I'd bin the four tables that contain the same data AND store that data in one table

    then I'd create a pivot table out of the that data

    Having 4 tables with the same layout and similar data is a a clear warning that this may be flaky physical design. the fact that you want to re-integrate the data is almost certain proof
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Sep 2012
    Posts
    21
    Wow Poppa Smurf! Your results and technique are very impressive. Since I do not know Visual Basic coding, this could possibly lead to a problem for me if there are update requirements/maintenance to be done down the road. However, I would like to use your methodology as an interim solution for now.

    I do need to make some cosmetic changes, so I will contact you on Monday via your email, since I cannot access the database until then. Thx a bunch for doing all of that for me!
    -Tim

  9. #9
    Join Date
    Sep 2012
    Posts
    21
    Hi Healdem,

    The reason for the four different tables with the same design was because each table has different requirements coming from different sources.

    Using a pivot table was one of the unsuccessful methods that I tried before seeking help through this Forum. I could only get the regions to appear or the totals, but not both. I would love to learn how to use the pivot table. Could you please provide me an example using one of my tables?
    Thx -Tim
    Last edited by Tim-Morgan; 09-08-13 at 02:36.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •