Hello, UAers. I hope you can help me with something - these forums have been a frequent source of resolution for me as I have gotten to know Access for my job as a research assistant, but now I'm coming across a problem I can't seem to resolve by just searching for similar posts.

So, our research project is a huge meta-analysis; we are trying to collect data for 215 separate medical/public health studies. My researchers have been coding each of these 215 studies and assigning different variables that answer different questions. For each study there are 10 questions that need answered, but since a study can include data for up to 20 sub-studies, I need separate fields for the answer to my 10 questions each sub-study. There's also a bunch of bibliographic and demographic data I'm collecting about each study, specifically 47 fields of that. So ... in my table (under design view), I have:

47 text/number fields of biblio/demo data (including the primary key, the REPORT ID)
10 questions, repeated 20 times each, that each look like this: SS1 Q1 (sub-study 1 question 1), SS1 Q2, SS1 Q3 .... all the way to SS20 Q10

This might be the moment you experienced Access Gurus are slamming your head against the desk because I should never have allowed this data to be formatted/collected this way but the "10 questions about up to 20 sub-studies!" part was added very recently as data-gathering points and my researchers now need THIS data alongside the previous data for each of these studies.

So here's what went wrong. I was adding my fields in chunks of 10 or 20 in the Table (under Design View), and then popping back to the Form that I was also designing so that the researchers have a friendly place to enter all this data. When I got up to SS14 Q10, which would be the 14th set of 10-question chunks I needed to add, suddenly I couldn't save the Table. I have sixty more fields I need to enter in this Table, but Access is now telling me "Too many fields defined", and when I check the help dialog, I get this:

Too many fields defined. (Error 3190)
You tried to perform an operation that involves more than 255 fields.
Reduce the number of fields, and then try the operation again.

I've read a few other people finding this problem when they're importing data from a SQL database (I've never done that) and I don't think that's my problem. Moreover, I don't think I have 255 different fields here - there are 187 to my count. Can anyone explain to me why I can't add the last 60-odd fields I need, and whether I'm just doing this all wrong? I thought I was pretty handy in Access for someone who's never formally studied how to use it, but I have the feeling I'm not understanding something about my Table here and would appreciate any input or insight available.

Krissa,

Greetings and Welcome to UtterAccess (IMG:style_emoticons/default/welcome2UA.gif)

A couple of things. I believe the count/limit of 255 includes controls that have been deleted.

Additionally, using concise and descriptive names for tables, forms, queries, etc will save you oodles of time typing out longer names.


AvgJoe (IMG:style_emoticons/default/hat_tip.gif)

This link might help - Library of Database Models
Use Ctrl+F and search for "Survey"

This post has been edited by AvgJoe: Apr 17 2012, 04:46 PM

Krissa,

A table should really never have more than a couple dozen fields, at the VERY most. What you're describing sounds more like a spreadsheet. Based on your description of what you're doing, here's what I'd recommend:



tblStudies
StudyID [Primary Key, Autonumber]
lngParentStudyID [Foreign Key to tblStudies.StudyID]
intStudyNumber
strStudyName

tblQuestions
QuestionID [Primary Key, Autonumber]
lngStudyID [Foreign Key to tblStudies.StudyID]
intQuestionNumber
strQuestionText

With this table structure for studies and questions, you can have any number of studies and any number of questions for each study. If a study is actually the sub-study of another study, you'd put the StudyID for the parent study in the lngParentStudyID field in the record for the sub-study. If the study has no parent, just leave that field blank. To create a link from a field to another field within the same table you just add the table to the Relationships Window TWICE, and create the link between them. When you get to the point where you're writing queries with this table, you can use the Alias feature of queries to name the second copy something like "tblSubStudies".

This strategy is how you would basically handle things like Genealogy or an Employees table with people reporting to supervisors (who are ALSO employees). You wouldn't have a table of supervisors and a table of employees, you'd just link from the "supervisor" field to the table's primary key.



As long as you understand that the data shouldn't be formatted like this, you're halfway home. Once you fix the data table setup, you just need to re-do the forms to work with the new table structure. Since this was added "very recently", the change shouldn't be that disruptive.

You haven't mentioned how you're handling the actual RESPONSES to these questions, but those need to be in a separate table as well.



Without seeing these 47 fields, I can only guess what precise fields you need, but here's a starting point: Look for ANY fields that are repeated (BookTitle1, BookTitle2, etc.), and you've found something that probably needs to be broken out into a separate table that links back to the tblStudies table. Anything that occurs only once per study can stay in the Studies table.

Hope this helps,

Dennis

Just for posterity's benefit -

I'm going to gamble the reason why you can't import even with only 187, is that with 47 text (but not memo) fields, it's possible that some records exceed the page size and thus import fails. You may be allowed to create a table that has 187 fields but it may not necessarily mean you can store a record where the total on-page size exceeds the page size (a number slightly less than 4096 bytes). Memo field don't count toward this limit as they're stored off the page but with 100 text field * 50 field size = 5000, it is possible to exceed that page size and thus fail to import.

Just to reiterate, however, that Dennis' advice is the best one, even if the page size wasn't a factor here.

More posterity ...

>> Memo field don't count toward this limit as they're stored off the page but with 100 text field * 50 field size = 5000 <<

Also, please note that the designed field size of a text typed field is not the amount of space consumed by the record. In other words, if you define a Text typed field to have a size limit of 50 characters, and another Text typed field to have a size limit of 255, and store a value that has 10 characters in each field, then each of those fields will consume 10 characters of space on the page. What that means is the text fields are not "padded" to consume their defined size.

Thanks for the clarification - it did sound like I was saying it'd be 5000 all the times, not a maximum consumption of 5000.