"too many fields defined" error

[Rachel]

I am trying to add a field to my table and keep getting an error message "too
many fields defined" and it will not let me add the new field. Does anyone
know what I have done to get this error and how I can fix it so I can add
fields to my database?

 

[Ken Snell \(MVP\)]

How many fields do you have in your table? The maximum number for a single
table is 255. Note: it's rare that you would need more than 30 fields in a
table if you've properly normalized your table structure.

 

[John W. Vinson]

I am trying to add a field to my table and keep getting an error message "too
many fields defined" and it will not let me add the new field. Does anyone
know what I have done to get this error and how I can fix it so I can add
fields to my database?

If you've been adding and deleting fields, you can get this error even if
you're well below the (absurdly huge) 255 field limit; each added field takes
up one of the 255 "slots" and they're not freed when you change or delete the
field. You can get them back using Tools... Database Utilities... Compact and
Repair.

As Ken says, if you're even CLOSE to 255 fields, you need to reconsider your
table structure. "Fields are expensive, records are cheap"!

John W. Vinson [MVP]

 

[Rachel]

I have 117 fields. I use the database for physician information for my
hospital keeping much information. I performed the "Compact and Repair" as
John Vinson suggested and am now able to add. I had added and deleted fields
taking up the space.
Thanks for your respond, I appreciate your feedback. Rachel

 

[Rachel]

I do have 117 fields and have added and deleted several others as things
change so I am sure I used up the space. I performed the "Compact and
Repair" and it completely solved my problem. Thanks so much for your help!!

I appreciate it, Rachel

 

[John W. Vinson]

I do have 117 fields and have added and deleted several others as things
change so I am sure I used up the space. I performed the "Compact and
Repair" and it completely solved my problem. Thanks so much for your help!!

I VERY strongly suspect that you have one or more "one to many" relationships
embedded in each of these enormously wide records. Do you perhaps have fields
like Specialty1, Specialty2, Specialty3; Degree1, Degree2, Degree3? If so each
set of these fields should be pulled out into a related table.

With 117 fields you run the risk of a more subtle but even more dangerous
limit: you can have no more than 2000 characters *actually used* in any one
record. You can create a table with (say) 117 50-byte Text fields; Access
won't complain. But as soon as you (or, much more likely, a computer-phobic
influential user!) fills a record with text... bang, confusing error message,
no record saved.

Glad the compact worked but... I think you still have some normalizing that
should be done!

John W. Vinson [MVP]

 

[Kim Gruenstein]

John,

I have a concern regarding a database I created. I have one table with
almost 250 fields defined. I know what you’re thinking, it’s ridiculous, but
I can’t figure out how to link specific records to specific records in
another table without re-entering the primary key. For example, I have a
record for an ingredient; the ingredient number is the primary key. (ex:
W12345 v1) How do I enter information about W12345 in a subform and link it
to the specific record without retyping W12345 in the subform?
I'm not at risk for reaching the 2000 character limit per record, but I
would love to clean the design up a bit. And I can't combine any fields
because it needs to be searchable.

Any advice you offer is appreciated!

 

[Jeff Boyce]

Kim

It all starts with the data...

You've told us that you have a table with LOTS of fields (more than 30 is
VERY unusual)... but you haven't told us what you're keeping in those
fields.

If you'd like more specific suggestions, please provide a bit more specific
information about your current table structure.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[boblarson]

And to add to what Jeff said -

To answer your question about how to add the key into the subform, with a
properly designed mainform/subform with the master/child links set right you
don't have to worry about it. It is taken care of.


--

Bob Larson
Access MVP
Access World Forums Administrator
Utter Access VIP
Free Access Resources at http://www.btabdevelopment.com

 

[Kim Gruenstein]

Ok, to be more specific about what's in the fields:

Denisty Standard, Density Min, Density Max, Density Analysis (Yes/No field),
Density Method: I have around 25 different physical and chemical
characteristics, all of which require 5 fields a piece. I can't put all the
info in one field because it must be searchable.

A few other fields I have are: ingredient description, ingredient
statement, Allergen information, ship temp min, ship temp max, ship temp
target, etc...

I would really like to separate out the physical and chemical fields into
separate tables and link them. I've tried setting up the link child/master
fields correctly, but to no avail.

I have to be able to pull up specific records in a form, and display each
seperate record in a report. I can do this with the current set-up, I
haven't been able to do it any other way.

Thanks for your help, I'll give you more information if you need it!

Kim

 

[Jeff Boyce]

Kim

I'm still not clear on whether we're talking about oranges or toner for
copiers or ...?

Are you saying that every row needs to have values for EVERY column (i.e.,
each item has [Density Std], [Density Min], [Density Max], ..., [ingredient
description], ... )?

This sounds very much like how you'd set up to record information in Excel
(need another characteristic? add another column ... or five!).

Are you saying you have "around 25 physical characteristic", each of which
has a [xxxx Std], [xxxx Min], [xxxx Max], ...?

As mentioned before, it would be a VERY unusual real-world situation that
required more than 30 columns in a well-normalized database table (of
course, there would be more tables with more data). The general idea is
"deep, not wide" when you work with relational data.

(still) More info, please...?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Frank]

What if you have a questionnaire with more than 255 items? I imagine the only
solution is to split the questionnaire into several tables, them link them.
For data entry it is annoying to have to move from one table to another, and
retype the IDs, for a single questionnaire.

Any ideas on the best way for doing this?

Thanks,

Frank

Kim

I'm still not clear on whether we're talking about oranges or toner for
copiers or ...?

Are you saying that every row needs to have values for EVERY column (i.e.,
each item has [Density Std], [Density Min], [Density Max], ..., [ingredient
description], ... )?

This sounds very much like how you'd set up to record information in Excel
(need another characteristic? add another column ... or five!).

Are you saying you have "around 25 physical characteristic", each of which
has a [xxxx Std], [xxxx Min], [xxxx Max], ...?

As mentioned before, it would be a VERY unusual real-world situation that
required more than 30 columns in a well-normalized database table (of
course, there would be more tables with more data). The general idea is
"deep, not wide" when you work with relational data.

(still) More info, please...?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Ok, to be more specific about what's in the fields:

Denisty Standard, Density Min, Density Max, Density Analysis (Yes/No
field),
Density Method: I have around 25 different physical and chemical
characteristics, all of which require 5 fields a piece. I can't put all
the
info in one field because it must be searchable.

A few other fields I have are: ingredient description, ingredient
statement, Allergen information, ship temp min, ship temp max, ship temp
target, etc...

I would really like to separate out the physical and chemical fields into
separate tables and link them. I've tried setting up the link
child/master
fields correctly, but to no avail.

I have to be able to pull up specific records in a form, and display each
seperate record in a report. I can do this with the current set-up, I
haven't been able to do it any other way.

Thanks for your help, I'll give you more information if you need it!

Kim

 

[Roger Carlson]

No, the correct solution would be to normalize your tables so there is a
single field for the questions and each question is stored in a *different
record*. To see an example of a properly normalized questionaire, take a
look at Duane Hookom's "At Your Survey" database at
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=3


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

What if you have a questionnaire with more than 255 items? I imagine the
only
solution is to split the questionnaire into several tables, them link
them.
For data entry it is annoying to have to move from one table to another,
and
retype the IDs, for a single questionnaire.

Any ideas on the best way for doing this?

Thanks,

Frank

Kim

I'm still not clear on whether we're talking about oranges or toner for
copiers or ...?

Are you saying that every row needs to have values for EVERY column
(i.e.,
each item has [Density Std], [Density Min], [Density Max], ...,
[ingredient
description], ... )?

This sounds very much like how you'd set up to record information in
Excel
(need another characteristic? add another column ... or five!).

Are you saying you have "around 25 physical characteristic", each of
which
has a [xxxx Std], [xxxx Min], [xxxx Max], ...?

As mentioned before, it would be a VERY unusual real-world situation that
required more than 30 columns in a well-normalized database table (of
course, there would be more tables with more data). The general idea is
"deep, not wide" when you work with relational data.

(still) More info, please...?

Regards

Jeff Boyce
Microsoft Office/Access MVP




message

Ok, to be more specific about what's in the fields:

Denisty Standard, Density Min, Density Max, Density Analysis (Yes/No
field),
Density Method: I have around 25 different physical and chemical
characteristics, all of which require 5 fields a piece. I can't put
all
the
info in one field because it must be searchable.

A few other fields I have are: ingredient description, ingredient
statement, Allergen information, ship temp min, ship temp max, ship
temp
target, etc...

I would really like to separate out the physical and chemical fields
into
separate tables and link them. I've tried setting up the link
child/master
fields correctly, but to no avail.

I have to be able to pull up specific records in a form, and display
each
seperate record in a report. I can do this with the current set-up, I
haven't been able to do it any other way.

Thanks for your help, I'll give you more information if you need it!

Kim



:

And to add to what Jeff said -

To answer your question about how to add the key into the subform,
with a
properly designed mainform/subform with the master/child links set
right
you
don't have to worry about it. It is taken care of.


--

Bob Larson
Access MVP
Access World Forums Administrator
Utter Access VIP
Free Access Resources at http://www.btabdevelopment.com



message
John,

I have a concern regarding a database I created. I have one table
with
almost 250 fields defined. I know what you're thinking, it's
ridiculous,
but
I can't figure out how to link specific records to specific records
in
another table without re-entering the primary key. For example, I
have
a
record for an ingredient; the ingredient number is the primary key.
(ex:
W12345 v1) How do I enter information about W12345 in a subform and
link
it
to the specific record without retyping W12345 in the subform?
I'm not at risk for reaching the 2000 character limit per record,
but I
would love to clean the design up a bit. And I can't combine any
fields
because it needs to be searchable.

Any advice you offer is appreciated!


:

On Mon, 11 Jun 2007 20:22:00 -0700, Rachel
<[email protected]>
wrote:

I do have 117 fields and have added and deleted several others as
things
change so I am sure I used up the space. I performed the "Compact
and
Repair" and it completely solved my problem. Thanks so much for
your
help!!

I VERY strongly suspect that you have one or more "one to many"
relationships
embedded in each of these enormously wide records. Do you perhaps
have
fields
like Specialty1, Specialty2, Specialty3; Degree1, Degree2, Degree3?
If
so
each
set of these fields should be pulled out into a related table.

With 117 fields you run the risk of a more subtle but even more
dangerous
limit: you can have no more than 2000 characters *actually used* in
any
one
record. You can create a table with (say) 117 50-byte Text fields;
Access
won't complain. But as soon as you (or, much more likely, a
computer-phobic
influential user!) fills a record with text... bang, confusing
error
message,
no record saved.

Glad the compact worked but... I think you still have some
normalizing
that
should be done!

John W. Vinson [MVP]

 

[Douglas J. Steele]

Each question would be a separate row in a Questions table, each answer
would be a separate row in an Answers table.

For the proper way to model a questionnaire, see Duane Hookom's "At Your
Survey"
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At Your Survey 2000'

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

What if you have a questionnaire with more than 255 items? I imagine the
only
solution is to split the questionnaire into several tables, them link
them.
For data entry it is annoying to have to move from one table to another,
and
retype the IDs, for a single questionnaire.

Any ideas on the best way for doing this?

Thanks,

Frank

Kim

I'm still not clear on whether we're talking about oranges or toner for
copiers or ...?

Are you saying that every row needs to have values for EVERY column
(i.e.,
each item has [Density Std], [Density Min], [Density Max], ...,
[ingredient
description], ... )?

This sounds very much like how you'd set up to record information in
Excel
(need another characteristic? add another column ... or five!).

Are you saying you have "around 25 physical characteristic", each of
which
has a [xxxx Std], [xxxx Min], [xxxx Max], ...?

As mentioned before, it would be a VERY unusual real-world situation that
required more than 30 columns in a well-normalized database table (of
course, there would be more tables with more data). The general idea is
"deep, not wide" when you work with relational data.

(still) More info, please...?

Regards

Jeff Boyce
Microsoft Office/Access MVP




message

Ok, to be more specific about what's in the fields:

Denisty Standard, Density Min, Density Max, Density Analysis (Yes/No
field),
Density Method: I have around 25 different physical and chemical
characteristics, all of which require 5 fields a piece. I can't put
all
the
info in one field because it must be searchable.

A few other fields I have are: ingredient description, ingredient
statement, Allergen information, ship temp min, ship temp max, ship
temp
target, etc...

I would really like to separate out the physical and chemical fields
into
separate tables and link them. I've tried setting up the link
child/master
fields correctly, but to no avail.

I have to be able to pull up specific records in a form, and display
each
seperate record in a report. I can do this with the current set-up, I
haven't been able to do it any other way.

Thanks for your help, I'll give you more information if you need it!

Kim



:

And to add to what Jeff said -

To answer your question about how to add the key into the subform,
with a
properly designed mainform/subform with the master/child links set
right
you
don't have to worry about it. It is taken care of.


--

Bob Larson
Access MVP
Access World Forums Administrator
Utter Access VIP
Free Access Resources at http://www.btabdevelopment.com



message
John,

I have a concern regarding a database I created. I have one table
with
almost 250 fields defined. I know what you're thinking, it's
ridiculous,
but
I can't figure out how to link specific records to specific records
in
another table without re-entering the primary key. For example, I
have
a
record for an ingredient; the ingredient number is the primary key.
(ex:
W12345 v1) How do I enter information about W12345 in a subform and
link
it
to the specific record without retyping W12345 in the subform?
I'm not at risk for reaching the 2000 character limit per record,
but I
would love to clean the design up a bit. And I can't combine any
fields
because it needs to be searchable.

Any advice you offer is appreciated!


:

On Mon, 11 Jun 2007 20:22:00 -0700, Rachel
<[email protected]>
wrote:

I do have 117 fields and have added and deleted several others as
things
change so I am sure I used up the space. I performed the "Compact
and
Repair" and it completely solved my problem. Thanks so much for
your
help!!

I VERY strongly suspect that you have one or more "one to many"
relationships
embedded in each of these enormously wide records. Do you perhaps
have
fields
like Specialty1, Specialty2, Specialty3; Degree1, Degree2, Degree3?
If
so
each
set of these fields should be pulled out into a related table.

With 117 fields you run the risk of a more subtle but even more
dangerous
limit: you can have no more than 2000 characters *actually used* in
any
one
record. You can create a table with (say) 117 50-byte Text fields;
Access
won't complain. But as soon as you (or, much more likely, a
computer-phobic
influential user!) fills a record with text... bang, confusing
error
message,
no record saved.

Glad the compact worked but... I think you still have some
normalizing
that
should be done!

John W. Vinson [MVP]

 

[Frank]

Thanks for the response. If I understand correctly, each respondant will get
their own table? And each question will be a row in that table.

One other question - how would you export the whole thing to SPSS, with all
the respondants and their responses in a single table?

Thanks in advance,

Frank

 

[John Spencer]

No, you will have one set of tables

Respondents
Questions
PossibleAnswers
Responses - with three fields (poijting back to the three tables)
RespondentID
QuestionID
AnswerID

If you have more than 255 questions, you will need to export the responses
into a text file to be read by SPSS. This will require a custom VBA function
that will write the data to a text file.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Frank]

Correction: as I understand each respondent will take up multiple rows in a
table.

Still I am wondering how to export several tables into SPSS to merge into
one table.

Thanks again,

Frank

 

[Roger Carlson]

No. All of the respondants would be in the same table, just as all the
questions would be in the same table. Although I'm not sure we're talking
about the same thing. Are you referring to people who responded? Or to
their responses? Either way, you'd have a single table holding all of them,
each in a separte ROW, not column. You would never have a separate table
for each of a group of things (entities). You would have one table to hold
them all and differentiate them with an identifing field.

It's a little hard to explain with generalities. Take a look a Duane's
sample.

As for exporting it to SPSS, you would do that with a query or perhaps some
code that would denormalize it into a flat file. A crosstab query might
work for this or something like my Denormalize.mdb sample on my website:
http://www.rogersaccesslibrary.com/download3.asp?SampleName=Denormalize.mdb

You really need a better grasp of data normalization, as that is the
foundation of relational database design. You might want to look at some of
my Database Design tutorials as well:
http://www.rogersaccesslibrary.com/TutorialsDesign.html.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Frank]

Thanks, I understand now. I needed to have a look at the table itself to see
how it works. I can see that each respondant has multiple rows in the
'responses' table, where each of these rows is a question.

I'll have a look at the links to learn more about normalization and
denormalization.

Thanks,

Frank

 

[Frank]

Thanks for your response. It looks a bit complicated but at least I know now
that it's do-able

Frank