Too many fields defined 1

[StellasGrandpa]

I have an Access97 table that contains approximately 240 fields, which has been functioning just fine. When I changed the data type of several of these fields from Date/Time to Text, and then attempted to save this file, I got, "Too many fields defined. You tried to perform an operation that involves more than 255 fields. Reduce the number of fields, and then try the operation again." I let the table go unsaved, and opened it again, and changed a single field, and got the same message. Next I copied just the structure of that table to a new table name, opened it changed one field and saved it OK. But when I continued down through the fields attempting to save each change as I went, I still got that error. Any ideas?

Thanks, Gary

 

[ghubbell]

Hi Gary,
I cannot say I've ever seen an error message like that before. One thing I will pose to you is: Is there any way you could break down the table? If you run the table through the "Table Analyzer" (Tools-Analyze-Table), does it suggest anything to you? I tend to suspect that a table with as many fields as yours could probably be split, which will improve the performance of your Db, probably reduce upkeep and probably better follow the "rules" of relational database design. Give it a go and keep everyone posted on your findings. Good luck, Gord
ghubbell@total.net

 

[StellasGrandpa]

Thanks for your reply.
I finally got everything changed by simply opening a copy of the table (struct only), changing on the copy, saving it as I went along, and finally appending the data back into the changed table. (Believe it or not, virtually all of those some 240 odd fields are needed. viz. they are all data items related to a patient.)

 

[Peps]

Hi Guys,

For the future, there is an easy way of getting around this
error. Let's say that the table which containes the 240
fields is called "Customers". Make a copy of this table (including data) and call it "Customers1". This new table
can be edited without any problems. Change the name of "Customers" to "Customers11" (for exampel) and then
change the name of the new table "Customers1" to "Customers". If your happy with the changes made and every thing works ok, delete "Customers11".

Peps

 

[JerryDennison]

I strongly suspect that your db is not normalized properly. I have no doubt that you need all the information you're capturing, but I do believe that you are putting most of it in the wrong table. I'll give you an example. I'm going to assume that many of the fields are direct responses to health related questions, i.e. various codes for types of services performed, medications, procedures, etc... In all likelyhood, a patient will have many such records. Possibly, one for each visit. If this is the case, then many of these fields are left blank. Only used if/when that particular procedure is performed.

Correct me if I'm wrong, but in all my experience, I've never seen a case where anyone needed to collect 240 different specific pieces of information that apply to one entity. Usually, when someone builds a table with that many fields they misunderstand what an entity and it's descriptors really is. They have fields that, on first blush, appear to be descriptors of the entity but actually describe a dependent entity. An example would be an office visit, a prescription, a procedure, all of which HAPPEN to a patient but are not really a part of that patient.

Think of it this way: An entity is something you can hold in your hand(literally or figuratively), if your hand were big enough, that cannot be broken down into individual components without damaging it. A descriptor is an attribute that the entity has that is not likely to change and is unique to the entity. In your case, an entity is a patient, descriptors would be name, current address, SSN, DOB, etc. Something that DOES NOT describe the patient is their temperature, medication they're on, the doctor they're seeing today, a procedure performed on them while at the hospital, etc....

 

[ElizaJ]

Related question:

So then, where is the counter? I'd like to be able to get a count on the number of fields in a table. Is there a simple way to do this?

Thanks,
Eliza J.