"Too many fields defined" issue

[forest8]

Hi there

I have an access table that has about 255 columns.

Several fields need to be adjusted to a Yes/No instead of its current
setting of text.

When I try to change it, I get a "Too many fields defined" message that pops
up.

Followed by "Errors were encountered during the save operation. Data types
were not changed. Properties were not updated."

What is the best way to resolve this?

Thank you in advance.

 

[John W. Vinson]

Hi there

I have an access table that has about 255 columns.

Then you have a Really Badly Designed Table. 30 columns is a very wide table.
60 columns is a *huge* table.

Several fields need to be adjusted to a Yes/No instead of its current
setting of text.

Let me guess... a survey with one field per question? Have you seen Duane
Hookum's "At Your Survey" design? It solves this problem. We may have
discussed this earlier, I don't recall.

When I try to change it, I get a "Too many fields defined" message that pops
up.

Followed by "Errors were encountered during the save operation. Data types
were not changed. Properties were not updated."

What is the best way to resolve this?

What's happening is that there is a hard limit of (an absurdly huge) 255 field
limit on tables. When you change a field definition it adds a field with the
new datatype, and copies the data from the existing field... eating up one of
the 255 "slots". What you may need to do is change the definitions of one or
two fields (few enough that you don't hit 255); Compact the database; change
one or two more; etc.

MUCH better... normalize your data so that your tables are tall and thin, not
wide and flat.

 

[Jeff Boyce]

Take a look at related posts in the tablesdbdesign newsgroup.

You'll find that any table with more than around 30 columns is a likely
candidate for further normalization.

Although Access tables look a bit like spreadsheets, Access is NOT a
spreadsheet.

The way you'd structure data in a spreadsheet will only lead to much more
work from you and from Access, trying to come up with workarounds for
feeding it 'sheet data.

Access' features and functions are optimized for well-normalized data. If
"normalization" and "relational" are unfamiliar terms, plan on spending some
time coming up to speed on them before expecting to get good use of Access.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[forest8]

The original data was in the SPSS format. I inherited the database from
someone who had imported the SPSS tables into ACCESS.

I can try to normalize the data but that may take more time than I can
afford at the moment. So I'm trying to make the most out of the situation.

Thanks

 

[John W. Vinson]

The original data was in the SPSS format. I inherited the database from
someone who had imported the SPSS tables into ACCESS.

I can try to normalize the data but that may take more time than I can
afford at the moment. So I'm trying to make the most out of the situation.

Were you able to restructure the table?

The alternative would be to create a new (250 or so) field table, manually;
and run an Append query to migrate the data. Access will convert most datatype
changes, you might need some expressions or code to do it in some cases.

And you have my sympathy... I used to work with a lot of SAS users...!

 

[forest8]

Since I started this, I haven't attempted any queries since I don't fully
understand the functionality. I am going to divide my table into 2 sections.
Hopefully the append query will be easy for my to use to make sure that the 2
tables are connected.

Thank you for all your help with this question as well as other questions
you've been kind to answer.

Forest

 

[forest8]

Hi again

Since my last post, I attempted an Append Query but was not very successful.
Instead of appending columns I appended rows.

I normalized my table into 6 separate tables for each category. There are 2
unique keys in each table. The first is an autonumber for each table (i.e.,
Part1_ID, Part2_ID, Part3_ID, etc.) . I made the StudentID also unique (and
it appears in every table). There is no primary key identified.

My question here is: when people are adding data to each table do I have to
run an append each time?

If I am going off in a tangent, how do I get back on track?

Thank you.
Forest

 

[John W. Vinson]

Hi again

Since my last post, I attempted an Append Query but was not very successful.
Instead of appending columns I appended rows.

Well, it sounds like you succeeded perfectly, in that this is exactly what
Append queries are SUPPOSED to do: add new records, not new fields.

I normalized my table into 6 separate tables for each category. There are 2
unique keys in each table. The first is an autonumber for each table (i.e.,
Part1_ID, Part2_ID, Part3_ID, etc.) . I made the StudentID also unique (and
it appears in every table). There is no primary key identified.

Sorry... but that's DEnormalizing, not normalizing.

If you're going to do this, I'd delete the autonumber; there is no point to
having two different unique identifiers. If each student will have one, and
only one, ever, record in each of these tables you can make StudentID the
Primary Key and define a one-to-one relationship between the Students table
and this table.

My question here is: when people are adding data to each table do I have to
run an append each time?

It's not at all clear to me what you're appending from. Generally, no - if
users enter data into a table, then the data *is in the table* and you don't
need a separate operation to add it! Only if the users enter data into one
table, and it needs to end up in a different table, will you need an Append
query.

If I am going off in a tangent, how do I get back on track?

If you have consciously chosen to reject the "tall thin" structure - with one
ROW per answer, not one FIELD per answer - described in At Your Survey, then
I'm afraid I can't be much help. Perhaps you could post an example of your
current table structure.

 

[Arvin Meyer [MVP]]

The original data was in the SPSS format. I inherited the database from
someone who had imported the SPSS tables into ACCESS.

I can try to normalize the data but that may take more time than I can
afford at the moment. So I'm trying to make the most out of the situation.

As John mentioned, you really should re-design your structure. The only
other possibility that I can think of is to add a second table, and use the
same primary key field for the additional columns. That will create a 1 to 1
relationship between the tables. It is really poor design though and you
will most certainly have problems with queries, reports and forms. It's
probably much more time efficient in the long run to bite the bullet and fix
it now.

 

[Jeff Boyce]

As John & Arvin have pointed out, you can "pay now" (spend the time to learn
normalization and normalize your data structure) or "pay later" (keep
struggling with how to get Access to use 'sheet data, and modifying your
structure and queries and forms and ... everytime you change questions).

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[gls858]

Hi there

I have an access table that has about 255 columns.

Several fields need to be adjusted to a Yes/No instead of its current
setting of text.

When I try to change it, I get a "Too many fields defined" message that pops
up.

Followed by "Errors were encountered during the save operation. Data types
were not changed. Properties were not updated."

What is the best way to resolve this?

Thank you in advance.

While this doesn't address your current situation, you may want to take
a look at Question Pro. We used it here for a while and price wise it's
reasonable. Might be a better tool for the job.

http://www.questionpro.com/

I don't work for them or benefit from their sales yada yada. Just liked
their program.

gls858

 

[Tom Lake]

Hi there

I have an access table that has about 255 columns.

Several fields need to be adjusted to a Yes/No instead of its current
setting of text.

When I try to change it, I get a "Too many fields defined" message that pops
up.

Followed by "Errors were encountered during the save operation. Data types
were not changed. Properties were not updated."

What is the best way to resolve this?

Thank you in advance.

You'd have to delete a field and pack the database before you could add another field.
Changing a field type is like adding a new field, copying the data from the old field
and then deleting the old field. You can't do that when you already have 255 fields.

You might try writing a make table query that creates a new table with half the fields
from the original table, delete the copied fields from the original table, then relate the
two
tables in a one-to-one relationship.

Tom Lake