A few questions on a simple DB

[Ericnord]

Hey everyone,
I've set up a DB to replace our paper survey system. Our surveys usually consist of 10 - 25 questions that are usually a scale of 1-9 type questions. I set up one table that autonumbers the participants, and then just added q1,q2,q3,q4,q5,etc with all having a number field. I then made a form with buttons that write back to the DB. Everything was working fine until I got to question 15 and went past the page limit.

1. How can I either increase the page size past the 22", or make a next button that goes to a pg.2 form?

2. Also, I read on here how to make the form open on startup, and all the menus go away, but now I'm quite stuck and cannot edit my DB in any manner. Any ideas on how to erase the startup command if I took away all the menus?

3. Any advice on changing my setup on the one table and one form?

Thanks for any help.

 

[Fizzio]

Hi.

1) Format your forms yourself or use tab controls on your forms to split the questions into 2/3 sections.
2) Press the Shift Key whilst opening the Db
3) Depends on how much flexibility you want in your survey collection. Your current setup is adequate for using 1 survey only (although not ideal)
A better structure would be

tables:

tblParticipants
------------------
ParticipantID
ParticipantName (Split this into Forename/Surname if you want)
etc all data related to the participant

tblSurveys
-------------
SurveyID
SurveyName
SurveyDate
etc all data related to each survey

tblSurveyQuestions
-------------------------
QuestionID
SurveyID
QuestionText
etc

tblSurveyResults
---------------------
ResultsID
ParticipantID
SurveyID
QuestionID
QuestionScore

This allows many participants to complete many surveys, many surveys to have multiple questions and results.
You can then form relationships between the tables (look up normalisation / normalization both here and at microsoft) and using forms and subforms, allow yourself a much more robust, longer lasting survey collection system.

It seems quite complicated now but get the basic structure right and look up how to use forms/subforms effectively.

Hope this helps and gives food for thought.

 

[Ericnord]

Fizzio,
Thanks for the reply. Can you or someone expand some on your #1. I did format the form myself and it still exceeded the page length. I do not know how to make the form go to a second page with a next button, or tabs, or anything.

Thanks
P.S. I'll look more at your DB setup!

 

[Fizzio]

Pat, Was the structure I suggested OK for the application in mind?

 

[Ericnord]

Thanks for the help Pat I'll search the archives now.

We do differents experiments and rather than a surevey, I guess they are more a questionnaire.

The reason I haven't done something more elaborate is because I don't know if there is a need. I do not need to store anything about the participant other than a number, so I do not need a participant table. The questions are all a scale so they are stored as numbers 1-9, so I don't need a table for question answers.

I do see a need for a questions table, that way I wouldn't need to change my form everytime, but then again I would need to change my questions table everytime, so I don't see an advantage.

Right now the only things I need to change are the numer of questions in my table depending on the questionnaire, and the questions on the form.

I just do not know how to fit 40 questions on a form. It is too big. How to I link a form to another with a next button or tab.

Thanks

 

[Fizzio]

To split the form up, look at the form control panel. Hover the mouse over the icons and look for the tab pages icon. you can place this on the form then create/delete tabs to make more than one 'page' on the form.

The structure I suggested would match Pat's solution on using a Continuous form rather than 'hard coding' the questions into the design of the form.

It is better to over-engineer than just create what is needed now as 6/12 down the line, you may need that extra info you allowed for in your basic database structure.

 

[Ericnord]

I'm sorry Fizzio, but I cannot find what you are speaking of. Control panel for form...hover mouse over icons. I don't know where you are talking about. I have the form open in design view. I've searched all the menus, to toolbox icons, looked for tab pages icon in helper, etc.

 

[Fizzio]

Cheers Pat. (I'm still learning but getting better - must be hard jetting around all the time)

On the form design screen, there will be a toolbar that is used to create controls for the form (If you created the form, you've already used it!) Look for the one that has 2 tabs on it and place this on a blank form. Then add all your controls into the 2/3 tabforms. (I'm off home now but if you haven't managed to suss it, search microsoft for the forms example database. this gived loads of good examples how you can set up a form and the controls you could use)

I still think you'd be better off improving your structure first though. It'll be worth it in the long run.................

 

[Ericnord]

Ok, thanks to the both of you for all the help. I will mess around with Fizzio's setup and see if I can get a grasp on it.

Thanks

 

[Ericnord]

Fizzio, HELP

Hey Fizzio,
I'm trying to implement your DB suggestions that you offered a while back. I'm stumped on the SurveyQuestions tbl. You have QuestionID, SurveyID (which I entered and then made a relationship with the SurveyID from Surveys tbl), and then you have QuestionText. The last one is the one I don't understand. Do I need to type, " 1. this is a sample question" or what? Also, all the questions need 9 big buttons under them for the people to answer the question with, since they are scale questions. Will I need to enter this row of buttons on the form for every question? The buttons are numbered 1-9, but sometimes the first and last buttons have words that cue the participant, ex. 1 - Easy 9-Difficult other times they say 1 - Agree 9 - Disagree. Any suggestions?

Thanks

 

[Ericnord]

Here is an image to maybe help you visualize what I have going and the way I would like the form to end up. I should have added this to the above post to help clear it up some.

Thanks

 

[Ericnord]

Nobody can help me any? It doesn't have to be Fizzio or Pat Hartman. Any help would be appreciated

 

[Fizzio]

The QuestionText is as you correctly describe the actual question. You do not neccessarily include the question number as you can do this by code but it does make it easier. The relationships are set up how I intended.
Re: the buttons. It depends how you are going to display each question. The best way, as Pat suggests is in a continuous subform but it depends on how much info you want to present to the user at once. There is a useful workaround with the scoring buttons by using a scoring field on your form and when this is entered, another form opens with the scoring buttons on. When a button is clicked, it enters the score in your record. I have an example of this using a calendar which is on the same form as the field and just unhides to allow you to enter the value (attached).

 

[Ericnord]

Fizzio,

Thank you for the reply. I'm glad I set everything up like you had intended. It does not matter if they are presented w/ 1 or more at a time. My question is how can I have the 9 buttons, that I assign values of 1-9, be the same for every question that is presented. That is, if I'm going to put the buttons on as a permanent part of the form, and the questions are changing, how can I tell the buttons which question they are answering so they can record it in the results table correctly.

Thanks for all the help!
P.S. I wasn't able to get any ideas from your calendar because I'm not good enough at access to know what I'm even looking for.

 

[dgm]

Hi Ericnord,

How about if u use something like this (see attachment, if it works). An option group with 9 toggle buttons in it.

To get the text underneath, add 3 (or 9 if u like) extra fields to your questions table. For each question, enter the text for the buttons. E.g.

tblQuestions

QuestionID 1
SurveyID 1
QuestionText "1. Sample Question"
ButtonTextLeft "Too Light"
ButtonTextMiddle "Just Right"
Button TextRight "Too Heavy"

QuestionID 2
SurveyID 1
QuestionText "2. Sample Question 2"
ButtonTextLeft "Agree"
ButtontextMiddel " "
ButtonTextRight "Disagree"

When u click on a button it adds a value of 1-9 in your questionscore field of your table tblSurveyResults.

Alternatively, if u are going to reuse button text for different questions, u could add the text to a separate table, and just link this table to your question table.

Also, I would add an extra table to store completed surveys. Not really nessesary i guess, but just makes it a bit neater in my opinion.

P.S.
You mentioned before that u didn't know how to go to the next page on a form. I believe u can use the docmd.gotopage command to do this. Just make sure u seperate pages with a page break.

It's funny how sometimes a simple idea turns out to be not so simple in practice.

Hope this helps

 

[Ericnord]

Still crawling along here...

Ok, so I'm still crawling along on this survey DB I was asked to do. I liked dgm's .jpg he sent and tried to copy his tables and relationships. The problem I am now running into is in duplicating his, or making my own, form. The top of his form uses the survey name and date from tblSurveys and it also uses participant name from tblParticipants. Then his questions appear to be in a subform that contains qText, qFirst, qMiddle, and qLast. So when using my wizard, I need to link the form to tblQuestions, tblSurveys, and tblParticipants.

Now, my main problem is telling the items on the form WHERE to write the data. For example, the survey name, date and participant name are written to tblCompl Surveys correct? Then question 1 I have it record the data in resAnswer in the tblResults? Do I even need to be assigning all these questions to a location to record? When I did my one table design I did and it worked, but now with this "better" table setup I can't get any of the data to record anywhere.

Thanks for all the help. My failures are not a result of lack of support from everyone here I've just never worked on access before given this task and seem to be taking a longer than normal time to grasp it.