Error message help

[Guest]

Let me start by saying that I am a noob at Access and am using a database
that was created by someone else. My question is I have 220 fields in a
table and I tried adding some additional ones. When I do this I get an error
message saying "property value too large" and tells me that I would get this
message if I either (1) have exceeded the max # of columns allowed in a table
or the maximum # of locks for a single file or (2) the indexed property of a
field was changed from YES ( DUPILICATES OK) to YES ( NO DUPLICATES) when
duplicate data is already present in the table. Of course it does not save
my addition. I thought I had up to 255 fields that I could use and I know
that this is a new field name that I am adding. Any suggestions?

 

[Terry Kreft]

If you have 220 fields in the table it is almost certainly badly designed.

The prope way to handle this is to normalise the tables, this will almost
certainly reduce the number of fields in this particular table and reduce
the amount of data repetition.

The alternative (bad method) would be to create another table, key it on the
same fields as your existing table and establish a one to one relationship
between the two tables.

 

[Guest]

I figured as such that it was not designed well. This database is used to
track a mentoring program for a Juvenile Parole office. We keep track of the
youths info, the mentor's info, and a way to track the contact history
between the two. We track the mentor match for up to two years and the bulk
of the fields are month, day, year, and type of contact. Each month (ie
month 1, month 2 ...) have have a spot to document the month, the day, the
year and type of contact. So for 24 months there are 48 fields and this is
just for the contacts between the youth and mentor. There are 48 more for
the contact between the program staff and the mentor. There is half of the
fields in just the contact portion.

 

[Douglas J Steele]

Each of those 24 months should be a single row in a second table.

 

[Guest]

Any "one-to-many" data relationship should be broken out into separate tables.

 

[Terry Kreft]

Doug and Dennis have already given you the answer on how these should be
handled properly.

Have a search for normalisation on the web and you'll find the rules which
should be applied to a database in order to make it relational.

It may not be of much use to you in this situation but is essential reading
if you want to carry on with database development (or even if you don't want
to but are forced to <go>)