Database for a Survey

[Klion]

Alright, I was asked to make a database to hold answers to a graduation survey. The answers will be used mostly for statisical purposes.

As it stands I've designed the form in microsoft word (the actual survey) which I want to be distributed via email or something similarily convenient. I was initially going do it with a pdf form, except that I discovered people with reader cannot save the filled in form. Useless adobe. I was forced to switch to microsoft word by my inability to find a better solution. I looked at html forms but I don't think I can make those work for my situation. I need whatever I use to be both very convenient to fill out (to maximize the number of responses), as well as to cause a minimal number of potential technical problems for the person filling it out.

As for the tables where the information will be stored, I am not sure how I should design it. The survey itself has aproximately 40 fields for different student information, answers, etc. Some examples are

StudentID, firstName, lastName, areaCode, phoneNumber, Degree, Art, Sciences, Commerce, etc.

StudentID is a unique identifier for each student so easy to make that a primary key btw.

The problems I run into are partly because of the limitations in the way I am distributing the form. I do not want to use macros in microsoft word because I believe that will make 95% of the people unable or unwilling to fill the form out. This leaves me with a lack of functionality that I must make up for on the database end.

For example, I cannot do a cascading menu without macros in microsoft word (as far as I know), and the list box is limited to 25 items. There are 30 majors, so I broke it into 3 categories, Arts, Sciences, and Commerce. However on the database end the one filled out must be found and put into the "Major" field. I figure it should work if I just make an append query to append all 3 fields into one field since the other 2 should be blank. Have yet to try it though.

My problem with all of this is that when this database is being used I am not going to be around. So basically I need help either eliminating steps, or automating the steps to take the information from the form to the database tables.


------------------------------------------------------------------------

Right now the steps required would be something along the lines of:

I) Microsoft word form is sent out to grads, filled out form is sent back.

II) Form information is manually saved to a text file (options>save form info) in format something like "info0", "info1", "info2" etc.

III) The text file is manually imported into access to (dont know where. Possibly a table containing all information in the original format, or else an otherwise empty table) Not sure how to make this simple.

IV) The single survey worth of information is appended to the last row of a table containing the other survey information, as well as some fields combined via queries.

At which point the information is in a useable format.



Things I could use help with:

1) Is there any better way/program I could make the survey that would make my work in Access easier?

2) How many tables should I be using? One seems convenient, since it isn't being used for any other purpose and there is a seperate database for student information etc (not connected to this one at all) so this is self contained..

3) How to greatly simplify this process for the user when they are importing answers?

4) How to do a query to put the three fields into one, and if I can combine this with step 5?

5) The best way to go about adding the new survey answers into the table(s) containing existing survey answers.

The university I'm doing this for is quite small btw and it seems quite unlikely the lifetime of this database will be such that it sees 1000 seperate survey entries.

Any help is much appreciated, I wasn't hired for this and I have limited experience with databases in general heh. The hard part doesn't making it though, it's making it so they can use it.

Sorry for length

 

[Klion]

That first article seems to be exactly what I was looking for for importing my form's data, thanks a lot

 

[The_Doc_Man]

I agree that the first article Pat gave you is good. If you want to take a lesser view, you can also take this approach.

Just build the questionnaire as a Word document with one table like is shown in that article. For each questionnaire returned as a filled-in table, drop it into a directory.

Open the file using a Word Application Object. Open the document. Look in the Tables collection for table #1. That table will include a collection of Rows. The rows will include a collection of Cells. You can random access the cells. You have to do something involving the Selection.Text property of the cells, but it is just a syntax issue. Suppose that your column 1 is row titles and column 2 is the data. Suppose that your form puts the person's age in row 6. This is (if I recall it correctly)

WordApp.Documents(1).Tables(1).Rows(6).Cells(2).Selection.Text

or something like that. In other words, build the form, know where you put things, and then just pick out the results. The only difference between this and the article is that I don't bother to assign field names to the fields in the table.

In either case (the article Pat gave you or the quick-and-dirty method I described above), there is a rule to remember when playing with application objects. When you are done,...

If you opened it, close it.
If you created it, delete it.
If you allocated it, release it.

Otherwise you get "out of memory" errors real fast.

 

[Klion]

Upon searching previous threads I found an example survey database design for a very similar problem in this thread: results database

I have a few questions about this. The way this database seems to work is instead of instead of storing the actual data it contains a unique reference to an answer in another table. This other table contains all the possible valid answers to the choose an answer type questions.

First, it seems to be set up for one row per response? That would be like 40 rows per person who filled it out, I'm a little hesitant about that design since it seems that set up would be unneccesarily large. Also with this set up the only way I can see to add responses that don't follow a standardized format (ie a "general comments" answer) is to add a new table for all the non combobox/checkbox type questions, and a table such as this would by necessity be missing things such as an optiongroup.. well i suppose I could make an empty one just so I could name it/look it up hm.


My other question is how do I actually make use of this reference in access?

For example if I were to make a form inside access for manual data entry, and an arbitrary answer is chosen from a combobox that is say the optiongroupID = 3, optionID= 8. Is there any simple way to have the form automate the storage of the reference, or am I going to have to (try to) code something in VB to somehow validate and convert then store responses?

Another example problem I forsee is the VB import code from the article pat suggested, since that directly imports the data from the word form to access table how do I modify it to take it from straight "data form" to "data reference form", then store that reference? Something like hm make a function with the word data variable passed in as an arguement then just have the function search the answer table and when it finds that then get the unique IDs that accompany it, then return that or so.

Hrm, could add another arguement to the function for the optiongroupID so that the function would only search the answers for the question rather than the whole table, but I don't know VB so I'm not sure if this would be the best way. If it is I will likely need some help with the syntax, heh.