09-26-2019 01:27 PM
09-26-2019 01:27 PM
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?
11-08-2019 08:49 AM
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.
11-12-2019 10:15 AM
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?
11-13-2019 10:14 AM
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