Table problem

[Redwood]

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 B]

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.

 

[Duane Hookom]

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

 

[Redwood]

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]

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.

 

[Redwood]

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.

 

[Pat Hartman\(MVP\)]

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]

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

 

[Jeff Boyce]

"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

 

[John Vinson]

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]

 

[Redwood]

Here are some fields name in this table: BoneGraftEvalSFID(AutoNumber, Long
Integer, Increment, No duplicateds), VisitID(Number,Long Integer),
GraftEvalDate(Date/Time), DateOfMedicalEval(Date/Time), MedicalHEENT(Text,15,
Combo box), MedicalHEENTComm(Text,200), NicotineUseY(Yes/No),
NicotineUseN(Yes/No), LLateralFlexionLumbarDegree(Number, long
Integer),LLateralFlexionLumbarPain(Text,15, Combo Box)),
BridgingAcrossR(text,10,combo box), BridgingAcrossL(Text,10,combo box),
Comments(Memo).
The table has a relationship with Visit table by VisitID, Join type is 1.
Enforce Referential Integrity box has been checked, Relationship type show
one-to-many.
The table structure should be right. I can change some check box fields to
combo box fields to reduce number of fields, but there is not much room for
it (about ten fields).
Thank you very much for help!

 

[Dejan]

It looks you are keeping track of some medical exams or so. For each visit
you collect data on some or all of the following: MedicalHEENT,
MedicalHEENTComm, NicotineUseY, LLateralFlexionLumbarDegree, ........

You can reduce number of fields from 245 to less by splitting data in 3
(three) tables: Visits, VisitParameters, VisitParameters
Visits = info on visit only
VisitParameters = all possible parameters you may expect to see in a visit
VisitParameters = actual parameters found in particular visit

Table structures:


VISITS (VisitID, VistDate, patientID) etc -- all about visit, nothing about
parameters

PARAMETERS (ParamID, Description) = list of all parameters that you may
collect on any given visit. Most of your fields from the original table will
be stored here, as records. Example:

ParamID, Description
--------------------
"MedicalHEENT", "Medical HEENT"
"MedicalHEENTComm", "Comment for Medical HEENT"
"NicotineUseY", "Yes if the patient is nicotine user"
"LLateralFlexionLumbarDegree","Degree of Lateral Lumbar Flexion"
"LLateralFlexionLumbarPain","Presence of pain with LLFL"

You got the idea?

VISIT_PARAMETERS (VisitID, ParamID, Value) = this is the key piece in the
story. For each visit, you enter as many records as you need. Like this:

VisitID, ParamID, Value
-----------------
1, "MedicalHEENT", "37"
1, "MedicalHEENTComm", "very bad, but not life threatening"
1, "LLateralFlexionLumbarDegree","High"
1, "LLateralFlexionLumbarPain", "No"
1, "NicotineUseY","No"
2, "LLateralFlexionLumbarDegree","Medium"
2, "LLateralFlexionLumbarPain", "No"
2, "NicotineUseY","Yes"

So, in Visit=1 we collected 5 parameters and recorded their values
Visit 2 was not so rich, only 3 parameters.

Of course, you may expand this, so for each Parameter in VISIT_PARAMETERS
you may want to provide a lookup (combo box) list of valid values and so on.

In any case, instead of one very wide table, you will have at least three
narrow but long tables. Wide table means many fields, long table means many
records.

There is more room for design improvement, but this should give you an idea.

 

[Redwood]

I need to add this new project into a existing database and have to keep the
structure of this new project is consistent with other project in the
database for future analysis and maintaince.
The access designed to allowed have 255 fields in one table for maximum. If
it only can handle 20 to 30 fields, people will think about it very carefully
before using Access.

 

[Duane Hookom]

If you truly have fields like "BoneGraftEvalSFID" then you are storing data
(BoneGraft) in a field name. BoneGraft should be a value in a field that
stores procedures.

 

[Redwood]

Thank you so much for replying my posts.

I understand what is your mean. Splitting data in two or more table is my
last choise. I have thinked about it before I posts the question.

In the database, I already have patient's table and visit table which store
the information for all projects. This table is only for a new project and
all the fields in this table are parameters on visit for this project. Each
patient has several rows in this table depending on visit times. the visitID
is field to link to visit table and I can use it for creating queries.

The reason I did not try to split the table is that I not sure if it would
cause more trubles in future analysis. I need to think about it.

I have ten forms based on this table. if I split it two tables: table1 and
table2. then form 1-5 would use table1 and form 6-10 would use table2. if a
patient have 5 visits, the visitID should be 1,2, 3, 4, 5, then both table
shoud have 5 rows. but if the paitent only have the informtion in form 1-5
and nothing for form 6-10 for a visit, the data entry persion will only use
form 1-5. so the table2 will nerve have this visitID. that means table1 will
have 5 rows and table2 only has four rows.

I am not sure if there is any truble for creating queries when I need the
information
from both table1 and table2 plus patient table and visit table. I had such
kind problem before.

Thank you very much for your time and help.

 

[Redwood]

I use the "BoneGraftEvalSFID" field for audit trial. In other table also have
a field like this, e.g. "LabEvalID". We have a code to send the information
of change to the audit trial database.

 

[Dejan]

It looks you already have troubles. Big table => 10 data entry forms. With
proposed normalization, you would need only one data entry form. Or, better
to say, set of forms. You would need a master form for each visit and subform
for parameters. In the subform, key entry person would enter as many
parameters as needed.

From experience, it is much easier to query normalized than non normalized
tables. Now, if you want to do simple counts for each parameter, you need
about 200 queries, one for each parameter. With proposed normalized
structure, you would have to create and run only one query. Seems like a
quite diferrence to me. And yes, by adding something like PROJECTS table, you
can keep multiple projects in the same Access database.

In any case, good luck

 

[Pat Hartman\(MVP\)]

I think we are having a communication problem. Access doesn't limit you to
20-30 fields in a normalized table. Good database design simply results in
many fewer columns per table than you have; 20-30 being a suggested
maximum. There are of course exceptions. Your table is not an exception
however. You have created a table that looks like a spreadsheet - wide
(many columns) and short (few rows) whereas a good relational table will be
narrow (few columns) and long (many rows).

If you do some reading on normalization you may be able to understand what
we are all talking about. In your case, I can understand why you are having
difficulty recognizing the changes we are suggesting. Your data isn't an
obvious repeating group. Those are easily identified because they include
suffixed or prefixed names such as JanAmt, FebAmt or Contact1, Contact2.
Yours is more subtle. For example, if you had a rental property and you
needed to track expenses, you would most likely create a column for each
expense type - insurance, property tax, oil, gas, water, trash removal, lawn
maintenance, etc. In reality, this is a repeating group because all items
are expenses. The data should be stored in a table with only four columns -
PropertyID, TransactionDate, ExpenseType, ExpenseAmount. That structure
would allow you to store an infinite number of different expenses. It would
also allow you to add new ExpenseTypes without modifications to your
forms/reports/queries/or tables. So, if you acquired a new property that
had a swimming pool, you could now add pool maintenance, and pool chemicals
as expense types without changing any structure. With the spreadsheet
technique, you would need to add two new columns to the table and change ALL
your reports, queries, and forms. That's the difference between normalized
and unnormalized.

Since you already have data in your database, you would need to create a
number of queries to "normalize" it should you decide to take the offered
advice and normalize. We can offer suggestions on that also.

 

[Redwood]

Soryy for bothering you again.

1. I am not sure the mean "In the subform, key entry person would enter as
many
parameters as needed". I have a form for only enter visit information and
the data will enter to visit table. I have ten form because I need the screen
form to macth with paper form. Each screen form only enter part of fields
data to this big table. e.g. 10 fields data from form1 and 15 fields data
from form2....., add all these forms together, the fields will be over 200. I
did not design one form for one visit.

I would like to learn how can I enter as many parameters as needed. It will
help me work out my problem.

2. I create the queries using patients table, visit table and this bigtable.
patID field for join patient table and visit table. VisitID for join visit
table and the big table.
there is only one row for each patient in patient table and more then one
row in visit table for each patient. The big table may have same or less rows
then visit table for each patient. The visit table is for all projecta and
the big table is only for a new project.
I am not sure what's worng about queries, would you please let know?

I used to have one database for one project and have a data management
database to manage all the projects database. Here they use to put all
projects in on database.

 

[Jeff Boyce]

As others have pointed out, you are experiencing difficulties getting Access
to do what you want because of the way the data is structured. The
suggestions about normalizing your data are intended to point the way to
getting better use (and easier use) out of Access. Access limits you to 255
fields per table, but normalization rarely requires more than 20-30 per
table.

If you'd rather stick with the data the way it is now organized, that's your
choice. I would offer an analogy ... while it is possible to use a table
saw to drive nails, it isn't a very good idea. You can find a way to use
Access against the data as you now have it organized, but it isn't a very
good idea either.

As Pat pointed out, you can go through all the work to get your queries and
forms and reports and code built to handle the current data structure, but
you will have to re-do everything when a new test is added, given your
current data structure.

Best of luck on your project

Jeff Boyce
Microsoft Office/Access MVP