Question MS Access questionnaire database!! (1 Viewer)

[H4v0k]

Hi
I have said yes to constructing a survey questionnaire type database in access 2007
It is for a mining company to asess the impact the mine is having on the community and whether they can create jobs for the locals. I have 28000 questionaires going to print as we speak. once these have been filled in they need to be captured.

I thought this was something easy to do but since I have changed my mind on that. I d/l the Access 2007 Bible and started learning what I can but its 1413 pages and I dont have enough time. I sorta know a bit so far. What I need help with is the basic structure of the database...what tables forms ect any ideas as to how you would go about designing such a database?

thanks

 

[Galaxiom]

It is a little difficult to advise when all you have told us is there are 28000 questionaires.

If there are multiple choice questions there should be a table for each set of answers unless they are simply Yes/No. These tables will be used as the Control Source for comboboxes on the form.

These answers will be normalised. This means they will be stored in the main records as codes, usually a number, associated with the particular response. Their tables will have the number and the text as two fields.

Where the answer to one question controls the choices in another you will probably have cascading comboboxes on the form. In this circumstance you might have separate tables for the different groups of questions.

If you have no experience with Access you have a very steep learning curve ahead of you.

 

[Atomic Shrimp]

If there are multiple choice questions there should be a table for each set of answers unless they are simply Yes/No. These tables will be used as the Control Source for comboboxes on the form.

This bit doesn't sound quite right to me - why have separate tables for the source of each combo?

 

[H4v0k]

Hi , thanks for the response

I have included (attached) a shortened version of the questionnaire that will be issued to 28000 residents of the area. I thought it might help give an idea of what i am dealing with.

I would also like my forms to look similar so the data captures will easily work with it. Then finally I will need to be able to ask questions like how many people who have a drivers license also have a degree in something...that kind of stuff.

thanks

 

[Galaxiom]

This bit doesn't sound quite right to me - why have separate tables for the source of each combo?

It depends on the nature of the answers. If they are all the same such as Strongly Agree to Strongly Disagree then yes you would use the same RowSource for them all.

Even if there are different answers all the combos can share one table and have a field for each. If every combo has the same number of choices this is not a bad idea. If there are different numbers of answers you then include Is Not Null in the Where clause to prevent blanks being included. But this would lead to more processing than simply loading all the records straight into the combobox.

However if you have diverse choices and cascading combos with a selction grid for the associations between boxes it makes sense to put them in separate tables. I often include a checkbox field for include (so a choice can be easily turned off) and a number field for sort order. Where you have a particular non-alphabetical requirement the sort order can be important when you need to add new choices. When you have several boxes in the same table it can get messy with naming of all the fields and their associated properties.

I also tend to think of it in terms of records. If the answers in one combo have no relationship to the answers in another they are really not part of the same record and don't really belong together.

Guess I was thinking at the complex end of the scale.

 

[DCrake]

With regard to the layout of the forms that you will require to capture the data. Use the actual questionairre as a template. Starting top left to bottom right. This way whoever is inputting the results will be able to do it blindfolded after 1 day.

The forms should also give you an indication of what fields you need and what data types the fields should be.

I have not read all the threads closely but this should give you a good head start.

David

 

[The_Doc_Man]

Shorter than reading the Access Bible, try searching this forum for the topic of
Questionnaires. It comes up every couple of months so you should see some other ideas people have had for their Q&A databases.