Forum Discussion

jamesson_kaupanger's avatar
jamesson_kaupanger
Copper Contributor
Sep 26, 2019

Rearranging data in Access

I'm trying to input a large amount of data into Access, but instead of having one row for each result, the results are in a 2-dimensional grid, where each row represents a device being tested, each c...
2016
access
tsgiannis's avatar
tsgiannis
Iron Contributor
Sep 28, 2019

jamesson_kaupanger I can't say that its crystal clear what you are after but I think you are out for transposing "rows" to "columns"..

Just load the data in a recordset...iterate the fields on each record and .AddNew/.Edit accordingly.

jamesson_kaupanger's avatar
jamesson_kaupanger
Copper Contributor
Nov 08, 2019

I don't know how I missed this reply...

 

Sort of: I want to go from columns to column. In other words: if I have 4 sites and I'm running 5 tests on each of those sites, that's 20 total test results. Currently, the data is laid out in a 4 x 5 grid. I want to transpose that to a 20 x 1 grid, making the results all in one column.

  • tsgiannis's avatar
    tsgiannis
    Iron Contributor
    Nov 11, 2019

    jamesson_kaupanger As i said...load the data on a recordset...iterate it and use it to populate a table with the shape you want.

    Probably some sample data will make things clearer

    • jamesson_kaupanger's avatar
      jamesson_kaupanger
      Copper Contributor
      Nov 12, 2019

      tsgiannis 

       

      Here's a simplified example of what the data looks like:

       

      Each Excel file represents one lot

      Each lot has several samples

      Each sample has ~30 sites being tested

      Each site is tested with up to 300 tests.

      • tsgiannis's avatar
        tsgiannis
        Iron Contributor
        Nov 13, 2019

        Based on your sample you need some work ( i assume that every Excel has similar structure)

        Parse the Excel with Automation 

        Read the Lots (column) ...Group by ...these are inserted to the LOT table

        Read the Sites(Column)........these as inserted to the Sites table

        Read the Test Columns........these as inserted to the Test table

        Read the Columns with the data these are inserted to the DataValue table

        LotID  SiteID TestID DataValue

        I reckon that this probably could be a paid job

    • jamesson_kaupanger's avatar
      jamesson_kaupanger
      Copper Contributor
      Nov 12, 2019

      tsgiannis 

       

      I'll experiment with that and get back to you.

       

      On a related note, I've since learned that Excel's "Unpivot" function is precisely what I'm trying to do. The problem is that it relies on the user creating a Query. I need this tool to be able to be used by people that do not have an intimate understand of Excel's inner workings. When I tried to implement the function through VBA, the result was very convoluted and dependent on the specific data there; I don't know how to create a generic query for whatever tests might be across the top of this file. Do you have any insight in that realm?

Resources