too many fields in a table

[Ricoy-Chicago]

Using Access XP. The main back end file (There are 5 BE files ) contains the
table tblStudentInformation with over 40 fields already, ALL the FE files use
some of the fields in this table, I need to add 12 more fields but I know it
is not good to have too many fields in a table. Is there a way to split the
existing table so I have 2 or 3 tables with fewer fields? If I split it, do
have to modify all the FE forms (almost 50 of them), should I just add the
fields and not worry about anything?

thank you for your help.

 

[Golfinray]

You can have 255 fields in a table but you may want to split them for ease of
use, relationships purposes, or normalization purposes. Put them into the
be's where the users need them and they will automatically have access.

 

[pietlinden]

Most experts will tell you that a table holding more than 20-25 fields is
probably an indication that your database is not normalized, and you're
talking about 50+ fields here. You really need to re-evaluate your table
design and, as Golfinray said, consider splitting your data into several
logical tables.

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted viahttp://www.accessmonster.com

I don't think it's so much a question of the strict number of fields,
but it's a reasonable guideline to check for normalization issues.
The real question is whether every column in the table describes the
same entity. If they all describe that same thing, then the design is
correct... Usually, though, if a table is not normalized, it will
contain far too many fields.

 

[Ricoy-Chicago]

Dumb question:
What do you mean by "normalizing"?

 

[Golfinray]

Google database normalization. Basically, it means having keys on every table
and storing each piece of data only once. Your table structure should be
where all tables are properly related to all the other tables they need to
have a relationship to and that you don't store data redundantly. If you do,
somewhere down the line you will have difficulty getting queries, forms, and
reports to give you the data you need.

 

[Ricoy-Chicago]

Oh, I see... everything is already normalized. The main table has all the
student info: Name, address, major, etc. Some fields are linked to another
(The field Major has its own table, etc.). I wanted to add a Financial Aid
Table with FA info. My problem is that if I do it I want these new fields to
available for pre-existing records.

 

[tedmi]

Quite seriously, and with no offense intended, if you don't know the meaning
of "normalizing," I strongly question your ability to determine if
"everything is already normalized."