A) 140 fields in a record -- OK ?? --- B) Do empty fields use disk space?

[MyEmailList]

1 - I've got a record with 140 fields - should I expect any problems
because of so many fields in each record?

2 - How does Access treat fields in which the user enters no data? if
it is a text field with 50 charcters of space allowed... does Access
see that it is an empty field... or a field in which on a few
characters are entered... and compact it... or does it simply save 50
bytes for the field even though none or only some of them are used?

thanks for any help.

Mel

 

[Guest]

1 - I've got a record with 140 fields - should I expect any problems
because of so many fields in each record?
140 fields sounds like a spreadsheet and not a normal database structure.
The number of fields should not be a problem but if it is spreadsheet
methodology you will have problems when you need to rollup the data.

2 - How does Access treat fields in which the user enters no data? if it is a text field with 50 charcters of space allowed... does Access see that it is an empty field... or a field in which on a few characters are entered... and compact it... or does it simply save 50 bytes for the field even though none or only some of them are used?

My understanding is that it reserves the total space but Access can be up to
2Gb in the newer versions.

 

[Douglas J. Steele]

1) I can't imagine a properly normalized table that would require 140
fields. 30 fields is considered a large number for a properly normalized
table. Access is capable of handling that many fields (it can handle up to
255 fields), but yes, you could run into problems if you've got a lot of
text fields, and the total storage requirement for a row is in excess of
2000 bytes.

2) Access takes as much space as is required for the amount of text being
stored. This relates to what I said above. Access won't complain at all if
you create a table with 10 fields of 255 bytes each, despite the fact that
that exceeds the maximum size allowed for a row. It's not until you try and
store the 2001st byte in that row that you'll get an error.

 

[Joseph Meehan]

1 - I've got a record with 140 fields - should I expect any problems
because of so many fields in each record?

2 - How does Access treat fields in which the user enters no data? if
it is a text field with 50 charcters of space allowed... does Access
see that it is an empty field... or a field in which on a few
characters are entered... and compact it... or does it simply save 50
bytes for the field even though none or only some of them are used?

thanks for any help.

Mel

As noted 140 fields is almost certain to mean bad design and that will
waste a lot of space cause slow operation and make many tasks more
difficult. The key is to normalize them

As an example consider a list of students and their test results.
You would normally have one table for the students. First Name, Last
Name, Address, Phone number

Next you might have the names of available classes in a table
From there you might list each class by subject and that list (table)
Each student would have a connection (relationship) to each class they
take.

Follow that up with another table for test scores related to the class
and the student.

The thing you don't want is a table with one record for each student and
a filed for each class they take and another for each score they get. To do
that you might need 140 fields. But it would be a very bad design, unless
you were truing to use a spreadsheet program.

 

[Joseph Meehan]

As noted 140 fields is almost certain to mean bad design and that
will waste a lot of space cause slow operation and make many tasks
more difficult. The key is to normalize them

As an example consider a list of students and their test results.
You would normally have one table for the students. First Name,
Last Name, Address, Phone number

Next you might have the names of available classes in a table
From there you might list each class by subject and that list
(table) Each student would have a connection (relationship) to
each class they take.

Follow that up with another table for test scores related to the
class and the student.

The thing you don't want is a table with one record for each
student and a filed for each class they take and another for each
score they get. To do that you might need 140 fields. But it would be
a very bad design, unless you were truing to use a spreadsheet
program.

Sorry I intended to add one thing. It sounds a little like you may be
trying to store information that may be best stored in a memo field. This
would include large amounts of related text (over 255 characters) and you
are using multiple fields since they will not fit in one. Is that possible.

 

[Albert D. Kallal]

My understanding is that it reserves the total space but Access can be up

to
2Gb in the newer versions.

No, ms-access does not pre-allocate the space for text fields.

If you define a field with 5,chars or 255 chars, it takes up the same space.
Ms-access does NOT pad, or store the extra length.....

The fields are thus variable length, and not fixed length like the "older"
databases that were on pc's many years ago (like Dbase, FoxPro etc).

So, if you define 20 fields of 5 chars, or 20 fields of 255 chars..the
spaced used is the same. (ms-access does not save, or store the extra space
in a field, but only the characters actually typed in).

 

[MyEmailList]

See my post "140 fields in a single Record - or What ?"

thanks for the help