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 |