Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2005

    Red face Unanswered: Access Database design question from Novice

    I have a survey that has nearly 300 questions. It has 3 sections.
    1. background (including an id and location code
    2. activities
    3. problems

    Data types include
    About six scale types (e.g., very bothered----not bothered)
    Check boxes

    I need to
    construct a form for easy data entry that has all the questions
    be able to export the data and conduct my analysis in SPSS.

    Any advice on how to structure the file and form in ACCESS would be appreciated

  2. #2
    Join Date
    Jun 2005
    Richmond, Virginia USA
    Provided Answers: 19
    Don't know about exporting data to SPSS, but this is obviously going to be a huge form! My inclination would be to use tabbed pages with the Multi-rows property set to YES. This is going to be a big undertaking for someone to fill out the questionaire, and you need to make it as easy as possible by using a decent size font and not crowding your questions together. The Multi-row property allows the tabs to be displayed in, you guessed it! Multiple rows! Instead of trying to crowd 20 or 30 tabs across the top of your form.
    Hope this helps!

    The problem with making anything that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Jul 2004
    Southampton, UK
    I suspect Missinglinq's suggestion is the way to go for ease of implementation. However, there are a couple of things to think about.

    - Will the user complete the survey in one sitting. If not how will they know where they are up to ?

    - If the user misses a field they won't know until they try to save the record. But it wont be obvious which tab the missing data is under (although appropriate error messaging might help).

    I wonder if it's better to have a series of forms perhaps controlled by a master form. The user can only progress to the next form once the previous is complete. The master form obviously know about userID and which form the user is processessing. All collected data can still be stored in a single table. This might be more trouble than it's worth and depends on whether you can trust your users and your ability to right some code to manage the forms.

    Consider whether you want to force the user to answer the questions. By this I mean you could have a load of tick boxes (yes/no). But the default is "no" so the user doesn't have to actually answer them. If you have a combo with options blank/yes/no, and the default is blank but the constraint is <>blank then the user will be forced to consider the answer and change the value. See my point ?

    As for SPSS, if I remember rightly it readily imports from Excel and flat acsii files. Access will export to both of these very easily. Easiest is probably to export to Excel then use SPSS to import - ideal if you're one going to do it a small number of times. I've never tried importing from Access to SPSS but since SPSS is a data analysis tool I would imagine lots of import methods have been provided for.


  4. #4
    Join Date
    Oct 2005

    Wink Thanks for your assistance, some info, and Q's

    Tab pages is a great idea, I was going to use page breaks but tabs are clearer. I have only done small, clean 1 page forms This a lot bigger and more complicated than I am used to.

    The respondent wonít be entering the survey, interns will. So I have look up tables and check boxes in there to help them out as suggested.

    By ďseries of forms perhaps controlled by a master formĒ do you mean a main and sub-forms? I havenít done anything with sub-forms so I will see. I have read that sub-forms are for many to one relationships and I donít see that I have that here, but I may be missing it.

    Some things Iíve tried
    I think a major problem I have is in structure. With over 255 fields and one record per id I canít have one table, form or report to export.

    I first separated the survey into three tables joined by ID
    1. background (including an id and location code
    2. activities
    3. problems

    That was fine to get over the table problem but I still couldnít get everything to go on one form without hitting the limit.

    I have also tried constructing 3 tables as suggested in another thread
    1. Respondent info (individual id and location)
    2. Question info (and question id and question text)
    3. Answer info (individual id, question id, numeric answers, satisfaction answers, check box answers, and so on)

    The result of this should have been a record for each question and answer for a given id but it was a mess. I clearly didnít take something into consideration. Furthermore, I really would prefer a flat file to use in SPSS with a record for each ID.

    To compound the issue, there are actually other questionnaires to be administered to different types of clients. Some of the questions are identical some are not. I had initially thought I could create something that would cover them all so they could be analyzed together, but Iím having enough problems just dealing with one survey!

    I will try experimenting with your suggestions on a test file. If you or any one has any thoughts on how best to approach this, I would be very grateful.

    In the meantime, I have asked the folks Iím working with to try to reduce the number of items they want to look at!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts