0

I face the following problem in MS ACCESS 2010.

I have a (or many) table A that looks like this:

 F1  | F2   | F3   | ... | F?
-------------------------------
     | B005 | B005 | ... | B345
     | C235 | C255 | ... | C235
A123 | 12.3 | 27.4 | ... | 87.0
A276 | 34.8 |  7.2 | ... | 42.0
...  |  ... |  ... | ... |  ...
A876 | 91.2 | 13.3 | ... |  0.1

So there a two rows at the top and one column to the left containing key values.

I need to achieve a representation of this data that looks like this (more normalized?):

 K1  | K2   | K3   | Data
-------------------------
A123 | B005 | C235 | 12.3
A123 | B005 | B255 | 27.4
A123 | B345 | C235 | 87.0
A276 | B005 | C235 | 34.8
         .  .  .
A876 | B345 | C235 |  0.1

Searching for a solution brought up UNPIVOT (which does not work with ACCESS?) and a number of UNIONs of SELECTs (to simulate UNPIVOT). The problem, however, is that the total number of columns, nor their names, in table A is not known in advance (or not constant for different tables as input). How can I deal with this?

Thanks for suggestions.

Erik A
  • 31,639
  • 12
  • 42
  • 67
Filo
  • 1
  • 1
  • Possible duplicate of [How to simulate UNPIVOT in Access 2010?](http://stackoverflow.com/questions/7255423/how-to-simulate-unpivot-in-access-2010) – philipxy Feb 18 '17 at 01:10
  • Clarified the question to show that even if unpivoting was possible the main problem is the unknown number of columns (and column names). – Filo Feb 18 '17 at 14:50
  • 1
    Curious what is the source of this data? It does not seem to have derived from a database table but imported from some type of analytical report with multiple/hierarchical headers (i.e., Excel pivots, Pandas groupby). Please advise because working at the entry point may be a better solution. – Parfait Feb 18 '17 at 17:32
  • You are right that the sources are "analytical report with multiple/hierarchical headers" (Maybe Excel or txt/csv). Since these files are extern data sources, the idea was to leave them untouched (for documentation) and directly load to ms-access and go from there (to reduce the number of not automated steps in the workflow). – Filo Feb 18 '17 at 20:35

0 Answers0