If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
table with too many fields
In a new data base, I have imported an excel table with about 180 fields and
25 records. When I change the data type property in the design mode for the blank fields from text to number double and save there is an error message too many definitions, cannot save; what is the limit to the max number of fields in a table? If I split the table into two or three and create a query with the 180 or 200 fields, will it accept so many fields? |
#2
|
|||
|
|||
table with too many fields
255 is the maximum number of fields in a table; however, it often runs out
before them. For example your changing the datatype of a field used up one or more of the 255. Then there is the maximum number of characters or bytes in a record which is 2000 not counting OLE and Memo fields. You probably do need more than 1 table. If the data is properly normalized, there's a good chance that you could even need more than 3 or even 5. It's rare to have more than 30 or 40 fields in a table within a properly designed database. It's possible to link that many fields in 2 or 3 tables in a query. They also run out around 255 fields as do forms and reports. You'll want to create a primary key, such as an autonumber, and put this field in each table. Then you can do a 1-to-1 join between the tables. -- Jerry Whittle Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "r subrahmanian" wrote: In a new data base, I have imported an excel table with about 180 fields and 25 records. When I change the data type property in the design mode for the blank fields from text to number double and save there is an error message too many definitions, cannot save; what is the limit to the max number of fields in a table? If I split the table into two or three and create a query with the 180 or 200 fields, will it accept so many fields? |
#3
|
|||
|
|||
table with too many fields
On Fri, 17 Feb 2006 18:20:30 -0800, "r subrahmanian"
wrote: In a new data base, I have imported an excel table with about 180 fields and 25 records. When I change the data type property in the design mode for the blank fields from text to number double and save there is an error message too many definitions, cannot save; what is the limit to the max number of fields in a table? If I split the table into two or three and create a query with the 180 or 200 fields, will it accept so many fields? As Jerry says, this table cries out for normalization. If it's a typical spreadsheet, it very likely has repeating fields (such as fields named January, February, March or some other such series). As a *temporary* expedient prior to moving the data into a set of normalized tables, you should be able to get around the 255-field limit you're hitting. Every time you *change* a field type it "uses up" one of the 255 slots - but you can recover them by using Tools... Database Utilities... Compact and Repair Database. Try changing ten or twenty fields at a time, compacting after each. Or, build an empty table with the desired fieldtypes, and import from Excel directly into this table, rather than importing, letting Access guess at the fieldtype, and fixing it up afterwards. John W. Vinson[MVP] |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Table design problem? | Melissa | Database Design | 29 | November 18th, 2005 04:14 AM |
Multiple Options Group | Patty Stoddard | Using Forms | 19 | August 4th, 2005 02:30 PM |
Here's a shocker | Mike Labosh | General Discussion | 2 | October 26th, 2004 05:04 PM |
Complicated Databse w/many relationships | Søren | Database Design | 7 | July 13th, 2004 05:41 AM |
COMPARE THE TWO TABLES | Stefanie | General Discussion | 0 | June 4th, 2004 04:36 PM |