"Too many fields defined" issue

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.

On Fri, 15 Jan 2010 15:27:15 -0800, forest8
wrote:

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.

--

John W. Vinson [MVP]

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" wrote in message
...
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.

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" wrote:

On Fri, 15 Jan 2010 15:27:15 -0800, forest8
wrote:

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.

--

John W. Vinson [MVP]
.

On Fri, 15 Jan 2010 20:37:04 -0800, forest8
wrote:

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...!
--

John W. Vinson [MVP]

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

"John W. Vinson" wrote:

On Fri, 15 Jan 2010 20:37:04 -0800, forest8
wrote:

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...!
--

John W. Vinson [MVP]
.

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



"forest8" wrote:

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

"John W. Vinson" wrote:

On Fri, 15 Jan 2010 20:37:04 -0800, forest8
wrote:

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...!
--

John W. Vinson [MVP]
.

On Sat, 16 Jan 2010 09:12:01 -0800, forest8
wrote:

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.
--

John W. Vinson [MVP]

"forest8" wrote in message
news
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.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

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.

"forest8" wrote in message
...
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.