Survey Development with infopath + Microsoft Access, looking for a way to do this with decent database design

Arguas — Thu, 27 Aug 2009 19:00:20 GMT

Hello people of Infopath Dev,

I've been charged with the task of making a user friendly survey that produces analyzable data. I'm a computer scientist, and as a result I'm not afraid to delve into doing the task I'm about to detail programmatically, if I could just get a straight answer as to how to do it.

The survey needs to be able to be posted onto or accessed using SharePoint. I have determined I can upload the document, so I will go ahead and skip the Web Service components of Infopath. The survey is intended to gather project sponsor feedback for those who have had projects done through our department. The survey has 8 fields that detail information about who the person is, what the project was, and what the project was for. There are 27 questions in the survey, all of which can be replied to on a scale of 1 to 5 with the exception of a general comments section. The analysis of the survey will count the number of people who responded to the survey in each possible way. For example, the report is intended to analyze how many people answered question 1 with a 1, how many with a 2, how many with a 3, and so on, for a given project. I was asked to do this project using sharepoint because, well, it is easy to make it look pretty; SharePoint's survey utility looks awful if you can't adjust any code to be able to provide headers or font changes.

My first approach was to take each field and use it as a column in Access 2003 in a large table of responses, and try to get the counts and reporting from there. However, the restrictions of queries in Access make it very difficult to figure out the count results from each of the question and generate a new table based upon those results, and also, since they are columns, I can't make a generic function to easily loop through a given column to get counts of a particular response. In general, we figure that the raw data can be thrown into an Excel data and some manual analysis will suffice for now.

But honestly, I find this to be a very ugly way to do it. My boss shouldn't need to do something manual like that, since a database's purpose is to be able to not only store data, but also to be able to run queries robust enough to generate that data for her. So I looked at a database design that would make this easier: Make a table populated when information is entered via infopath. Ideally, the columns would be the possible responses, and each row would be a response to a given question, making queries much easier and having the counting algorithm just be a summation.

However, InfoPath 2003 makes this very difficult. Each field can only have one data source, and it appears that I can't have a repeating section predefined more than once, even if the only thing changing is the question name and the description of what the question is.

So, after having explained what the goal is, my question is this: How do I programmatically submit multiple rows into an Access database through Infopath with the same data source target? The XDocument.submit appears to send all data currently present on the form to their various sources, which is not what I want if multiple fields are pointing to the same datasource. Really, all I want to do is to generate an SQL query that does a multiple insertion into a table that I have some control over. To visually illustrate, this is what I'm looking for on the Infopath form:

PM1: I was suitably involved in the planning/estimating aspects of the project. (Drop down with responses 1, 2, 3, 4, 5) generates the row PM1, a 1 in the appropriate response column, and a 0 in all of the others.

PM2: I understood thoroughly what was expected of me. (Drop down with responses 1, 2, 3, 4, 5) generates the row PM2, a 1 in the appropriate response column, and a 0 in all of the others.

... and so on, for all 27 questions.

Is there any way for me to do this at all in Infopath, or must I stick to the nasty solution of throwing every response into the same table in the database?

Thank you for reading my long post, and in advance for your response.

~Ryan Walters.