Too many fields....

[konquistador]

Hi Guys, I am Creating columns in a table in Design Mode. First i created a table with upto 235 columns and i was able to save it. Now, i had to modify the table and delete some of old columns and create new columns. But Now, my table is not saving and throwing the error: " Too many fields" even if my total fields are 215 or so.? All the columns are text, date/time or memo fields.
Any ideas please?

Thanks a Lot,
Kon

 

[DJkarl]

Access doesn't always do so well with table changes, you may need to rebuild from scratch...and while you're doing that you should look at redesigning your database. There are very few scenarios that come to mind where 100+ columns are actually needed in a table, and even fewer where 200+ columns are needed. You should look at normalization, tons of examples here on the forum. It takes a little longer to setup than a flat table, but the payoff later on makes it worth while.

 

[GaryPanic]

well put...
i have only seen this requirement once or twice in x years - and even that could of been split into sectional tables

- i have been informed that tables should be long and thin , not short and wide

 

[konquistador]

thanks

Hi thanks so much for your reply. Any easy way to build it from scratch. I created this table by getting data from three tables(using a query). ...Any easy way to do this or do i have to type in all the column names again?
Meanwhile i will also work on your other solution.
Thanks,
Kon

 

[DJkarl]

Hi thanks so much for your reply. Any easy way to build it from scratch. I created this table by getting data from three tables(using a query). ...Any easy way to do this or do i have to type in all the column names again?
Meanwhile i will also work on your other solution.
Thanks,
Kon

Well if all your data is in one query couldn't you just make that a Make table query? Or maybe I'm misunderstanding you.

 

[trucktime]

Too many columns

If you compact and repair the database you should be able to add columns again up to 255.

However, I agree with the comments above that you may have to review your
design.

Good luck.

 

[Mike375]

Kon

The main table I have has 245 fields and the limitation is if I change a field type then it will only allow one field to be changed at a time. In fact if I change two fields I usually need to close the data base and reopen to change the second field.

As the others have said it is not common to have tables with so many fields.

You should check the fields in your table and look for groups of fields that are multiple instances of the same type of data and especially where the number of instances of that data being required is an unknown. An example would be different fields where details of appointments are stored. For some people you will have had no appointments but for other people you will have had three appointments.

If data such as "appointments" are stored as part of the persons record then you either have to replace each of the appointment details for each time an appointment is made or need multiple fields such as [Date1] [Time1] [Comments1] followed by [Date2] [Time2] [Comments2] etc. As you can see you won't know how many fields to have for appointments and you might need to exceed the 255 field maximum. Also makes it difficult if you want to search on a type of data or do calculations and because all of the data in question won't be stored in one field.

 

[Pat Hartman]

You've come a long way Mike