Access table field limits

[Guest]

I am creating a table and have 173 total fields. When I create another field
I get and error "too many fields defined". I am a little confused because the
limit is 255.

 

[Guest]

If you changed the design of any of the fields, for example going from text
to number, you burn up one of the 255 columns. There's also a limit of about
2000 characters per record. I've also wondered if things like lookup fields
or subdatasheets might cut into the number. I'll never find out though,
because I've only seen two tables where there was a legitimate reason to have
over 100 fields. One was for a coroner's office and let's face it, death is a
singular event where repeating data isn't likely.

If you want that many fields in a table, there's a very, very good chance
that you have a normalization problem. If it's for importing an Excel
spreadsheet with that many fields, consider linking to that spreadsheet then
properly split out the data to an appropriate number of tables.

 

[Joseph Meehan]

I am creating a table and have 173 total fields. When I create another
field I get and error "too many fields defined". I am a little
confused because the limit is 255.

If you compact the database you likely will be able to add more fields.

I would also say that it is about 98.97% certain that you have a poor
table design. You likely should have two or more tables for this
information. This is not Excel you are working with.

The process of correcting this kind of thing is call normalization.
That means having parent - child tables.

For example you might have a table for families. You would have fields
for the father's name (first last etc should each have its own field) and
then more for mom and then more for kids, maybe 12 or more sets for the
kids, addresses phone numbers etc. All the kids should have their own
table of kids linked to their parents in the parent table. Now when you
get adopted kids and divorces etc, it can get to be real fun.

 

[Guest]

Thank you for the suggestion. I will try compacting. Let ma ask this. Can a
make 1 form with info from 2 tables?

 

[John Vinson]

Can a make 1 form with info from 2 tables?

Certainly; use a Form with a Subform, one for each (related) table.

Take a look at the Orders form in the Northwind sample database. It
contains information from at least four tables.

John W. Vinson[MVP]