As was foretold, we've added advertisements to the forums! If you have questions, or if you encounter any bugs, please visit this thread: https://forums.penny-arcade.com/discussion/240191/forum-advertisement-faq-and-reports-thread/
Options
Unpivoting/Normalizing data in Access 2007
Drez
Registered User regular
Registered User regular
So, it looks like SQL has a nifty little UNPIVOT statement...which isn't available in Access, unless I'm just being syntax-dumb right now.
I have a bunch of amounts with periods as fields/columns, which is not ideal. I'm trying to marry this to another data source which is already normalized with regard to periods.
This project is kind of quick and dirty, so initially I just pivoted the second source (via crosstab) and worked with the data that way. But now I'm trying to do another calculation and the pivoted data just doesn't work as I need to look at multiple pivoted values (periods that have been pivoted).
Anyway, I don't really want to spend too much time on this. Normalizing the first set of data would be force me to rewirite the queries that build off that data. Easy, but time consuming. The other option is to figure out some way to do a dynamic cross tab where I can change the field/column to be pivoted on the fly, do that systematically for each period, and then chuck it all together into one table.
So I have two questions. Is that even possible - is it possible to create a single cross tab query that can be reused multiple times (rather than copied - I am working with 25 periods and 25 crosstabs would be ridiculous) by pointing to a different column?
And is the UNPIVOT command available in Access 2007, or do I have to do it myself with a massive UNION query? I'm trying to avoid using any VBA for this project.
Final note: I know I'm not doing this the "right way" but I was given an extremely short amount of time to finish this, so whatever is fastest is what I'm doing.
(Don't judge me!)
I have a bunch of amounts with periods as fields/columns, which is not ideal. I'm trying to marry this to another data source which is already normalized with regard to periods.
This project is kind of quick and dirty, so initially I just pivoted the second source (via crosstab) and worked with the data that way. But now I'm trying to do another calculation and the pivoted data just doesn't work as I need to look at multiple pivoted values (periods that have been pivoted).
Anyway, I don't really want to spend too much time on this. Normalizing the first set of data would be force me to rewirite the queries that build off that data. Easy, but time consuming. The other option is to figure out some way to do a dynamic cross tab where I can change the field/column to be pivoted on the fly, do that systematically for each period, and then chuck it all together into one table.
So I have two questions. Is that even possible - is it possible to create a single cross tab query that can be reused multiple times (rather than copied - I am working with 25 periods and 25 crosstabs would be ridiculous) by pointing to a different column?
And is the UNPIVOT command available in Access 2007, or do I have to do it myself with a massive UNION query? I'm trying to avoid using any VBA for this project.
Final note: I know I'm not doing this the "right way" but I was given an extremely short amount of time to finish this, so whatever is fastest is what I'm doing.
(Don't judge me!) Switch: SW-7690-2320-9238Steam/PSN/Xbox: Drezdar
Drez on
0