Pivot nightmare
I am quite busy actually on this b*** school census. It's a questionnaire that schools filled and sent back to us about a lot of things on ICT and their usage.
On the paper it's not so bad, divided in 6 parts from A to G,and about 60 questions per section, sometime more.
After some database coding, I have now the really painful task to export this database to be analyze by SPSS.
Well I don't know anything about this tool, not being myself a statistician, but apparently the version we have (10 I think) don't allow too much table manipulation.
Unfortunatly I absolutluy need to pivot my data, and it's truely slow boring and... painful !
The first idea is that our guy in charge of the stats ask first to have a huge unique table with evrything in it.
No way to do that,as I said to him, SQL or Access have some limitations on the numbers of fields.
Strangely, SPSS seems to be able to accept a very large number of columns(I don't remember exactly how much).
So actually I am going to create 6 tables multiply by 3 types of schools. Well,a bit more than that because I ran out of memory very quickly when I create the Pivot table in Access, and export this to Excel.
So I divided again in small data chunks, something like 4 to 6 parts each section, and I am going to merge that in Excel.
I said before that I ran out of memory and I have actually 768 Mb on my machine ! It's amazing now how much you need to manipuilate some big database.
It's a bit annoying that Access provide only an export to Excel from a pivot table.
I know I can write a Transform in the Querydef, but I would like to see Access give me automatically the SQL statement when I switch from Pivot view to Sql view.
Actually Access give me back my initial select statement :-(
OK now time to go back to finish this before I transform myself as a mad pivoting chair !