Rearranging data in Access

[jamesson_kaupanger]

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 column represents a specific test, and a row-column combination results in the value from the test.

Is there a way in Access to shift all of the "values" data into the same column and then just identify each entry by its test ID?

[#2]

@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.

[#3]

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.

[#4]

@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

[#5]

@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?

[#6]

@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.

[#7]

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