Prev: Having problems with Combo Box (Form Control)
Next: String formatting question
From: forest8 on 15 Jan 2010 18:27
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.


From: John W. Vinson on 15 Jan 2010 18:50
On Fri, 15 Jan 2010 15:27:15 -0800, forest8
<forest8(a)discussions.microsoft.com> 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]
From: Jeff Boyce on 15 Jan 2010 18:55
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" <forest8(a)discussions.microsoft.com> wrote in message
news:EC4D0A43-3DA2-4331-BF39-0C6A8F1D5B9A(a)microsoft.com...
> 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.
>
>


From: forest8 on 15 Jan 2010 23:37
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
> <forest8(a)discussions.microsoft.com> 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]
> .
>
From: John W. Vinson on 16 Jan 2010 00:42
On Fri, 15 Jan 2010 20:37:04 -0800, forest8
<forest8(a)discussions.microsoft.com> 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]
 |  Next  |  Last
Pages: 1 2 3
Prev: Having problems with Combo Box (Form Control)
Next: String formatting question