Extracting data from a spreadsheet-style table

[milehighfreak]

I have a friend who collected hundreds of surveys from different college dorms across the state. Instead of asking me to database it all for him, he decided to give it a shot himself. He's wound up putting everything he collected into one single table, complete with duplicate dorm names and rooms down the columns, and everything filled out sideways. I offered to fix it but I'm stumped. The topleft table is roughly the format he's put the surveys in; the three tables on the right I created manually to try to work backwards to a solution, and the form is based on the basic query of those tables. I want to be able to turn the table on the left into something approaching the much better setup on the right. How can I go about it without him having to type everything out again? Thanks a ton.

 

[jdraw]

I have a friend who collected hundreds of surveys from different college dorms across the state. Instead of asking me to database it all for him, he decided to give it a shot himself. He's wound up putting everything he collected into one single table, complete with duplicate dorm names and rooms down the columns, and everything filled out sideways. I offered to fix it but I'm stumped. The topleft table is roughly the format he's put the surveys in; the three tables on the right I created manually to try to work backwards to a solution, and the form is based on the basic query of those tables. I want to be able to turn the table on the left into something approaching the much better setup on the right. How can I go about it without him having to type everything out again? Thanks a ton.

I think it is a matter of creating a number of queries to select data from the "spreadsheet table" and create a number of meaningful, normalized tables.
Once you get the meaningful tables, you will be able to make use of the data.

First step, in my opinion, would be to get some meaningful names for the tables. Make a diagram of the things (your friend) believes are resident in the spreadsheet table, then devise a query/queries to extract those things into meaningful, normalized tables.

If the data is in the spreadsheet, and if your friend can identify what he has collected, it's a matter of manually creating and adjusting queries to get the data into proper tables. I don't think it is a data retyping exercise, unless there is total confusion as to what was collected and stored in the "spreadsheet table".

Just my thoughts.