Too Many Fields??

[Shaun Allan]

I've a database set up to record refinery equipment for
risk based inspection. I've reached the limit, it seems,
of 255 fields. Thankfully, that's going to have to be
enough, but I need to change a number field (well, there's
about 5 in the set) to a text field so more than one value
can be entered in the same field. WHenever I try to do
this, it tells me there's Too Many Fields, but I'm not
trying to increase the number of fields, just alter
existing ones. Is there a way round this?

 

[Cheryl Fischer]

You can compact and repair the database and see if that allows you a little
more "leeway".

However, it appears from the brief description of your database that you are
building an application with highly un-normalized data which is going to
give you much grief down the road when you need to build queries and reports
and otherwise tap into the database for fast and accurate return of
information.

Here is a link which you may want to review regarding normalizing data:

Normalizing Your Database: First Normal Form
http://databases.about.com/library/weekly/aa081901a.htm

And a suggested book on database design:

"Database Design for Mere Mortals" Amazon link
http://tinyurl.com/2c6bp

 

[Roger Carlson]

I'm sorry, but you are stuck. Once you reach the field limit, you can neither add nor modify fields.

But your problems go much deeper than this. You are breaking all of the rules of relational database design. You are "comitting spreadsheet", that is, using your database as if it were a spreadsheet. It's not.

The real solution to your problem is to completely redesign your database according to a group of design principles called Normalization. Normalization is a way of grouping related data into smaller tables to reduce redundant data. This reduces the size of the database as well as allowing you to have much more confidence in its accuracy.

Most trade press books have some reference to Normalization theory, but I'd suggest two in particular:
"Access Database Design & Programming" By Steven Roman (O'Reilly)
"Database Design for Mere Mortals : A Hands-On Guide to Relational Database Design" By Michael J. Hernandez

You might also want to download: reldes00.exe, Microsoft's Designing a Database -- Understanding Relational Design Whitepaper. You can find it here: http://www.rogersaccesslibrary.com/knowledge.html

If you continue with this design, you will have a never ending series of problems. Proper design can eliminate virtually all of them.