Number of Access fields

[kerryh]

My Access table is refusing to take more than 191 fields. I tried deleting
some fields that weren't in use, but I still can't add on more fields, or
even replace deleted fields. Any ideas?

 

[John W. Vinson]

My Access table is refusing to take more than 191 fields. I tried deleting
some fields that weren't in use, but I still can't add on more fields, or
even replace deleted fields. Any ideas?

Two suggestions:

1. Access tables are limited to 255 fields, but each time you change a field,
or delete one field and add another, it "uses up" one of the 255 slots. You
can recover these by using Tools... Database Utilites... Compact and Repair
(in 2003 or earlier, it's under the Office button in 2007).

2. 191 fields is at least 150 fields too many for ANY properly normalized
table. Unless you are intentionally using Access to create a data warehouse (a
task better done in SQL/Server), your table design is almost certainly WRONG.
I suspect you're "committing spreadsheet", a very common venial sin for which
the penance is a requirement to study normalization! Do you have some fields
with names like January, February, March; or Question1, Question2, Question3
and so on? If so, you are embedding one to many relationships within a single
record, which should be split out into two TABLES in a one to many
relationship. "Fields are expensive, records are cheap"!

 

[kerryh]

Thank you John,

Your first suggestion has fixed the problem.

With regard to your second suggestion, I will look into the design. This
particular table holds all the possible competencies that can be achieved in
a firefighting organisation. I have another table with member information
(rank etc), and another with brigade info.

Thanks again, Kerry

 

[Klatuu]

A better design would involve 3 tables.
1. Your member table with demographic info on the members.
2. A table with a list of competencies with one record for each competency
3. A junction table that shows the competencies for each member. It need
contain only the primary key of the the member table as a foreign key and the
primary key of the competency table as a foreign key. If there is a scoring
or rating system for the member's competency, then you would want an
additional field to show that.

 

[John W. Vinson]

With regard to your second suggestion, I will look into the design. This
particular table holds all the possible competencies that can be achieved in
a firefighting organisation.

So... if you need to add another competency, you need to restructure your
table, rewrite all your queries, redesign all your forms, redesign all your
reports...!? Ouch.

Read Dave's suggestion. It's a much better solution.

 

[Pete D.]

This sounds like a spreadsheet. What will you do when addtional
competencies are added/changed. Think this way instead

fldItemKey fildCompetenciesDescription
1 EMT Level 1
2 Ladder Qualified
3 EMT Level 2