Record too Large

[Lisa]

Thanks for responding Doug. Your suggestion works except now it says
property value too large when I get close to the end of my table.
There are 150 fields and most of them are set to number. Why would I
be running out of room now?

Should I be limiting the number of fields in each table? Is there
somewhere to get immediate help vs. posting here.

Does anyone know how to help?

Thanks alot!

 

[John W. Vinson]

Thanks for responding Doug. Your suggestion works except now it says
property value too large when I get close to the end of my table.
There are 150 fields and most of them are set to number. Why would I
be running out of room now?

Should I be limiting the number of fields in each table? Is there
somewhere to get immediate help vs. posting here.

YES, you should certainly have fewer than 150 fields!! Ouch.

Any individual record is limited to 2000 bytes actually occupied. A Number...
Long Integer takes up 4 bytes, a Double Float or Date takes 8, a Text field
takes up as many characters as you type, and so on.

I have needed as many as 60 fields in a table. If you have 150 numbers, I
strongly suspect that you are "committing spreadsheet" - storing a one-to-many
relationship in each record. What are some of your fieldnames? Are you perhaps
storing DATA in fieldnames (e.g. fields named [Sales - January], [Sales -
February] and the like)?


John W. Vinson [MVP]

 

[Lisa]

Thanks for responding Doug. Your suggestion works except now it says
property value too large when I get close to the end of my table.
There are 150 fields and most of them are set to number. Why would I
be running out of room now?

Should I be limiting the number of fields in each table? Is there
somewhere to get immediate help vs. posting here.

YES, you should certainly have fewer than 150 fields!! Ouch.

Any individual record is limited to 2000 bytes actually occupied. A Number...
Long Integer takes up 4 bytes, a Double Float or Date takes 8, a Text field
takes up as many characters as you type, and so on.

I have needed as many as 60 fields in a table. If you have 150 numbers, I
strongly suspect that you are "committing spreadsheet" - storing a one-to-many
relationship in each record. What are some of your fieldnames? Are you perhaps
storing DATA in fieldnames (e.g. fields named [Sales - January], [Sales -
February] and the like)?

John W. Vinson [MVP]

Well its quite extensive. I have 22 different pages with 3 tabs on
each page to answer questions in a drop down. So the field names are
things like swallgoals1, swallowgoals2, swallgoals3. The person can
choose up to 3 or 4 items so I had to make a separate field for every
potential answer. I was converting everything to a number as was
suggested to me yesterday. Now I am trying to shorten my field names
but it wont let me do anything without saying property value too
large. Make sense?

 

[John W. Vinson]

Well its quite extensive. I have 22 different pages with 3 tabs on
each page to answer questions in a drop down. So the field names are
things like swallgoals1, swallowgoals2, swallgoals3. The person can
choose up to 3 or 4 items so I had to make a separate field for every
potential answer. I was converting everything to a number as was
suggested to me yesterday. Now I am trying to shorten my field names
but it wont let me do anything without saying property value too
large. Make sense?

In a spreadsheet it does, but not in a relational database. This is a very
common trap for people designing surveys or questionnaires. Instead of one
FIELD per answer, you should use another table, with one RECORD per answer!

Duane Hookum has an excellent sample database for surveys at

http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At Your Survey 2000'

I'd suggest that you download it, study it, and use it as the basis for your
questionnaire. It will be a LOT more flexible - with the "wide-flat" design
you have now you'll have a really difficult time adding new questions,
changing the number of answers to a question, etc.

John W. Vinson [MVP]

 

[Lisa]

In a spreadsheet it does, but not in a relational database. This is a very
common trap for people designing surveys or questionnaires. Instead of one
FIELD per answer, you should use another table, with one RECORD per answer!

Duane Hookum has an excellent sample database for surveys at

http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At%2...

I'd suggest that you download it, study it, and use it as the basis for your
questionnaire. It will be a LOT more flexible - with the "wide-flat" design
you have now you'll have a really difficult time adding new questions,
changing the number of answers to a question, etc.

John W. Vinson [MVP]

I understand what you are saying but this really isn't a
questionnaire. Its a database that stores patients and their medical
problems. They wanted something designed that was user friendly so a
staff member could click a data entry screen, pick the problem area,
and then choose the options that are in the drop down that apply to
the patient. Once my tables are done I wont be adding anymore
questions. Its just hard because there are so many pieces to this.

Thanks alot for responding to my problem.

 

[John W. Vinson]

I understand what you are saying but this really isn't a
questionnaire. Its a database that stores patients and their medical
problems. They wanted something designed that was user friendly so a
staff member could click a data entry screen, pick the problem area,
and then choose the options that are in the drop down that apply to
the patient. Once my tables are done I wont be adding anymore
questions. Its just hard because there are so many pieces to this.

My objections to the wide-flat, one field per option still apply. You can come
up with a design which is both normalized (say three fields per record -
PatientID, Option, Value) and user-friendly (using appropriate forms and
subforms). If you're pushing the Record Too Large limit you're in *REAL*
danger, because the limit applies only when data is actually added to the
table in a way that exceeds the limit; you can test thousands of records with
no problem, but a verbose user can come along and blow your application out of
the water.

John W. Vinson [MVP]

 

[Lisa]

My objections to the wide-flat, one field per option still apply. You can come
up with a design which is both normalized (say three fields per record -
PatientID, Option, Value) and user-friendly (using appropriate forms and
subforms). If you're pushing the Record Too Large limit you're in *REAL*
danger, because the limit applies only when data is actually added to the
table in a way that exceeds the limit; you can test thousands of records with
no problem, but a verbose user can come along and blow your application out of
the water.

John W. Vinson [MVP]

I thought that Memo fields didnt take up much space in a record. That
is really the only field that someone can type in and become very
wordy. Other than that all other fields are numbers. Do you still
not think this will work for me?

 

[John W. Vinson]

I thought that Memo fields didnt take up much space in a record. That
is really the only field that someone can type in and become very
wordy. Other than that all other fields are numbers. Do you still
not think this will work for me?

Memo takes up 16 bytes, regardless of the amount of text.

The fact of the matter is that you ARE getting the "record too large" error.
You're getting it because you *have too many fields*.

John W. Vinson [MVP]