Survey database problems

[DeborahP]

HELP!!

I need to generate a survey database. I have created a database and entered data but now I am having problems getting my results to tally. I have a survey with 4 questions and with each question, the user can have up to 3 answers. I generated a table with user's job title, department, answer1a, answer1b, answer1c, answer2a, answer2b, etc. The data entry is so pretty, however, if I wanted to tally on job title and have the report to show me how many selected the first option, second - up to ten choices. It will not work.

Little more info! For each question I have a drop down with 10 choices that the user can select.

Example:What support programs would be beneficial as a new employee?

Choice #1 Choice #2 Choice #3

Dropdown would contain: Shadowing program, Program-updates, hospital tours, legal classes, ethical classes, time management classes, Hot line

The user would read the question, click on choice #1 dropdown and select one from the list. Again, the user could select up to 3 choices for each question.

I know by putting all my answers in one table is not normalization but I can think of another approach.

Any suggestions would be greatly appreciated.

Deborah

 

[Lord Byron]

I would try to solve this by using 3 queries to count the results. This way, each of those 3 queries would go over their own field.

But a more elegant solution for this might be the use of subforms. With this, you could use different tables for each question since there can be multiple answers to each question. The main problem that I see here is how to restrict the users to enter a maximum of 3 choices here.
But maybe you, or other people on this forum know a solution for that.

Don't know whether this helps you or not. If you have any more questions regarding what I just said, please do not hesitate to post it, and I will do my very best to answer it.

 

[Pat Hartman]

You have created a flattened spreadsheet structure for what is actually hierarchical data. You would be better off analyzing the data with Excel especially if this survey is limited use and you don't intend to expand it or do another.

If you want to restructure your application to use the correct relational structure so that you can analyze the results with queries, you'll have to start from scratch. There have been numerous questions regarding how to properly structure a survey poste here with suitable answers. Search the archives.

If you want to persue a restructure and the previous posts don't help, post back using a reply to this thread.

In any event, if you intend to create other databases, you need to do some studying of database design. Search the Microsoft knowledge base for articles on database design and normalization to get you started. There are also a couple of good articles to be found at www.fmsinc.com . One of these articles specifically addresses the differences between spreadsheets and relational tables.