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

    Red face Access data base 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
    Numbers
    Text
    Memo
    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
    and
    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
    Oct 2005
    Posts
    1
    Hi vickii!

    This response is likely to get some negative feedback from our relational database friends, but what you are actually building isn't so much a relational model as it is a data mining model. Basically you are just trying to gather the data for analysis, you aren't necessarily going to be accessing the information over and over again, and you certainly won't be changing the data.

    Depending on the performance you need (which must be limited if you are implementing in Access) I would suggest you just dump all of the information in one table. Since all of the information for each survey response relates ONLY to that survey response there is really no reason to try to normalize your tables.

    As for the form design, if you are new to access, once you have designed your table, just use a wizard to design your form. Let the wizard put all the pieces in place, then go into design view and move stuff around to your liking.

    Hope this helps a little bit.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    If the destination for your data is SPSS then Access isn't neccesarily the right weapon to use to capture the data. But if its your weapon of choice (or the only one avaliable) then it is pefectly adequate to do the job. A potential limit is that Access has a limit of around 250 columns (not sure if it still exists but it is a problem). If you need more than 250 columns then consider splitting the table and use a one to one join between the questionaire table and a sub table using the same questionaire number

    When you come to reporting it can be a serious problem. There are also some limits in the length a query can be (ie actual text length aswell as number of columns in a query). depends on your version of Access but I have come accross problems in A97, A2000 & A2002/XP - it may no longer mbe a problem in A2003. The real issue is that soemtimes Access doesn't report the error and you spend ages chasing phantom problems.

    (Just) guessing your knoweldge of Access is limited then agree with 'DynamicData' and store your questionaires in a single table

    you may want other tables to provide some data validations, but it depends on your time available and budget or experience

    As regards the physical design of the table I'd try to find out what SPSS is happiest with in terms of data input and reflect that capability in your physical design (so that the step of pumping the data into SPSS goes easily as and when required - it may even be worthwhile prooving the concept before capturing a great deal of the data.

    As regards the form (data capture) I have seen a questionaire that used a tabbed form for each questionaiure section. Where questions had a preference these were stored as numerics (in this case there were 4 options (very good, good, bad , very bad) coded 4,3,2,1 respectively 0 indicated no response)
    in your case you may need to use 7 values - the reason when you do yoiur data capture if your repsponse is coded as a number then you can simplify the form design and speed up data entry

    limit your controls on the form (or conceivably in the table validation rule to 0 to 6)
    try to avoid textual responses, or at least keep them to a minimum
    try to use validation on the data - for example if its a multi site survey then have a table called 'DTSite' which stores the site options, pull that through onto your questionaire survey as an index number not text
    validate dates and other 'obvious data'
    HTH

  4. #4
    Join Date
    Oct 2005
    Posts
    4

    Thank you

    Thank you for your responses they were very helpful.

    I have tried using multiple joined tables
    1. background (including an id and location code
    2. activities
    3. problems

    That helped with the database part since I have too many fields.

    But I hit limits again with the form. I will also use tabbed pages for the form but I still have way too many fields, even for SPSS.

    I had thought that I was looking at the structure wrong and that there was, perhaps a way to restructure the database to get around the field limitations and make it more appropriate for ACCESS. I have used Filemaker and SPSS more than ACCESS so I am not that familiar with it. However, I have to use it for this project.

    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 didnt take something into consideration. Furthermore, I really would prefer file to use in SPSS with a record for each ID.

    If you or any one has any further thoughts on how best to approach this, I would be very grateful.

    In the meantime, I have asked the folks Im 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
  •