Table problem

I have a table which contains 245 fields. I need to change properties of some
fields and get the message "property value is too large". I couldn't save any
change. I try create a new table in different name and paste all the fields
to new table. It only let me paste about 190 fields into the new table, then
the same message comes up when I save the new table. I did compact and repair
the database and didn't get any help. There is any way to solve this problem
without editing registry entry for the local computer (I need to share the
database with other user).

Why do you have 245 fields?

A properly designed table should have 20 or 30 fields at the very most. 10
to 20 is pretty typical.

I would suggest you normalize your table. It sounds like you are trying to
create a spreadsheet, not a relational database. If that is true, then you
should be using a spreadsheet program, like Excel.

Feel free to post details about your table, and we can help you get it
normalized.


--
Rick B



"Redwood" wrote in message
...
I have a table which contains 245 fields. I need to change properties of
some
fields and get the message "property value is too large". I couldn't save
any
change. I try create a new table in different name and paste all the
fields
to new table. It only let me paste about 190 fields into the new table,
then
the same message comes up when I save the new table. I did compact and
repair
the database and didn't get any help. There is any way to solve this
problem
without editing registry entry for the local computer (I need to share the
database with other user).

Can you justify the need for more than about 30 fields?

--
Duane Hookom
MS Access MVP
--

"Redwood" wrote in message
...
I have a table which contains 245 fields. I need to change properties of
some
fields and get the message "property value is too large". I couldn't save
any
change. I try create a new table in different name and paste all the
fields
to new table. It only let me paste about 190 fields into the new table,
then
the same message comes up when I save the new table. I did compact and
repair
the database and didn't get any help. There is any way to solve this
problem
without editing registry entry for the local computer (I need to share the
database with other user).

Hi Rick,

I have to put all releted fields into this table for a project and I have
several other tables for this project. If I put these fields into two
tables, it may cause unbalance records for these two table. I have several
forms based on this table. If I use two tables, it is hard to keep same
number of records under visit date which from other table.
I thought I could create a new table by using copy paste, but it didn't
work. there are about sixty blank fields left and tell me the property value
is too large. The maximum fields for a table are 255. Why?



"Rick B" wrote:

Why do you have 245 fields?

A properly designed table should have 20 or 30 fields at the very most. 10
to 20 is pretty typical.

I would suggest you normalize your table. It sounds like you are trying to
create a spreadsheet, not a relational database. If that is true, then you
should be using a spreadsheet program, like Excel.

Feel free to post details about your table, and we can help you get it
normalized.


--
Rick B



"Redwood" wrote in message
...
I have a table which contains 245 fields. I need to change properties of
some
fields and get the message "property value is too large". I couldn't save
any
change. I try create a new table in different name and paste all the
fields
to new table. It only let me paste about 190 fields into the new table,
then
the same message comes up when I save the new table. I did compact and
repair
the database and didn't get any help. There is any way to solve this
problem
without editing registry entry for the local computer (I need to share the
database with other user).

You do not explain here why you need one table. What do you mean you can't
keep the same number of records under visit date?

You need to tell us what kind of data you are storing and we can help you
normalize.

There is NO VALID reason to have 245 fields in one table.


--
Rick B



"Redwood" wrote in message
...
Hi Rick,

I have to put all releted fields into this table for a project and I have
several other tables for this project. If I put these fields into two
tables, it may cause unbalance records for these two table. I have several
forms based on this table. If I use two tables, it is hard to keep same
number of records under visit date which from other table.
I thought I could create a new table by using copy paste, but it didn't
work. there are about sixty blank fields left and tell me the property
value
is too large. The maximum fields for a table are 255. Why?



"Rick B" wrote:

Why do you have 245 fields?

A properly designed table should have 20 or 30 fields at the very most.
10
to 20 is pretty typical.

I would suggest you normalize your table. It sounds like you are trying
to
create a spreadsheet, not a relational database. If that is true, then
you
should be using a spreadsheet program, like Excel.

Feel free to post details about your table, and we can help you get it
normalized.


--
Rick B



"Redwood" wrote in message
...
I have a table which contains 245 fields. I need to change properties of
some
fields and get the message "property value is too large". I couldn't
save
any
change. I try create a new table in different name and paste all the
fields
to new table. It only let me paste about 190 fields into the new table,
then
the same message comes up when I save the new table. I did compact and
repair
the database and didn't get any help. There is any way to solve this
problem
without editing registry entry for the local computer (I need to share
the
database with other user).

The fields in most of major tables in the database are over 100. This
database handles several projects. This table is for new project. It would be
better for keeping same with other projects.

"Duane Hookom" wrote:

Can you justify the need for more than about 30 fields?

--
Duane Hookom
MS Access MVP
--

"Redwood" wrote in message
...
I have a table which contains 245 fields. I need to change properties of
some
fields and get the message "property value is too large". I couldn't save
any
change. I try create a new table in different name and paste all the
fields
to new table. It only let me paste about 190 fields into the new table,
then
the same message comes up when I save the new table. I did compact and
repair
the database and didn't get any help. There is any way to solve this
problem
without editing registry entry for the local computer (I need to share the
database with other user).

Rick isn't suggesting that you split your fields and place them in two
tables with a 1-1 relationship. He is suggesting that you normalize the
table which may result in two tables or it may result in more. In any event
the tables will be related 1-many. That means that one row in tableA will
have multiple rows in tableB. A very simple and common example is an order.
TableA is the OrderHeader. It contains information about the order such as
who placed it and when. TableB is the OrderDetails. It contains the
OrderHeaderID so it can be linked to its parent record and the items that
were ordered. So, if 3 items were ordered. There will be 1 row in the
OrderHeader and three related rows in the OrderDetails. If 100 items were
ordered, there will be 100 related rows in OrderDetails.

Tell us a little more about your data and we can offer some ideas on how to
proceed.

"Redwood" wrote in message
...
Hi Rick,

I have to put all releted fields into this table for a project and I have
several other tables for this project. If I put these fields into two
tables, it may cause unbalance records for these two table. I have several
forms based on this table. If I use two tables, it is hard to keep same
number of records under visit date which from other table.
I thought I could create a new table by using copy paste, but it didn't
work. there are about sixty blank fields left and tell me the property
value
is too large. The maximum fields for a table are 255. Why?



"Rick B" wrote:

Why do you have 245 fields?

A properly designed table should have 20 or 30 fields at the very most.
10
to 20 is pretty typical.

I would suggest you normalize your table. It sounds like you are trying
to
create a spreadsheet, not a relational database. If that is true, then
you
should be using a spreadsheet program, like Excel.

Feel free to post details about your table, and we can help you get it
normalized.


--
Rick B



"Redwood" wrote in message
...
I have a table which contains 245 fields. I need to change properties of
some
fields and get the message "property value is too large". I couldn't
save
any
change. I try create a new table in different name and paste all the
fields
to new table. It only let me paste about 190 fields into the new table,
then
the same message comes up when I save the new table. I did compact and
repair
the database and didn't get any help. There is any way to solve this
problem
without editing registry entry for the local computer (I need to share
the
database with other user).

The database handles several projects. this table is for new project. the
table based on the visitID which from visit table. The visitID is no
duplicate. when I enter data from a form, the form has function to look up
visitID. if the visitID already in this table. the data will be added into
this record. if the visitID not in this table, then will create new record
for adding data. This table also has own ID which will be used for audit
trial (another database).
All the field I put in this table are releted the visitID. there are
several similar tables for other projects. but those tables don't have so
many fields

"Pat Hartman(MVP)" wrote:

Rick isn't suggesting that you split your fields and place them in two
tables with a 1-1 relationship. He is suggesting that you normalize the
table which may result in two tables or it may result in more. In any event
the tables will be related 1-many. That means that one row in tableA will
have multiple rows in tableB. A very simple and common example is an order.
TableA is the OrderHeader. It contains information about the order such as
who placed it and when. TableB is the OrderDetails. It contains the
OrderHeaderID so it can be linked to its parent record and the items that
were ordered. So, if 3 items were ordered. There will be 1 row in the
OrderHeader and three related rows in the OrderDetails. If 100 items were
ordered, there will be 100 related rows in OrderDetails.

Tell us a little more about your data and we can offer some ideas on how to
proceed.

"Redwood" wrote in message
...
Hi Rick,

I have to put all releted fields into this table for a project and I have
several other tables for this project. If I put these fields into two
tables, it may cause unbalance records for these two table. I have several
forms based on this table. If I use two tables, it is hard to keep same
number of records under visit date which from other table.
I thought I could create a new table by using copy paste, but it didn't
work. there are about sixty blank fields left and tell me the property
value
is too large. The maximum fields for a table are 255. Why?



"Rick B" wrote:

Why do you have 245 fields?

A properly designed table should have 20 or 30 fields at the very most.
10
to 20 is pretty typical.

I would suggest you normalize your table. It sounds like you are trying
to
create a spreadsheet, not a relational database. If that is true, then
you
should be using a spreadsheet program, like Excel.

Feel free to post details about your table, and we can help you get it
normalized.


--
Rick B



"Redwood" wrote in message
...
I have a table which contains 245 fields. I need to change properties of
some
fields and get the message "property value is too large". I couldn't
save
any
change. I try create a new table in different name and paste all the
fields
to new table. It only let me paste about 190 fields into the new table,
then
the same message comes up when I save the new table. I did compact and
repair
the database and didn't get any help. There is any way to solve this
problem
without editing registry entry for the local computer (I need to share
the
database with other user).

"better" for whom?

Access is a relational database. If you want to get the best use of it,
your data will need to be in a format that recognizes this (i.e., is
well-normalized).

As others have pointed out, more than 20 or 30 fields in a single table is
usually a symptom... you may have committed spreadsheet on Access.

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Redwood" wrote in message
...
The fields in most of major tables in the database are over 100. This
database handles several projects. This table is for new project. It would
be
better for keeping same with other projects.

"Duane Hookom" wrote:

Can you justify the need for more than about 30 fields?

--
Duane Hookom
MS Access MVP
--

"Redwood" wrote in message
...
I have a table which contains 245 fields. I need to change properties of
some
fields and get the message "property value is too large". I couldn't
save
any
change. I try create a new table in different name and paste all the
fields
to new table. It only let me paste about 190 fields into the new table,
then
the same message comes up when I save the new table. I did compact and
repair
the database and didn't get any help. There is any way to solve this
problem
without editing registry entry for the local computer (I need to share
the
database with other user).

On Wed, 29 Mar 2006 13:33:03 -0800, Redwood
wrote:

The database handles several projects. this table is for new project. the
table based on the visitID which from visit table. The visitID is no
duplicate.

It's not that VisitID is a duplicate; the problem is that you are
almost certainly storing a one-to-many relationship IN EVERY RECORD,
by storing data (of some sort, we cannot see your structure so we
don't know the details) IN FIELDNAMES.

If you have fields named (for example) Checkpoint1Date,
Checkpoint1Attained, Checkpoint1Comments, Checkpoint2Date,
Checkpoint2Attained, Checkpoint2Comments, etc.etc., then your table
structure *is simply wrong*.

Could you post the names of ten or twelve of these far-too-many
fields? We can very likely get you into a new mindset which will make
Access work much more easily for you!

John W. Vinson[MVP]