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.