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

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.

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

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 didn’t 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 I’m working with to try to reduce the number of items they want to look at!!!