Relational Excel File?

[sfmfs]

I have an excel file which was exported from a surveying package. It contains date as follows:

            Question 1    Question 2   Question 3   Etc.
Alabama      Answer 1      Answer 2     Answer3      Etc.
Colorado     Answer 1      Answer 2     Answer3      Etc.
California   Answer 1      Answer 2     Answer3      Etc.
Etc.         Answer 1      Answer 2     Answer3      Etc.

My goal is to get crystal to group by question and then list each State's response to each question underneath that question. Can that be done from this flat excel file, if so how?

 

[synapsevampire]

Well you do have a dilema in that the spreadsheet isn't in a table format, it's in a pivot table or crosstab format.

If you open the Excel file and in the location above the State place the word State, then Crystal will understand this, however it still doesn't lend itselrf well to what you want.

A shame you didn't bother to state your software version, something that should accompany any post for any software, and the first question asked by any technical support person.

What I would do is "unpivot" the data using Access if you have it.

Basically you would modify the Excel file as suggested, then create a UNION ALL query to present the data in a usable fashion, as in:

select 'q1' Question, state, question1 answer from table
union all
select 'q2' Question, state, question2 answer from table
union all
select 'q3' Question, state, question3 answer from table
etc...

This would then result in a table which had a column called Question, with each of the question numbers defined as Q1 thru Q<however many you have>, the state, and an answer field to contain the answers.

Now you can group on the Question field and use the answers intelligently.

-k

 

[sfmfs]

Sorry...

The surveying package is called Inquisite, I belive version 3.5. I am using Excel 2003, SQL 2000, and Crystal 10 Pro. I have the Inquisite Access .mdb, but it uses some very complex linking that makes certain types of responses such as yes/no, choose all that apply, etc. not show up in the ResponsesText field in the database where the majority of the answers do. Because Inquisite is what is exporting the excel file, it knows where to grab all of the differnt types of answers from, and creates a the file in the format in the original post. Thanks for your help, it is much appreciated. I will be playig with this again tomorrow morning.

 

[sfmfs]

PS.

How exactly would you go about "Unpivoting" this data using access? Thanks again, Steven

 

[synapsevampire]

I explained how to unpivot the data, that's what the UNION ALL statements are doing...reread the post, and try it in a Access Query. You can link (not import) the Excel file into an Acess database (doen't have to be their Access database), and build out the query.

And I was referring to the version of Crystal you were using, which you still didn't share. You might also perform a UNION ALL query within newer versions of Crystal.

-k